Specifies the format of the JSON document.

Syntax

Connect-JSON -JSONFormat string

Possible Values

Data Type

cstr

Default Value

"JSON"

Remarks

This option allows you to specify the format of the JSON document which enables parsing specifically for the selected format.

JSON Formats

The following JSONFormat configurations are available.

• JSON

  This is the default format and should be used in the majority of cases.

• JSONRows

  This is a specific format in which data is returned in a relational format consisting of rows of data contained within primitive arrays. Column information is returned as well in a separate array.

  Note: DataModel does not apply when using this JSONFormat.

  Example:
  

    {
      "dataset": {
        "column_names": [
          "Name",
          "Age",
          "Gender"
        ],
        "data": [
          [
            "John Doe",
            37,
            "M"
          ],
          [
            "David Thomas",
            25,
            "M"
          ]
        ]
      }
    }
    

  The JSONPath property requires special syntax to identify the column and row paths. The syntax consists of specifying a path for each using a "column:" and "row:" prefix. Using the example above, the JSONPath would be set to: column:$.dataset.column_names;row:$.dataset.data

  In the case that columns are returned in an object with additional data, an additional "columnname:" prefix can be specified to identify the path to the value containing the column name.

  Example:
  

    {
      "columns": [
        {
          "name":"first_name",
          "type":"text"
        },
        {
          "name":"last_name",
          "type":"text"
        }
      ],
      "rows": [
        [
          "John",
          "Doe"
        ],
        [
          "David",
          "Thomas"
        ]
      ]
    }
    

  In the above example, JSONPath would be set to: column:$.columns;columnname:$.columns.name;row:$.rows

• LDJSON (Line-Delimited JSON)

  This format is used to parse line-delimited JSON files (also known as NDJSON or JSONLines). Line-delimited JSON files contain a separate JSON document on each line.

  Example LDJSON File:
  

    { "Name": "John Doe", "Age": 37, "Gender": "M" }
    { "Name": "David Thomas", "Age": 25, "Gender": "M" }
    { "Name": "Susan Price", "Age": 35, "Gender": "F" }
    

  The JSONPath value is treated the same as when using the regular JSON format. The only difference is that the root path ($.) is always used (therefore treating all the lines of JSON as it is contained within an array).

  In the above example, the JSONPath will be "$.", which will return 3 rows containing the columns: Name, Age, and Gender.