CODEWITHSUNDEEP
azureazure-cosmosdbazure-cosmosdb-sqlapi
Chris
Chris

Reputation: 187

How do you flatten an array of values into rows in a CosmosDB query?

Given the following collection data in a CosmosDB database:

[
    {
        "id": "1",
        "title": "Blog post #1",
        "tags": ["tag1", "tag2", "tag3"]
    },
    {
        "id": "2",
        "title": "Blog post #2",
        "tags": ["tag1"]
    }
]

How would you flatten this into a result set that looks like the following:

[
    {
        "id": "1",
        "title": "Blog post #1",
        "tagName": "tag1"
    },
    {
        "id": "1",
        "title": "Blog post #1",
        "tagName": "tag2"
    },
    {
        "id": "1",
        "title": "Blog post #1",
        "tagName": "tag3"
    },
    {
        "id": "2",
        "title": "Blog post #2",
        "tagName": "tag1"
    }
]

The ultimate goal in this example is finding the tag count, so this query would be wrapped in something like SELECT COUNT(1) as count, s.tagName FROM (subquery) s GROUP BY s.tagName.

Upvotes: 0

Views: 1036

Answers (1)

NotFound
NotFound

Reputation: 6157

You got the right idea but are using the wrong type of join. You need to use JOIN IN to flatten the array.

SELECT c.id, c.title, t AS tagName
FROM c
JOIN t IN c.tags

Upvotes: 2

Related Questions