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 https://stackoverflow.com/help/someone-answers — Imre Pühvel, Jul 25 '18 at 06:56

score 11 · Answer 1 · answered Jul 20 '18 at 20:27

11

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 '18 at 20:27

Samer Boshra

869
5
6

So, there are subqueries now in CosmosDB. Any pointers regarding documentation? – Imre Pühvel Jul 23 '18 at 09:25
2

Yes, Cosmos DB recently added support for subqueries. We're currently working on publishing the documentation. – Samer Boshra Jul 23 '18 at 17:33

score 1 · Answer 2 · edited Apr 27 '21 at 10:52

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 '21 at 10:52

Good Night Nerd Pride

8,245
4
49
65

answered Jul 20 '18 at 12:41

Jan Metzger

31
2

Selecting values from objects in document nested array in CosmosDB

2 Answers2

Linked