CosmosDb querying a field within an object within an array

Question

Let's say I have this document:

{
  "body": {
    "items": [
      {
        "id": 1,
        "person": {
          "id": 100
        }
      },
      {
        "id": 2,
        "person": {
          "id": 101
        }
      }
    ]
  }
}

I'm trying to create a query that will select documents based on an id of a person within my items array. So far I've come up with this:

SELECT * FROM c WHERE ARRAY_CONTAINS(c.body.items, { 'id': 1 }, true)

This works for selecting based off the item of the item object itself, but if I try to do this:

SELECT * FROM c WHERE ARRAY_CONTAINS(c.body.items, { 'person.id': 100 }, true)

It doesn't work.

I'm trying to build my query such that I could do a query where it should check for documents containing person.id = 100 and person.id = 101, but for now I just want to get the main part working.

How can I fix my query to allow me to search inner objects with the ARRAY_CONTAINS function, or is there a more intelligent way to do this?

Edit:

I also tried:

SELECT * FROM c JOIN i IN c.body.items WHERE i.person.id = 100

This works, but I need to be able to query on multiple people, so something like

SELECT * FROM c JOIN i IN c.body.items WHERE i.person.id = 100 AND i.person.id

Wouldn't work since it'd be expected an item to have a person.id equal to 100 and 101, which is impossible.

David Makogon David Makogon · Accepted Answer · 2020-05-05T01:01:22

Hmm. I couldn't get that to work either, though it seems like it should.

As a workaround, you can do a self-join and then filter based on the inner id. Note: in my example, I renamed your inner person object's id property to personid, as this query results in multiple top-level id properties otherwise:

select c.id, i.person.personid
from c
join i in c.body.items
where i.person.personid = 101

Result:

[
    {
        "id": "1",
        "personid": 101
    }
]

I could've avoided the top-level id collision, leaving the original properties in place, with an alias:

select c.id, i.person.id as personid
from c
join i in c.body.items
where i.person.id = 101

You can also use bracket syntax for the inner properties, which is equivalent and gives the same result:

select c.id, i.person['personid']
from c
join i in c.body.items
where i.person['personid'] = 101

The above approach also works when searching for multiple person IDs. As an example, imagine another entry (id=2) where the array contains person id's 102 and 103:

select c.id, i.person.personid
from c
join i in c.body.items
where array_contains([101,103],i.person.personid)

This returns:

[
    {
        "id": "1",
        "personid": 101
    },
    {
        "id": "2",
        "personid": 103
    }
]

Alternative syntax:

select c.id, i.person.personid
from c
join i in c.body.items
where i.person.personid in (101,103)

CosmosDb querying a field within an object within an array

1 Answers