CODEWITHSUNDEEP
azure-cosmosdbazure-cosmosdb-sqlapi
daawnee
daawnee

Reputation: 61

How to query Cosmos DB to have an array from multiple items in the result set

I have the following content in a container, where device_id is the partition key.

[
    {
        "id": "hub-01",
        "device_id": "device-01",
        "created": "2020-12-08T17:47:35",
        "cohort": "test"
    },
    {
        "id": "hub-02",
        "device_id": "device-01",
        "created": "2020-12-08T17:47:36",
        "cohort": "test"
    },
    {
        "id": "hub-01",
        "device_id": "device-02",
        "created": "2020-11-17T20:25:20",
        "cohort": "test"
    },
    {
        "id": "hub-01",
        "device_id": "device-03",
        "created": "2020-11-17T16:05:18",
        "cohort": "test"
    }
]

How do I query all unique devices, with all their metadata collected into a sub-list, so I get the following result set:

[
    {
        "device_id": "device-01",
        "hubs": [
            {
                "id": "hub-01",
                "created": "2020-12-08T17:47:35",
                "cohort": "test"
            },
            {
                "id": "hub-02",
                "created": "2020-12-08T17:47:36",
                "cohort": "test"
            }
        ]
    },
    {
        "device_id": "device-02",
        "hubs": [
            {
                "id": "hub-01",
                "created": "2020-11-17T20:25:20",
                "cohort": "test"
            }
        ]
    },
    {
        "device_id": "device-03",
        "hubs": [
            {
                "id": "hub-01",
                "created": "2020-11-17T16:05:18",
                "cohort": "test"
            }
        ]
    }
]

I was experimenting along the lines of the following sub-query, but it does not behave as I would expect:

SELECT
    DISTINCT c.device_id,
    ARRAY(
        SELECT
            c2.id,
            c2.created,
            c2.cohort
        FROM c AS c2
        WHERE c2.device_id = c.device_id
    ) as hubs
FROM c

Upvotes: 1

Views: 1331

Answers (2)

Steve Johnson
Steve Johnson

Reputation: 8660

I agree with Mo B. You need to deal with this on your client side. I don't think UDF function can handle this because UDF function can't combine multiple items to one. I think the closest SQL like this:

SELECT
    c2.device_id,ARRAY_CONCAT([],c2.hubs)
FROM 
(SELECT c.device_id,ARRAY(
        SELECT
            c.id,
            c.created,
            c.cohort
        FROM c
    ) as hubs FROM c) as c2
GROUP BY c2.device_id

But ARRAY_CONCAT isn't Aggregate function and there is no Aggregate function can concat array.

Upvotes: 0

Hasan Savran
Hasan Savran

Reputation: 393

You can create UDF function to handle this. Here is a similar question I answered from another post. group data by same timestamp using cosmos db sql

Upvotes: 1

Related Questions