CODEWITHSUNDEEP
azure-cosmosdb
Codehelp
Codehelp

Reputation: 4777

Querying DocumentDb for Inner results

I have a document with this structure:

  [{
  "id": "test",
  "StudentRules": [
    {
      "id": "d8b730905",
      "name": "Test",
      "ruletype": "Allow",
      "startdate": "5/7/2015 10:05:15 AM"
    }
   ]

I need to write a query that will give me all the "StudentRules" for a given id "test".

I wrote this:

select * from json j where j.id = "test"

This returns the structure as the example above. I am expecting this:

       [
        {
          "id": "d8b730905",
          "name": "Test",
          "ruletype": "Allow",
          "startdate": "5/7/2015 10:05:15 AM"
        }
       ]

The idea is to have all the rules in a list which will be shown in an MVC application.

Any help is greatly appreciated.

Thanks in advance.

Regards.

Upvotes: 0

Views: 206

Answers (1)

stephbaron
stephbaron

Reputation: 46

You can use the JOIN keyword to form cross products with nested array elements:

SELECT Rule.id, Rule.name, Rule.ruletype, Rule.startdate 
FROM root 
JOIN Rule IN root.StudentRules 
WHERE root.id = "test"

You may find looking at the sample queries on DocumentDB's query playground useful.

Upvotes: 2

Related Questions