JSON External Files | CREATE FOREIGN TABLE SQL Statement | Teradata Vantage - 17.05 - JSON External Files - Teradata Database

Teradata Vantageā„¢ - Native Object Store Getting Started Guide

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.05
created_date
January 2021
category
Programming Reference
featnum
B035-1214-175K

Each record in the JSON external data must be formatted on a single line, terminated by a new line symbol (\n).

External JSON files can contain individual records or a single JSON array that uses a comma (,) as the record delimiter. For single-array JSON files, the record can span across multiple lines. Optionally, the JSON array can be named.

Vantage can read the following JSON document in external storage:
 {"field1": true,"field2": "somestring","field3": {"field4": 1}}
Vantage cannot read the external JSON data if it is stored in this format, due to the line breaks:
{
    "field1" : true,
    "field2":
         "somestring",
    "field3":
    {
        "field4":1
    }
}
Because the following external JSON data is in a single array (delimited by the opening and closing square bracket characters), using commas to delimit individual records in the array, Vantage can read the data, even with the embedded line breaks:
[{
                "field1": "string1", "field2": "string2"
}, {
                "field1": "string3", "field2": "string4"
}]

The field names are case sensitive. So, a reference to Field1 will not match the "field1" in the examples above.

Spaces inside quotes for the field names are significant. So, if the record contained "field1 ", then a reference to payload.field1 does not match.

External JSON files can be compressed in GZIP format. Other forms of file compression are not supported.