Cosmos DB Query Projection

Question

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?

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
– Gaurav Mantri, Commented Jun 5, 2020 at 7:01
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 Johnson
– Steve Johnson, Commented Jun 5, 2020 at 7:19
Have you tried the query posted by @SteveZhao? Did that work? — Gaurav Mantri
– Gaurav Mantri, Commented Jun 5, 2020 at 11:10
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
– Ankit Vijay, Commented Jun 5, 2020 at 11:57

Steve Johnson · Accepted Answer · 2020-06-08 03:39:01Z

3

+50

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?

answered Jun 8, 2020 at 3:39

Steve Johnson

8,8081 gold badge11 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ankit Vijay Over a year ago

That's a great, idea. Thanks for the suggestion. I guess the one issue with this approach is that we would need to create a separate udf in Javascript. The function needs to be executed/ deployed in a different way from the rest of the code. We use C# Cosmos SDK to perform all our operations on the Cosmos Document. We try to avoid the stored procedures, triggers, UDF as much as possible. This, now, would be an exception. Do you think that is the only way? If that is the case, I'm leaning more towards creating a separate POCO document to deserialize it (instead of creating a UDF).

Steve Johnson Over a year ago

I"m glad to hear that this is helpful to you.As far as I am aware,this is the only way to do this.I have tried many sql,but they don't work.

Mahesh More · Accepted Answer · 2020-06-13 06:14:10Z

-1

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!

answered Jun 13, 2020 at 6:14

Mahesh More

8551 gold badge8 silver badges24 bronze badges

1 Comment

Ankit Vijay Over a year ago

Don't think it answers my question.

Collectives™ on Stack Overflow

Cosmos DB Query Projection

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related