CODEWITHSUNDEEP
sqlazure-cosmosdbazure-cosmosdb-sqlapi
zillo
zillo

Reputation: 503

CosmosDB SQL query on child item in array with a given conditiion

I am working with CosmosDB and I have to do with a collection which looks like the following

[
  {
    "detected": [
      {
        "class": "person",
        "probability": 94.24896240234375,
        "centroid": {
          "x": 242,
          "y": 151
        },
        "is_tracked": false
      }
    ]
  },
  {
    "detected": [
      {
        "class": "person",
        "probability": 64.25231099128723,
        "centroid": {
          "x": 355,
          "y": 259
        },
        "is_tracked": true
      },
      {
        "class": "person",
        "probability": 50.44138431549072,
        "centroid": {
          "x": 185,
          "y": 183
        },
        "is_tracked": true
      }
    ]
  }
]

I would like to write a SQL query that allows me to select all the x, y values within the centroid object only where is_tracked is set to true. In principle, the query that I'm looking for would return something like the following: 

[
  {
    "x": 355,
    "y": 259
  },
  {
    "x": 185,
    "y": 183
  }
]

I've already tried

SELECT c.detected FROM c WHERE ARRAY_CONTAINS(c.detected, {'is_tracked': true}, true)

and I get what I posted above. However, I don't know how to further filter.

Any help is much appreciated

Upvotes: 1

Views: 690

Answers (1)

Jay Gong
Jay Gong

Reputation: 23782

Please use below sql:

SELECT detect.centroid.x as x,detect.centroid.y as y FROM c
join detect in c.detected
where detect.is_tracked = true

Output:

enter image description here

Upvotes: 1

Related Questions