Accessing DATASET Data Using Dot Notation

Teradata Vantageā„¢ DATASET Data Type

brand
Software
Teradata Vantage
prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1198-162K
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);