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

Question

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

AFAIK this kind of grouping is not supported so you'll need to do the data transform on the client side. — Mo B.
– Mo B., Commented Dec 9, 2020 at 9:37

Hasan Savran · Accepted Answer · 2020-12-09 15:58:52Z

1

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

answered Dec 9, 2020 at 15:58

Hasan Savran

3932 silver badges5 bronze badges

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

Comments

Steve Johnson · Accepted Answer · 2020-12-10 02:47:18Z

0

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.

answered Dec 10, 2020 at 2:47

Steve Johnson

8,8181 gold badge11 silver badges21 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related