JDBC Driver for JSON

Build 22.0.8462

JSONFormat

Specifies the format of the JSON document.

Possible Values

JSON, JSONRows, LDJSON

Data Type

string

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.

Copyright (c) 2023 CData Software, Inc. - All rights reserved.
Build 22.0.8462