CODEWITHSUNDEEP
azure-cosmosdb
alek kowalczyk
alek kowalczyk

Reputation: 4934

DocumentDb - query on nested document and root level

Alert me | Edit | Delete | Change type Question You cannot vote on your own post 0 Hi!

Lets assume I have docuents in following manner:

{
  id: 123,
  tags: [ { name: "something" } ]
}

and I want to query all documents that contain a tag with name="searched" OR have the id=9000. I tested on playground ( https://www.documentdb.com/sql/demo )something like:

SELECT food.id, food.description, food.tags
FROM food
JOIN tag IN food.tags
WHERE food.id = "09052" or tag.name="blueberries"

but then I get a bunch of duplicate records, each document from food is times the amount of tags in that document.

How can I get distinct results when filtering on nested collection and root properties?

Upvotes: 3

Views: 1945

Answers (1)

Aravind Krishna R.
Aravind Krishna R.

Reputation: 8003

The ARRAY_CONTAINS built-in function might be what you need. See https://msdn.microsoft.com/library/azure/dn782250.aspx#bk_array_functions for details, i.e., something like this:

SELECT food.id, food.description, food.tags
FROM food
WHERE food.id = "09052" or ARRAY_CONTAINS(food.tags, { "name": "blueberries" })

You can test this query on the Query Playground here.

Note that the function does not use the index, so ideally you should use this when there's another filter in the query. Otherwise, the only way to do this is to use the query you had previously, then perform a "distinct" on the client side.

Upvotes: 4

Related Questions