Dynamic query for the fields in the document

SQL++
1

Below is the scenario i am trying to resolve.
i have multiple documents say,

"Doc1" : {
  "CITY": "XYZ",
  "COUNTRY": "123",
  "STATE": "AB",
  "ZIPCODE": "123456"
}
"Doc2" : {
  "CITY": "XYZ1",
  "COUNTRY": "123A",
  "STATE": "AB1",
  "ZIPCODE": "1234567"
}

i get a map of data from user say

map = {“CITY”: “XYZ1”, “STATE” : “AB1” }

This map is dynamic, it can have different values.

Now i need to query the couchbase based on this map on above documents.

I have tried,

SELECT * FROM bucketName WHERE lower(state)=“XYZ1” AND lower(city)=“AB1”;

This is more like the static way, but now i am not sure what are the values i get in map, and also not sure how many i get.
say first time i might get only state and city, next i might get state, city, etc.

i need something like this
SELECT * FROM bucketName WHERE here i need to read the map and send the values.

can you help me out.

2

In your SDKs once you received map construct query dynamically in SDK and run the query using adhoc=true

OR

INSERT INTO default values("Doc1" , { "CITY": "XYZ", "COUNTRY": "123", "STATE": "AB", "ZIPCODE": "123456" });
INSERT INTO default values("Doc2" , { "CITY": "XYZ1", "COUNTRY": "123A", "STATE": "AB1", "ZIPCODE": "1234567" });
SELECT d.* FROM default d  WHERE ANY AND EVERY v IN OBJECT_PAIRS({"CITY": "XYZ1", "STATE" : "AB1" }) SATISFIES
                                        (ANY v1 IN OBJECT_PAIRS(d) SATISFIES v1.name = v.name AND v.val = v1.val END) END;

The following is query and passing map as query parameter as object

SELECT d.* FROM default d  WHERE ANY AND EVERY v IN OBJECT_PAIRS($qparam) SATISFIES
                                        (ANY v1 IN OBJECT_PAIRS(d) SATISFIES v1.name = v.name AND v.val = v1.val END) END;

$qparam {"CITY": "XYZ1", "STATE" : "AB1" }
1 Like
3

If you go the dynamic route, you need to be careful to avoid SQL injection problems with the keys and values that are coming from the user. Since the values in the input map correspond the values in the N1QL query, you can handle that by using N1QL parameters for those. The keys are trickier, since they correspond to fields in the query and you can’t use parameters. I recommend you check the key values carefully. You might try rejecting any key that isn’t strictly alphabetic.

4

Thank you, i will do that .

5

Thank you, it works for me.