Cosmos Nested JSON Query

Question

This is the fist time that I work with CosmosDB and I am trying to create a Query to get some details about this JSON:

{
"Status": "Uploaded",
"ProvidedOn": "2022-04-04T18:34:57.4160484Z",
"DocumentTaxonomy": {
    "JurisdictionalCountriesOfService": [
        {
            "Id": 5,
            "Name": "United States"
        }
    ],
    "WorkProduct": {
        "Id": 762,
        "Name": "Other reports and documents",
        "Type": "Info item"
    }
},
"id": "3a92c052-bc23-4b8a-acbf-54044785968a",
"Meta": {
    "VersionId": "3",
    "LastUpdated": "0001-01-01T00:00:00",
    "Source": null,
    "Security": null,
    "Tag": null,
    "Id": null,
    "Extension": null,
    "ModifierExtension": null
},

}

Basicaly I need to get something like

SELECT id,Status,ProvidedOn, WorkProductName, WorkProductType,MetaVersionId FROM JSONFILE

In this image I am highlighting the columnsthat my query needs

NOTE: since I need to query different CosmoDB, not all of them have the DocumentTaxonomy section so the plan is when they doesn't exists return like a null or blank value

Answer 1

As per your question, the code should return the DocumentTaxonomy section values if they exist in the JSON otherwise It should return null or blank values.

This code may work for you:

SELECT c.id, c.ProvidedOn, c.Status,c.Meta.VersionId as versionId,
IS_DEFINED(c.DocumentTaxonomy.WorkProduct.Type) = true ? c.DocumentTaxonomy.WorkProduct.Type
  : IS_DEFINED(c.DocumentTaxonomy.WorkProduct.Type) = false ? null 
  : "some default value"
  as TypeDoc, 
IS_DEFINED(c.DocumentTaxonomy.WorkProduct.Name) = true ? c.DocumentTaxonomy.WorkProduct.Name
  : IS_DEFINED(c.DocumentTaxonomy.WorkProduct.Name) = false ? null
  : "some default value"
  as NameDoc 
FROM c

The Output it gave when DocumentTaxonomy section exists is:

[
    {
        "id": "3a92c052-bc23-4b8a-acbf-54044785968a",
        "ProvidedOn": "2022-04-04T18:34:57.4160484Z",
        "Status": "Uploaded",
        "versionId": "3",
        "TypeDoc": "Info item",
        "NameDoc": "Other reports and documents"
    }
]

The Output when DocumentTaxonomy section not exists :

[
    {
        "id": "3a92c052-bc23-4b8a-acbf-54044785968a",
        "ProvidedOn": "2022-04-04T18:34:57.4160484Z",
        "Status": "Uploaded",
        "versionId": "3",
        "TypeDoc": null,
        "NameDoc": null
    }
]

Please check the screenshot of the output for your reference: