CODEWITHSUNDEEP
azure-cosmosdbazure-cosmosdb-sqlapi
jas
jas

Reputation: 103

Selecting values from objects in document nested array in CosmosDB

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"

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

Upvotes: 6

Views: 5355

Answers (2)

Jan Metzger
Jan Metzger

Reputation: 31

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!

Upvotes: 1

Samer Boshra
Samer Boshra

Reputation: 919

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"

Upvotes: 12

Related Questions