DocumentDb - query on nested document and root level

Question

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?

Aravind Krishna R. · Accepted Answer · 2016-11-20 20:09:56Z

4

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.

edited Nov 20, 2016 at 20:09

answered Jun 24, 2015 at 17:18

Aravind Krishna R.

8,00332 silver badges39 bronze badges

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

Collectives™ on Stack Overflow

DocumentDb - query on nested document and root level

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related