DocumentDB filter documents on multiple items in a child array

Question

I have a Cosmos DB database with documents that have the following form:

{
  "Id": "1",
  "Price": 200,
  "Properties": [
    {
        "Name": "Name1",
        "Type": "Type1",            
    },
    {
        "Name": "Name2",
        "Type": "Type2",            
    }
  ]
},
{
  "Id": "2",
  "Price": 500,
  "Properties": [
    {
        "Name": "Name1",
        "Type": "Type1",            
    },
    {
        "Name": "Name2",
        "Type": "Type3",            
    }
  ]
},
{
  "Id": "3",
  "Price": 400,
  "Properties": [
    {
        "Name": "Name1",
        "Type": "Type2",            
    }
  ]
}

I would like to create a query that returns documents that satisfy multiple properties. E.g. I would like to retrieve the documents that have both properties of Type1 and Type2. The result should give me only the document with Id = 1.

Jesse Carter · Accepted Answer · 2017-10-18 14:10:57Z

3

SELECT c.Id
FROM c
WHERE ARRAY_CONTAINS(c.Properties, {'Type': 'Type1' }, true)
AND ARRAY_CONTAINS(c.Properties, {'Type': 'Type2' }, true)

answered Oct 18, 2017 at 14:10

Jesse Carter

21.3k8 gold badges71 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

carens Over a year ago

What does the third argument (true) do? I cannot find any documentation on this argument.

Jesse Carter Over a year ago

The third boolean parameter is for enabling or disabling partial matching (ie, this property of the object matches but the rest of the object doesn't have to). It doesn't appear to be well documented

Jesse Carter Over a year ago

See this question stackoverflow.com/questions/46794691/… which was answered by someone directly from the Cosmos team

carens Over a year ago

Ah ok, I missed that one. It would be nice if this was documented. I tried to add a comment to the documentation page, but that did not work (just says "error")

Aravind Krishna R. Over a year ago

We'll get this added to the documentation asap.

Collectives™ on Stack Overflow

DocumentDB filter documents on multiple items in a child array

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related