Pass Map as parameter in where clause

SQL++
1

Hi @vsr1,

I need to know if there is some way to pass a map in the where clause.

Example
SELECT information,
META().id AS _ID,
META().cas AS _CAS
FROM demoBucket
WHERE information= “{“name”:“Kitchen”}”;

The map can be dynamic but it will contain always tags that are present in information

information:{
name: Kitchen,
origin: US,
packed: true,
shipping: extra
}

2

Index is difficult but u can query if those are not nested fields.

SELECT d.information,
META(d).id AS _ID,
META(d).cas AS _CAS
FROM  `demoBucket` AS d
WHERE  ANY AND EVERY f IN OBJECT_PAIRS($pholder) SATISFIES  d.information.[f.name] =  f.val END

named parameter

$pholder = {"name":"Kitchen", "packed":true}
3

@vsr1 …Thanks for the quick reply
This is the actual query ,
SELECT d.definition.meta.general,
META(d).id AS _ID,
META(d).cas AS _CAS
FROM demoBucket AS d
WHERE ANY
AND EVERY f IN OBJECT_PAIRS($1) SATISFIES d.definition.meta.general.[f.name] = f.val END $1 = “{“name”:“Kitchen”}”;

which fails with below error on couchbase.

[
{
“code”: 3000,
“msg”: “syntax error - at $1”,
“query”: “SELECT d.definition.meta.general,\n META(d).id AS _ID,\n META(d).cas AS _CAS\nFROM demoBucket AS d\nWHERE ANY\n AND EVERY f IN OBJECT_PAIRS($1) SATISFIES d.definition.meta.general.[f.name] = f.val END $1 = “{“name”:“Kitchen”}”;”
}
]

Is there way in which I can pass this as substitute in the actual query as parameter to OBJECT_PAIRS . I had tried this one through code the assignment does not take place.

It works if I am passing the map directly inside OBJECT_PAIRS without setting the placeholder as $1.

4 
SELECT d.definition.meta.general,
META(d).id AS _ID,
META(d).cas AS _CAS
FROM  `demoBucket`  AS d
WHERE ANY
AND EVERY f IN OBJECT_PAIRS({"name":"Kitchen"}) SATISFIES d.definition.meta.general.[f.name] = f.val END;

If you need place holders read through documentation it vary based on UI, SDK, cbq shell, REST API.

5

@vsr1, Yes this works through couchbase but through Java spring data the assignment does not take place.
I am trying something like this

@Query(SELECT d.definition.meta.general,
META(d).id AS _ID,
META(d).cas AS _CAS
FROM demoBucket AS d
WHERE ANY
AND EVERY f IN OBJECT_PAIRS($1) SATISFIES d.definition.meta.general.[f.name] = f.val END;)
List getList(String filter);
filter = {“name”:“Kitchen”}

Through code it is always returning empty response.

6

It object not a string

If you are using $1 It must be Array of object (not array of string)

You must create Json Object {“name”:“kitchen”} and it JsonArray and pass that as place holder.

If you have further questions open new post under Java SDK topic

7

Thanks @vsr1 , it went through fine

JsonObject jsonObject = JsonObject.from(customMap);
and passing this to query placeholder did the assignment perfectly.

8

@vsr1 … everything works fine , But i don’t see if any Index can be built , Query is constantly taking ~180ms.

9

@vsr1

For this Query , I am building an index for field name , this will be always present in the json Object.

SELECT d.definition.meta.general,
META(d).id AS _ID,
META(d).cas AS _CAS
FROM demoBucket AS d
WHERE ANY
AND EVERY f IN OBJECT_PAIRS({“name”:“Kitchen”}) SATISFIES d.definition.meta.general.[f.name] = f.val END;

But it is not reducing the time for the query
CREATE INDEX nameIndex ON demoBucket(definition.meta.general.name)

Is there some syntax error I am doing or some thing else is missing ?