CODEWITHSUNDEEP
c#azure-cosmosdbazure-cosmosdb-sqlapi
Matt Douhan
Matt Douhan

Reputation: 2113

How do I get the value property in this CosmosDb Query?

I am trying to get deeper into an object but for some reason it does not work in my Cosmos query, the query is quite simple as follows.

SELECT c.id,  
c.ItemNo, 
bv.variantId AS variantNo, 
bv.variantDescription AS title, 
c.BasicData.presentation.articleLongDescription[0] AS Description,
c.BasicData.presentation.articleCompositionList.origin,
bv.size.sizeName,
c.BasicData.asset.assets[0].locations[1].path
FROM c
JOIN bv IN c.BasicData.base.sales.variants
WHERE c.brand = 'XXX'
AND c.Consumer = 'XXX'
AND bv.variantId = 'XXX'
AND c.Season = 'XXX'

The result is rather straight forward too as follows

[
    {
        "id": "9c9c5b56-999f-4f98-8c52-902f43dd4cfe",
        "ItemNo": "0562168014",
        "variantNo": "0562168014003",
        "title": "Trousers Grey, 36",
        "Description": {
            "locale": "en-GB",
            "value": "jongel fibbel"
        },
        "origin": "{\"\":{\"materials\":{\"002elastane\":\"30.0\",\"002polyester\":\"70.0\"}}}",
        "path": "https://public.assets.XXX.com/assets/002/b0/1b/b01b4fa77023f48ca3c6ff8777cc27276f478f1e.jpg"
    }
]

My problem is I want to return the "value" property "jongel fibbel" instead of the entire description object

I tried changing 

c.BasicData.presentation.articleLongDescription[0] AS Description,

To

c.BasicData.presentation.articleLongDescription[0].value AS Description,

But that gives me a syntax error at value

How can I return the value properly in the above query?

EDIT -------

aricleLongDescription is an array as follows

"articleLongDescription": [
                {
                    "locale": "en-GB",
                    "value": "jongel fibbel"
                }
            ],

SO I also tried JOIN as follows

SELECT c.id,  
c.ItemNo, 
bv.variantId AS variantNo, 
bv.variantDescription AS title, 
ba.value AS Description,
c.BasicData.presentation.articleCompositionList.origin,
bv.size.sizeName,
c.BasicData.asset.assets[0].locations[1].path
FROM c
JOIN bv IN c.BasicData.base.sales.variants
JOIN ba IN c.BasicData.presentation.articleLongDescription
WHERE c.brand = 'XXX'
AND c.Consumer = 'XXX'
AND bv.variantId = 'XXX'
AND c.Season = 'XXXX'

But it also fails with syntax error at value

EDIT ---

Also tried a subquery as follows

SELECT c.id,  
c.ItemNo, 
bv.variantId AS variantNo, 
bv.variantDescription AS title, 
--c.BasicData.presentation.articleLongDescription[0] AS Description,
-- ARRAY(SELECT serving.description FROM serving IN food.servings) AS servings
ARRAY(SELECT jongel.value FROM jongel IN c.BasicData.presentation.articleLongDescription) AS Description

c.BasicData.presentation.articleCompositionList.origin,
bv.size.sizeName,
c.BasicData.asset.assets[0].locations[1].path
FROM c
JOIN bv IN c.BasicData.base.sales.variants
JOIN ba IN c.BasicData.presentation.articleLongDescription
WHERE c.brand = 'XXX'
AND c.Consumer = 'XXX'
AND bv.variantId = 'XXX'
AND c.Season = 'XXX'

But it also fails with syntax error at value, I am wondering is "value" a protected keyword or something in CosmosDB?

Upvotes: 1

Views: 2460

Answers (1)

Matt Douhan
Matt Douhan

Reputation: 2113

As it turns out value is a reserved keyword in CosmosDB so the syntax to get around that is as follows

c.BasicData.presentation.articleLongDescription[0]["value"] AS Description,

Note there is no .(dot) just brackets and doublequotes

Upvotes: 3

Related Questions