Vertical flattening queries enable you to retrieve an array of documents as if it were a separate table.

Vertical Flattening Query Syntax

In the FROM clause, you can use dot notation to drill down to a nested array.

SELECT * FROM [people.vehicles] 

Example

Consider the JSON structure from the people collection in Raw Data. Below is an object from the collection:

{
  "people": [
    {
      "personal": {
        "age": 24,
        "gender": "F",
        "name": {
          "first": "Jane",
          "last": "Roberts"
        }
      },
      "vehicles": [
        {
          "type": "car",
          "model": "Toyota Camry",
          "insurance": {
            "company": "Car Insurance",
            "policy_num": "98765"
          },
          "maintenance": [
            {
              "date": "05-11-2017",
              "desc": "tires rotated"
            },
            {
              "date": "11-03-2017",
              "desc": ["oil change","fan belt replaced","coolant reservoir replaced"]
            }
          ]
        },
        {
          "type": "car",
          "model": "Honda Accord",
          "insurance": {
            "company": "Car Insurance",
            "policy_num": "98765"
          },
          "maintenance": [
            {
              "date": "10-07-2017",
              "desc": "new air filter"
            },
            {
              "date": "01-13-2018",
              "desc": "new brakes"
            }
          ]
        }
      ],
      "source": "phone"
    }
  ]
}

Connection String

With the following connection string, the connector will not parse nested data -- the data is processed when you execute the query. Due to the default FlattenObjects functionality, the properties of the top-level object are flattened. Nested data is returned as a JSON aggregate.

URI=C:\people.txt;DataModel=Documents;JSONPath='$.people;'

Query

Vertical flattening will allow you to retrieve the vehicles array as a separate table:

SELECT * FROM [people.vehicles]