How to query in DocumentDB based on inner json object value?

Question

Suppose I have 3 objects in DocumentDB like this.

This is the class record.

And now I want to get all the Id's where a student exist with name sunny.

{
  "id": "111",
  "class": 1,
  "students": [
    {
      "name": "sunny"
    },
    {
      "name": "pinki"
    },
    {
      "name": "bobby"
    },
    {
      "name": "lucky"
    }
  ]
}

{
  "id": "222",
  "class": 2,
  "students": [
    {
      "name": "pinki"
    },
    {
      "name": "sunny"
    },
    {
      "name": "bobby"
    }
  ]
}

{
  "id": "333",
  "class": 3,
  "students": [
    {
      "name": "pinki"
    },
    {
      "name": "lucky"
    },
    {
      "name": "bobby"
    }
  ]
}

What will be the query to get the result?

Please take care to format your code before you post your question. — J. Steen
– J. Steen, Commented Mar 11, 2015 at 11:33

Andrew Liu · Accepted Answer · 2015-03-11 22:45:49Z

9

You can use DocumentDB's JOIN to create a cross product on documents with array elements.

For example, the following query creates a cross-product on documents with it's students property to query on the students.name:

select doc.id
from doc
join students in doc.students
where students.name = 'sunny'

returns the following dataset:

[{
    id: 111
}, {
    id: 222
}]

Reference: http://azure.microsoft.com/en-us/documentation/articles/documentdb-sql-query/#joins

answered Mar 11, 2015 at 22:45

Andrew Liu

8,12940 silver badges47 bronze badges

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

1 Comment

Mimi Gentz Over a year ago

Satish, You might want to take a look at the Query Playground for DocumentDB, it provides sample queries to run against predefined data and might be useful if you have similar questions in the future.

Collectives™ on Stack Overflow

How to query in DocumentDB based on inner json object value?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related