For simple shredding cases, you can use the INSERT statement to shred the JSON data into a table. For example, consider the following JSON data:
{"pkey":123,"val":1234}
You can use the following INSERT statement to shred this data into the MyTable table:
INSERT INTO MyTable JSON '{"pkey":123,"val":1234}';
You can also use the JSON_TABLE table operator to shred this data, but the SQL is more complicated than using the INSERT statement.
INSERT INTO MyTable
SELECT pkey, val FROM JSON_Table (
ON (SELECT 1, new JSON ('{"pkey":123,"val":1234}'))
USING rowexpr('$')
colexpr('[{"jsonpath" : "$.pkey", "type" : "INTEGER"},
{"jsonpath" : "$.val", "type" : "INTEGER"}]')
) AS JT(dummy, pkey, val);
Another advantage of using the INSERT statement is that this operation is a one-AMP operation when shredding JSON data that is a string literal. This can result in a performance improvement.
For parameterized SQL, INSERT JSON supports VARCHAR, CLOB, and external JSON data types; however, the operation is handled as a two-AMP process. For improved performance, it is better to directly specify a JSON literal.
Rules for Shredding JSON Data Using the INSERT JSON Statement
- You can only shred JSON data in text format. You cannot use the INSERT statement to shred JSON data that is in one of the binary formats such as BSON or UBJSON.
- The shredded data is in VARCHAR format and implicit casting is used to convert the VARCHAR data to the target table column format. If the VARCHAR data cannot be CAST to the target column format, the insertion fails. For example, if the target column is VARBYTE, casting values other than NULL to VARBYTE will fail because JSON does not have a matching textual value for binary data.
- The INSERT statement only handles a single row of JSON data with JSON OBJECT at the root. That is, the JSON data starts with '{' as the first non-white space character. You cannot insert multiple rows of data using the INSERT statement.
- The INSERT statement supports Load Isolation options if the target table is an LDI table.
- The target table must be a table and not a view.
- The column name matching is not case sensitive.
- If the same column is matched multiple times, the data stored is the last match.
- If any of the target table columns is NOT NULL, and the JSON input data does not contain any data for the column, the following rules apply:
- If the target column does not have a DEFAULT value, an error is thrown.
- If the target column has a DEFAULT value and the JSON data is a string literal, the DEFAULT value is inserted into the target table.
- If the target column has a DEFAULT value, and INSERT JSON uses parameterized SQL, the DEFAULT value is ignored and an error is thrown. This is another reason why specifying a JSON literal is preferable to using parameterized SQL.
For more information about INSERT JSON, INSERT, and INSERT SELECT, see Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.