CODEWITHSUNDEEP
azure-cosmosdbazure-cosmosdb-sqlapi
Sam
Sam

Reputation: 30356

Queryring array of objects in CosmosDb

I have a team object that looks like this:

{
   "id": 123,
   "name": "Los Angeles Lakers",
   "type": "basketball",
   "members": [
      {
         "id": 456,
         "firstName": "John",
         "lastName": "Smith"
      },
      {
         "id": 887,
         "firstName": "John",
         "lastName": "Doe"
      }
   ]
}

As the sample implies, in the database, I have other types of teams e.g. baseball, football, etc.

I want to find the basketball team whose members contains a person with Id 887. In other words, I'm looking for John Doe's team.

I've tried the following but not getting any results.

SELECT *
FROM c JOIN m IN c.members
WHERE c.type = "basketball" AND m.id = 887

I also tried:

SELECT *
FROM c
WHERE c.type = "basketball" AND ARRAY_CONTAINS("id", 887)

Neither of these approaches worked. How do I structure this query?

Upvotes: 1

Views: 2092

Answers (1)

NotFound
NotFound

Reputation: 6227

Your first query is fine apart from the SELECT statement. The * is used to select all 'columns' but due to the JOIN it can't resolve that as you got multiple sets. You could change it into:

SELECT VALUE c
FROM c 
JOIN m IN c.members
WHERE c.type = "basketball" AND m.id = 887

In the above query Iselect the entirety of c and use VALUE so it doesn't get wrapped into another object.

The second query requires some more adjustments:

SELECT * 
FROM c 
WHERE c.type = "basketball" AND ARRAY_CONTAINS(c.members,{'id':887}, true)

In the array contains you'll have to define the array you want to search and the expression (which is scoped to a single property). Lastly it requires the third argument set to true to find partial matches as you only want to filter on the id and not the full member object.

Upvotes: 3

Related Questions