CODEWITHSUNDEEP
azure-cosmosdbazure-cosmosdb-sqlapi
Ritik Patel
Ritik Patel

Reputation: 69

How to display non distinct or duplicate records in cosmosdb sql?

I have a cosmosdb collection that has records like(I have shown 2 records for sample):

[
{
    "id": "0000001|0000000|0000002",
    "primarycode": "0000001",
    "code2": "0000000",
    "code3": "0000002",
    "array": [
        {
            "requiredvalue": "79050040002010",
            "someobject": {
                "abc": "02",
                "def": "05",
                "ghi": "04",
                "jkl": "03"
            }
        }
    ]
},
{
    "id": "0000001|0000003|0000002",
    "primarycode": "0000001",
    "code2": "0000003",
    "code3": "0000002",
    "array": [
        {
            "requiredvalue": "79050040002010",
            "someobject": {
                "abc": "02",
                "def": "05",
                "ghi": "04",
                "jkl": "03"
            }
        },
        {
            "requiredvalue": "79050040005680",
            "someobject": {
                "abc": "02",
                "def": "05",
                "ghi": "04",
                "jkl": "03"
            }
        }
    ]
}
]

What i need to get here is the common "requiredvalue" from both these records(which is "79050040002010"), and the condition is these records have the same "primaryCode" but the "code2" is different, hence the id is also different.

I am able to get the distinct "requiredvalue" from these records with the query:

SELECT DISTINCT f.requiredvalue FROM c JOIN f IN c.array WHERE c.primarycode="0000001"

This would return the "requiredvalue" with "79050040002010", "79050040005680". But i just want the common "requiredvalue" which is "79050040002010".

Upvotes: 1

Views: 1799

Answers (1)

NotFound
NotFound

Reputation: 6112

You can use the GROUP BY clause that hasn't been introduced too long ago. Example:

SELECT s.requiredvalue
FROM (
    SELECT f.requiredvalue, COUNT(1) AS count
    FROM c
    JOIN f IN c.array1
    WHERE c.primarycode='0000001'
    GROUP BY f.requiredvalue
) s
WHERE s.count > 1

edit: hereby also a query to get the overlapping requiredvalue for every primarycode

SELECT s.primarycode, s.requiredvalue
FROM (
    SELECT c.primarycode, f.requiredvalue, COUNT(1) AS count
    FROM c
    JOIN f IN c.array1
    GROUP BY c.primarycode, f.requiredvalue
) s
WHERE s.count > 1

Upvotes: 1

Related Questions