CODEWITHSUNDEEP
azure-cosmosdb
Anggelo Bernaola Ibarra
Anggelo Bernaola Ibarra

Reputation: 57

CosmosDB, help flatten an array

I'm trying to flatten and filter my json data that is in a CosmosDB. The data looks like below and I would like to flatten everything in the array Variables. I need to show the id, amount and the array of orderidentifiers( only the orderId field)

my json:

{
        "id": "b71687be180da8116208cbb9a40e7e5e630e6cd595f3e09040a155978a2169f3",
        "amount": 5354.39,
        "orderIdentifiers": [
            {
                "orderId": "16520328183646646587",
                "itemIds": [
                    "90420839-2769-3acc-a686-3171386190a7"
                ]
            },
            {
                "orderId": "45288779686596595261",
                "itemIds": [
                    "fb662e41-1c7e-3f36-8cfd-ef2f3c7f0752",
                    "0b97371c-4eb9-3ec5-8ab9-bb65a9c9efe1"
                ]
            },
            {
                "orderId": "859986489484974993023",
                "itemIds": [
                    "5c16bb0d-f1a4-3289-bda5-28d0b09a2a56",
                    "ffb5dc2a-6e43-321d-97cb-3279ddfd1e39",
                    "781953ad-83e0-30da-a563-e69ed2a752c6",
                    "1936e885-c41a-3bd1-a3e0-80e7e3089fe4"
                ]
            },
            {
                "orderId": "80455639909013091834",
                "itemIds": [
                    "147d4a11-6c05-3fe3-8e4c-bcda3d238845",
                    "6c586585-6355-393d-a6f4-6fa6c665f3b8"
                ]
            }
           ]
    }

when I run this query : select c.id, c.amount, oi.orderId from finalcategorysales c JOIN oi IN c.orderIdentifiers where c.id='b71687be180da8116208cbb9a40e7e5e630e6cd595f3e09040a155978a2169f3'

the result is showing me only one element in orderIdentifiers array

{     "id": "b71687be180da8116208cbb9a40e7e5e630e6cd595f3e09040a155978a2169f3",
        "amount": 5354.39,
        "orderId": "16520328183646646587"
    },

Upvotes: 0

Views: 271

Answers (1)

Steve Johnson
Steve Johnson

Reputation: 8660

You can try something like this SQL:

SELECT 
    c.id, c.amount, Array(SELECT oi.orderId FROM c JOIN oi IN c.orderIdentifiers) AS orderIds  
FROM c 
WHERE c.id='b71687be180da8116208cbb9a40e7e5e630e6cd595f3e09040a155978a2169f3'

Result:

[
    {
        "id": "b71687be180da8116208cbb9a40e7e5e630e6cd595f3e09040a155978a2169f3",
        "amount": 5354.39,
        "orderIds": [
            {
                "orderId": "16520328183646646587"
            },
            {
                "orderId": "45288779686596595261"
            },
            {
                "orderId": "859986489484974993023"
            },
            {
                "orderId": "80455639909013091834"
            }
        ]
    }
]

Upvotes: 1

Related Questions