Cosmos DB Query Projection

2
votes

I have Cosmos Document of below format:

(Note: Example is just for illustration, the actual document has different properties)

{
        "OrderNumer":"12345",
        "Version":"5",
        "OrderDetails": [ 
            {
                "id":1,
                "OrderItem": {
                    "Name": "ABC",
                    "Description": "ABC Description",
                     .
                     .
                     .
                },
                "PaymentDetails": {
                },
                .
                .
                .
            },
            {
                "id":2,
                "OrderItem": {
                    "Name": "PQR",
                    "Description": "PQR Description",
                     .
                     .
                     .
                },
                "PaymentDetails": {
                },
                .
                .
                .
            },
        ],
        "OrderDate": "12-01-2020",
        "CustomerDetails": {
         }
        .
        .
        .
}

OrderNumber is a partition key.

I'm trying to project the above document through Cosmos Query to get the entire OrderDetails JSON object and a few properties from the parent into a single JSON with a filter on OrderDetails-> id.

The expected projection should be:

{
    "Order":
    [
        {
           "OrderNumer":"12345",
            "Version":"5",
            "id":1,
            "OrderItem": {
                "Name": "ABC",
                "Description": "ABC Description",
                 .
                 .
                 .
            },

            "PaymentDetails": {
            },
            .
            .
            .
        },
        {
            "OrderNumer":"12345",
            "Version":"5",
            "id":2,
            "OrderItem": {
                "Name": "PQR",
                "Description": "PQR Description",
                 .
                 .
                 .
            },
            "PaymentDetails": {
            },
            .
            .
            .
        },
    ]
}

As shown in the above JSON, I'm projecting OrderNumber and Version properties within the OrderDetails as Order.

OrderDetails is a massive JSON with a dynamic schema that makes it difficult for us to project its properties individually.

I have tried a few options to project this, but the closest I could reach was below query:

SELECT c.OrderNumber, c.Version, o as Order
FROM c
JOIN o in c.OrderDetails WHERE
c.OrderNumber= '1235' AND ARRAY_CONTAINS([1,2], o.id)

However, the above query does not give desired result as it keeps the OrderNumber and Version in as separate property to Order.

Is there any way I can achieve this?

2
azure-cosmosdbazure-cosmosdb-sqlapi
Please edit your question and include the query that you have written so far and tell us the issues you're running into. – Gaurav Mantri
try this sql : SELECT c.OrderNumer,c.Version,o.OrderItem,o.PaymentDetails FROM c join o in c.OrderDetails then new JsonObject add this result via code. – Steve Zhao
Have you tried the query posted by @SteveZhao? Did that work? – Gaurav Mantri
The query misses one of my requirements, I need all the properties from OrderDetails and OrderDetails has a huge and dynamic dataset. So I prefer to select it in entirety. Hope this clarifies – Ankit Vijay

2 Answers

2
votes

You can use UDF.

First:create a udf,below is code

function Converted(version,orderNumber,orderItem){
    orderItem.Version = version;
    orderItem.OrderNumber = orderNumber;
    return orderItem;
}

Second:use this sql

select value udf.Converted(c.Version,c.OrderNumber,o) from c join o in c.OrderDetails WHERE c.OrderNumber= '12345' AND ARRAY_CONTAINS([1,2], o.id)

Finally:new JsonObject add this result via code.

Here is result:

[
    {
        "id": 1,
        "OrderItem": {
            "Name": "ABC",
            "Description": "ABC Description"
        },
        "PaymentDetails": {},
        "Version": "5",
        "OrderNumber": "12345"
    },
    {
        "id": 2,
        "OrderItem": {
            "Name": "PQR",
            "Description": "PQR Description"
        },
        "PaymentDetails": {},
        "Version": "5",
        "OrderNumber": "12345"
    }
]

Does this meet your requirements?

-1
votes

I know this is too late to answer, till now you have resolved your problem but still providing the query to help other people who may have faced a similar issue.

CosmosDB provides flexibility which will help you to modify output JSON structure. Please see the below query for above problem.

Query: 

SELECT  
 [
     {
        "OrderNumer": c.OrderNumer,
        "Version": c.Version,
        "id": od.id,
        "OrderItem": {
            "Name": od.OrderItem.Name,
            "Description": od.OrderItem.Description
        }
     }
 ] AS Orders
 FROM c
 JOIN od IN c.OrderDetails

Happy coding!