Cosmos modeling question (nested queries)

Vinicius Carvalho 1 Reputation point

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?


Azure Cosmos DB
Azure Cosmos DB
An Azure NoSQL database service for app development.
1,535 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Sajeetharan 2,031 Reputation points Microsoft Employee

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

    f.number as Number,
    f.position as Position, as Name
    FROM d
    JOIN f IN d.pages
    JOIN g IN
    JOIN p IN g.publishers
    WHERE f.type = 'ads' and ='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.

    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