CosmosDb nested array query projection

Question

I would like to pull specific pieces of data from within a nested array inside of a document stored in CosmosDb. This is relevant in a reporting use case where I am currently over-fetching more data than is necessary for a report.

I'm using the example document from the playground found here https://www.documentdb.com/sql/demo to illustrate an example of what I'm trying to do. The structure looks like this:

{
    "id": "03226",
    "description": "Babyfood, dessert, fruit pudding, orange, strained",
    "version": 1,
    "foodGroup": "Baby Foods",
    "servings": [{
            "amount": 1,
            "description": "oz",
            "weightInGrams": 28.35
        }, {
            "amount": 1,
            "description": "jar",
            "weightInGrams": 113
        }
    ],
}

Assuming I want to grab the root id property and just the 'description' property of the servings object my desired output would be this:

{
    "id": "03226",
    "servings": [{
            "description": "oz"
        }, {
            "description": "jar"
        }
    ]
}

This is the closes I've gotten to shape the results as I want.

SELECT 
VALUE 
 {
   "id": c.id,
   "servings": [
      {
       "description": s.description
       }
   ]
 }

FROM c
JOIN s IN c.servings

However, the results appear to be only grabbing the first item in the servings arrary.

{
  "id": "03226",
  "servings": [
    {
      "description": "oz"
    }
  ]
}

I have not been able to find any examples of this nested object projection where they haven't hard-coded something like servings[0]. How might I go about solving this?

Steve Johnson · Accepted Answer · 2021-02-17 22:42:42Z

2

You should use the ARRAY expression to construct an array from subquery's results to achieve this.

Please try this SQL:

SELECT 
    c.id,ARRAY(SELECT s.description FROM s IN c.servings) AS servings 
FROM c

Result:

[
    {
        "id": "03226",
        "servings": [
            {
                "description": "oz"
            },
            {
                "description": "jar"
            }
        ]
    }
]

answered Feb 17, 2021 at 22:42

Steve Johnson

8,8081 gold badge11 silver badges21 bronze badges

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

Collectives™ on Stack Overflow

CosmosDb nested array query projection

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related