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:

Account must contain "John Smith" (id: 333) as a member
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"?

Mark Brown · Accepted Answer · 2022-04-27 01:59:42Z

3

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".

answered Apr 27, 2022 at 1:59

Mark Brown

8,8312 gold badges21 silver badges23 bronze badges

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

1 Comment

Sam Over a year ago

Yes, this works nicely. This answered a few questions I had. Now, I know I can have multiple JOIN's. Also using a SELECT for JOINs is not something I was familiar with. This is very helpful indeed. Thank you! And you're correct about ids. I actually use Guid values but for simplification purposes, I used int in my original post. Thanks again. Really appreciate it!

Collectives™ on Stack Overflow

CosmosDb querying in sub-object

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related