Query on how to retrieve documents which satisfies a condition in nested array and matching value outside nested array

Couchbase Server
, ,
1

Hi Team

I have a document as below, where person is an array of objects, which itself has keys which is an array. If entry in keys=“1234” and (name=“jack” or name=“jones”) then document satisfying that condition needs to be returned. So in the below example, Doc1 satisfies the condition and should be returned but not Doc2.

Doc1:
{
“person”: [
{
“keys”: [
“1234”,
“1111”
],
“name”: “jack”
},
{
“keys”: [
“1234”,
“9999”
],
“name”: “jones”
},
{
“keys”: [
“2222”,
“8888”
],
“name”: “jones”
}
]
}

Doc2
{
“person”: [
{
“keys”: [
“1234”,
“1111”
],
“name”: “jack”
},
{
“keys”: [
“3333”,
“9999”
],
“name”: “jones”
},
{
“keys”: [
“2222”,
“8888”
],
“name”: “jones”
}
]
}

Need on how to create an index and query to retrieve such documents.

Regards,
Venkat

2

You could use:

SELECT ...
FROM ...
WHERE ANY p IN person 
      SATISFIES
          p.name IN ["jack","jones"] 
          AND ANY k IN p.`keys`     -- must quote reserved word "keys"
              SATISFIES
                  k = "1234"
              END
      END

HTH.

3

And you could feed your statement into https://index-advisor.couchbase.com/indexadvisor/#1 for a hint as to what index should be created.

(Also, in your example, both documents have a key “1234” for person “jack”, so both are returned; one is returned if you use the key filter “3333”.)

4

@dh thanks for responding back.

My bad if I was not able to articulate the issue correctly.

The query is to return the documents which have keys entry of 1234 and both “jack” and “jones” in the outer array. In which case should return Doc1.

Regards,
Venkat

5

If you want two conditions - jack+1234 & jones+1234 you need to simply duplicate and AND the conditions:

Try:

SELECT ...
FROM ...
WHERE ANY p IN person 
      SATISFIES
          p.name = "jack"
          AND ANY k IN p.`keys`
              SATISFIES
                  k = "1234"
              END
      END
AND ANY p IN person 
      SATISFIES
          p.name = "jones"
          AND ANY k IN p.`keys`
              SATISFIES
                  k = "1234"
              END
      END

If you mean, jack+jones and any key (even if not associated with either jack or jones) is 1234, then try:

SELECT ...
FROM ...
WHERE ANY p IN person 
      SATISFIES
          p.name = "jack"
      END
AND ANY p IN person 
      SATISFIES
          p.name = "jones"
      END
AND ANY p IN PERSON
      SATISFIES
          ANY k IN p.`keys`
              SATISFIES
                  k = "1234"
              END
      END

If you mean jack+jones and one of them (but not necessarily both) have an associated key 1234, try:

SELECT ...
FROM ...
WHERE ANY p IN person 
      SATISFIES
          p.name = "jack"
      END
AND ANY p IN person 
      SATISFIES
          p.name = "jones"
      END
AND ANY p IN PERSON
      SATISFIES
          p.name IN ["jack","jones"]
          ANY k IN p.`keys`
              SATISFIES
                  k = "1234"
              END
      END

HTH.

6

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.