Selecting values from objects in document nested array in CosmosDB

Question

Imagine we have a such collection (example taken from https://www.documentdb.com/sql/demo)

{
    "_id" : "19015",
    "description" : "Snacks, granola bars, hard, plain",
    "servings" : [ 
        {
            "amount" : 1,
            "description" : "bar",
            "weightInGrams" : 21
        }, 
        {
            "amount" : 1,
            "description" : "bar (1 oz)",
            "weightInGrams" : 28
        }, 
        {
            "amount" : 1,
            "description" : "bar",
            "weightInGrams" : 25
        }
    ]
}

How i can query CosmosDB in SQL api to get results such like this?

{
    "_id" : "19015",
    "servings" : [ 
        {
            "description" : "bar"
        }, 
        {
            "description" : "bar (1 oz)"
        }, 
        {
            "description" : "bar"
        }
    ]
}

at MongoDB i would use such query

db.getCollection('food').find({id: '19015'}, {'servings.description' : 1})

Tried multiple scenarious with joins etc

SELECT 
    food.id,
    food.servings.description
FROM food
WHERE food.id = "19015"

or

SELECT 
    food.id,
    [{
        description: food.servings[0].description
    }] AS servings
FROM food
WHERE food.id = "19015"

instead of [0] i tried [] or [$] but doesn't works

Does somebody has idea how in simply way i can resolve this issue?

I believe you have exact answer, please check stackoverflow.com/help/someone-answers — Imre Pühvel
– Imre Pühvel, Commented Jul 25, 2018 at 6:56

Samer Boshra · Accepted Answer · 2018-07-20 20:27:40Z

12

You could use the ARRAY(subquery) expression to achieve this. Here's the query:

SELECT
    food.id,
    ARRAY(SELECT serving.description FROM serving IN food.servings) AS servings
FROM food
WHERE food.id = "19015"

answered Jul 20, 2018 at 20:27

Samer Boshra

9396 silver badges6 bronze badges

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

2 Comments

Imre Pühvel Over a year ago

So, there are subqueries now in CosmosDB. Any pointers regarding documentation?

Samer Boshra Over a year ago

Yes, Cosmos DB recently added support for subqueries. We're currently working on publishing the documentation.

Good Night Nerd Pride · Accepted Answer · 2021-04-27 10:52:56Z

1

By executing this query:

select food.id, servings.description
from food join servings in food.servings

you would at least get the following result:

{"id":19015, "description":"bar"}
{"id":19015, "description":"bar (1 oz)"}
{"id":19015, "description":"bar"}

Not 100% what you were expecting, but maybe something you can work with!

edited Apr 27, 2021 at 10:52

Good Night Nerd Pride

8,6904 gold badges57 silver badges72 bronze badges

answered Jul 20, 2018 at 12:41

Jan Metzger

313 bronze badges

Collectives™ on Stack Overflow

Selecting values from objects in document nested array in CosmosDB

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related