how to find records with duplicates inside a string array is cosmos db

Question

I'm trying to write a cosmos query that return a list of ids, which contain duplicates in a string array that exists on each record.

Here's an example of the dataset I'm working with. using this data, only the id of A and B should be returned as their "things" array contains at least one duplicate strings.


[
    {
        "id": "A",
        "things": [
            "1",
            "1",
            "2",
            "2",
            "3"
        ]
    },
    {
        "id": "B",
        "things": [
            "1",
            "1",
            "2",
            "3"
        ]
    },
    {
        "id": "C",
        "things": [
            "1",
            "2",
            "3"
        ]
    }
]

I've tried to use a subquery, but can't seem to get that to work.

Could you please share what you have tried? or any errors you faced with. — Balaji
– Balaji, Commented Sep 19, 2024 at 17:35
Please take the tour, read How to Ask, and then edit to provide a minimal reproducible example. "I've tried to use a subquery" unfortunately doesn't help. Aside from all that: I'm not sure you can accomplish what you want in a single query, as there's no pipeline of aggregations+operations; this might require some iteration through values, whether in a stored procedure or client-side code. Might be beneficial to evolve your schema to store "things" items in separate documents, which will make your grouping and counting more straightforward... — David Makogon
– David Makogon, Commented Sep 19, 2024 at 19:02

user2835238 · Accepted Answer · 2024-09-19 23:25:31Z

0

I was able to get around this by writing a udf to check for duplicates in the string array.

SELECT 
   c.id,
FROM c 
WHERE
  udf.isDuplicate(c.things) = true

answered Sep 19, 2024 at 23:25

user2835238

1

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

Collectives™ on Stack Overflow

how to find records with duplicates inside a string array is cosmos db

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related