This is a brief overview of using dot notation with the DATASET data type. It discusses dot notation as it relates to DATASET. For more information about dot notation, see Teradata JSON.
Dot notation supports the following members of the JSONPath syntax:
- Recursive descent operator ( .. )
- Wildcards ( * ) - both in reference to named and indexed items
- Name/index lists ( [a,b,c] or [0,3,5] )
- Name/index slices ([c] or [5])
The items are used for both the JSON and DATASET data types. The following examples and rules use these new syntax pieces in the SELECT list and the WHERE clause. Note that not all portions of the JSONPath syntax are supported by the DATASET types, including JSONPath expressions and filters.
The return value of a dot notation expression on a DATASET data type is VARCHAR by default. If the referenced DATASET type is a column of a table with a schema defined at the column level, the expected data type is inferred from the schema and used as the expression return type, if possible. There are certain scenarios where it is not possible. For example, if a dot notation expression retrieves multiple children of a record, which have different data types:
SELECT column.record[childA, childB, childC];
The following tables are referenced in examples throughout the book for the AVRO storage format:
CREATE TABLE myAVROTable09( id INTEGER, avroFile DATASET STORAGE FORMAT Avro); avroFiles09.txt avro09.data|1 avro09.data 7B2274797065223A227265636F7264222C226E616D65223A227265635F30222C226669656C6473223A5B7B226E616D65223A2253616C65222C2274797065223A7B2274797065223A227265636F7264222C226E616D65223A227265635F31222C226669656C6473223A5B7B226E616D65223A224974656D5F4944222C2274797065223A22696E74227D2C7B226E616D65223A224974656D5F4E616D65222C2274797065223A22737472696E67227D2C7B226E616D65223A224974656D5F436F6C6F72222C2274797065223A22737472696E67227D2C7B226E616D65223A224974656D5F5374796C65222C2274797065223A22737472696E67227D2C7B226E616D65223A225175616E746974795F507572636861736564222C2274797065223A22696E74227D2C7B226E616D65223A224974656D5F5072696365222C2274797065223A22646F75626C65227D2C7B226E616D65223A22546F74616C5F5072696365222C2274797065223A22646F75626C65227D5D7D7D5D7D006E0E62696379636C650672656408626F79730200000000000059400000000000005940 .import vartext file avroFiles09.txt USING (c1 BLOB as deferred by name, c2 INTEGER) INSERT INTO myAVROTable09(:c2,:c1);
If using the CSV storage format, substitute CSV in the examples instead:
CREATE TABLE myCSVTable09( id INTEGER, csvFile DATASET STORAGE FORMAT CSV); csvFiles09.txt csv09.data|1 csv09.data ItemNum,ItemName,Quantity,Price 10,Basketball,15,24.99 20,Shovel,9,7.99 30,Paint Brush,10,3.99 .import vartext file csvFiles09.txt USING (c1 CLOB as deferred by name, c2 INTEGER) INSERT INTO myCSVTable09(:c2,:c1);