Query for docs with multiple array field matches on attribute values

SQL++
,
1

Here is a sample of my document structure and a document that would be returned by the query i seek.

I want to query for documents that have detections where a detection where labelName is dog and another detection with labelName sofa

Basically in the array of objects find docs with two types of detections or two conditions.

[
  {
    "event": {
      "cn": "lr",
      "detection-human-timestamp": "2022-09-18 17:50:40.621833",
      "detectionTS": 1663523440.6216981,
      "detections": [
        {
          "confidence": 0.8150956034660339,
          "endX": 220,
          "endY": 207,
          "id": 2,
          "labelName": "dog",
          "startX": 183,
          "startY": 145
        },
        {
          "confidence": 0.9939553141593933,
          "endX": 327,
          "endY": 249,
          "id": 0,
          "labelName": "chair",
          "startX": 264,
          "startY": 130
        },
        {
          "confidence": 0.9754021763801575,
          "endX": 323,
          "endY": 424,
          "id": 1,
          "labelName": "sofa",
          "startX": 40,
          "startY": 181
        }
      ],
      "ohh": "k1",
      "duration": 992.843
    }
  }
]
2

Hi nhogan -
So you want to select documents HAVING a COUNT greater than 2 detections?

see GROUP BY Clause | Couchbase Docs

3

Not quite that simple.

Two detections yes but of certain criteria.

Like a document containing a detection where label name is dog and another where label name is sofa

4

If all you’re looking for are documents where both elements exist, you could try:

SELECT *
FROM event e
WHERE ANY d1 IN e.detections SATISFIES d1.labelName = "dog" END
AND ANY d2 IN e.detections SATISFIES d2.labelName = "sofa" END

HTH.

2 Likes
5

Thank you very much that worked!

Sorry for the newb question!

6

No problem. Always feel free to ask.

7

@dh approach right way to go for indexing prespetive
Another option if not looking for index stand point.

ANY AND EVERY v IN [“dof”,“sofa”] SATISFIES (ANY d1 IN e.detections SATISFIES d1.labelName = v END) END;