Querying DocumentDb for Inner results

Question

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.

stephbaron stephbaron · Accepted Answer · 2015-05-07T15:05:53

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.

Querying DocumentDb for Inner results

1 Answers