CosmosDb querying in sub-object

Question

I'm trying to find accounts with a "extra-storage" premium package for a particular user in my Azure CosmosDb database. Here's what my Account object looks like:

{
   "id": 123,
   "name": "My Account",
   "members": [
      {
         "id": 333,
         "name": "John Smith"
      },
      {
         "id": 555,
         "name": "Jane Doe"
      }
   ],
   "subscription": {
       "type": "great-value",
       "startDate": "2022-04-21T16:38:00.0000000Z",
       "premiumPackages": [
          {
             "type": "extra-storage",
             "status": "active"
          },
          {
             "type": "video-encoding",
             "status": "cancelled"
          }
       ]
   }
}

So, my conditions for the query (in-English) are:

1. Account must contain "John Smith" (id: 333) as a member
2. It must have the "extra-storage" premium package in its subscription

I'm not sure if I can have multiple JOINs but here's what I've tried with no results so far:

SELECT c.id, c.name, s.premiumPackages.status
FROM c JOIN m IN c.members
    JOIN s IN c.subscription
WHERE CONTAINS(m.id, 333)
    AND CONTAINS(s.premiumPackages.type, "extra-storage")

Any idea how I can get accounts with "extra-storage" package for "John Smith"?

Answer 1

This query should give you what you are looking for.

SELECT c.id, c.name, premiumPackages.status
FROM c
JOIN (SELECT VALUE m FROM m IN c.members WHERE m.id = 333)
JOIN (SELECT VALUE s FROM s IN c.subscription.premiumPackages WHERE s.type 
= "extra-storage") AS premiumPackages

This blog post on Understanding how to query arrays in Azure Cosmos DB is helpful to keep bookmarked when trying to write queries for arrays.

PS, id on the root in Cosmos DB must be a string so your "id": 123 should be "id": "123".