CODEWITHSUNDEEP
sqlazure-cosmosdb
carens
carens

Reputation: 319

DocumentDB filter documents on multiple items in a child array

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.

Upvotes: 1

Views: 710

Answers (1)

Jesse Carter
Jesse Carter

Reputation: 21147

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

Upvotes: 3

Related Questions