Cosmos modeling question (nested queries)

Question

Hi there, I'm starting with cosmos, and I have a simple document that contains a lot of nested data. This document is mostly static, as few updates should happen to it. It maps a Magazine with its internal pages.

On a relational db I would have set a Magazine Table and a FK to a Pages table, but in cosmos I decided to embedded it.

{  
"id" : "randomUUID",  
    "release" : "1989-7-1",  
    "pages" : [  
      {  
        "number" : 0,  
        "position" : 0,  
        "type" : "cover",  
        "tags" : ["Disney"]  
      },  
      {  
        "number" : 3,  
        "position" : 4,  
        "type" : "ads",  
        "tags" : ["Nintendo"],  
        "games" : [  
          {  
            "id" : 1,  
            "name":  "Super Mario 3",  
            "platform" : {  
              "id" : 1,  
              "name" : "NES"  
            },  
            "publishers" : [  
              {  
                "id": 1,  
                "name" : "Nintendo"  
              }  
            ]  
          }  
        ]  
      }]}  

I was reading about joins and subqueries, but so far I was only able to query for the top document. I could not run a query to say, find only the pages where a page type = 'ads' and the game.publisher = 'Nintendo'

I do get the id of the magazine, but I was hoping to get individual pages for this query. Should I just model Pages on a separate container?

Thanks

Answer 1

Yes you can achieve the result you need by using JOIN as below,

SELECT
f.number as Number,
f.position as Position,
g.name as Name
FROM d
JOIN f IN d.pages
JOIN g IN f.games
JOIN p IN g.publishers
WHERE f.type = 'ads' and p.name ='Nintendo'

However I would highly recommend you to reconsider the data modelling in this scenario, while embedding is a common scenario, if you think your nested array is going to grow in the future, be mindful that Cosmos DB has a limitation on the document size (2 MB for NOSQL API) , so it is ok to duplicate the document for each type of game. https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/modeling-data

While choosing multiple containers is also another option, but you might need to issue two queries on certain cases. Please follow the above documentation on the best practices