Examples | DATASET_TABLE Function | Teradata Vantage - 17.10 - Example: Using the Built-In Cast from Varbyte to DATASET to Perform the Insert - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - DATASET Data Type

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1198-171K
Language
English (United States)

The data in its text JSON format is shown further on, to aid in understanding the data.

The example uses the storage format AVRO.

CREATE TABLE my_table (id INTEGER, DatasetCol DATASET STORAGE FORMAT AVRO);
INSERT INTO my_table (1, '7B2274797065223A227265636F7264222C226E616D65223A227265635F30222C226669656C6473223A5B7B226E616D65223A226E616D65222C2274797065223A22737472696E67227D2C7B226E616D65223A22616765222C2274797065223A22696E74227D2C7B226E616D65223A227363686F6F6C73222C2274797065223A7B2274797065223A226172726179222C226974656D73223A7B2274797065223A227265636F7264222C226E616D65223A227265635F31222C226669656C6473223A5B7B226E616D65223A226E616D65222C2274797065223A22737472696E67227D2C7B226E616D65223A2274797065222C2274797065223A22737472696E67227D5D7D7D7D2C7B226E616D65223A226A6F62222C2274797065223A22737472696E67227D5D7D000E43616D65726F6E3008084C616B6514656C656D656E746172790E4D616469736F6E0C6D6964646C650C52616E63686F0868696768065543490E636F6C6C656765001470726F6772616D6D6572'xb); 
INSERT INTO my_table (2, '7B2274797065223A227265636F7264222C226E616D65223A227265635F30222C226669656C6473223A5B7B226E616D65223A226E616D65222C2274797065223A22737472696E67227D2C7B226E616D65223A22616765222C2274797065223A22696E74227D2C7B226E616D65223A227363686F6F6C73222C2274797065223A7B2274797065223A226172726179222C226974656D73223A7B2274797065223A227265636F7264222C226E616D65223A227265635F31222C226669656C6473223A5B7B226E616D65223A226E616D65222C2274797065223A22737472696E67227D2C7B226E616D65223A2274797065222C2274797065223A22737472696E67227D5D7D7D7D5D7D000E4D656C697373612E08084C616B6514656C656D656E746172790E4D616469736F6E0C6D6964646C650C52616E63686F0868696768144D69726120436F7374610E636F6C6C65676500'xb);
INSERT INTO my_table (3, '7B2274797065223A227265636F7264222C226E616D65223A227265635F30222C226669656C6473223A5B7B226E616D65223A226E616D65222C2274797065223A22737472696E67227D2C7B226E616D65223A22616765222C2274797065223A22696E74227D2C7B226E616D65223A227363686F6F6C73222C2274797065223A7B2274797065223A226172726179222C226974656D73223A7B2274797065223A227265636F7264222C226E616D65223A227265635F31222C226669656C6473223A5B7B226E616D65223A226E616D65222C2274797065223A22737472696E67227D2C7B226E616D65223A2274797065222C2274797065223A22737472696E67227D5D7D7D7D2C7B226E616D65223A226A6F62222C2274797065223A22737472696E67227D5D7D0008416C65783208084C616B6514656C656D656E746172790E4D616469736F6E0C6D6964646C650C52616E63686F08686967680A435355534D0E636F6C6C6567650006435041'xb); 
INSERT INTO my_table (4, '7B2274797065223A227265636F7264222C226E616D65223A227265635F30222C226669656C6473223A5B7B226E616D65223A226E616D65222C2274797065223A22737472696E67227D2C7B226E616D65223A22616765222C2274797065223A22696E74227D2C7B226E616D65223A227363686F6F6C73222C2274797065223A7B2274797065223A226172726179222C226974656D73223A7B2274797065223A227265636F7264222C226E616D65223A227265635F31222C226669656C6473223A5B7B226E616D65223A226E616D65222C2274797065223A22737472696E67227D2C7B226E616D65223A2274797065222C2274797065223A22737472696E67227D5D7D7D7D2C7B226E616D65223A226A6F62222C2274797065223A22737472696E67227D5D7D000A44617669643206084C616B6514656C656D656E746172790E4D616469736F6E0C6D6964646C650C52616E63686F08686967680028736D616C6C20627573696E657373206F776E6572'xb);

SELECT id, DatasetCol.toJson() FROM my_table ORDER BY 1;

 *** Query completed. 4 rows found. 2 columns returned.
 *** Total elapsed time was 1 second.

         id DatasetCol.TOJSON()
----------- -----------------------------------------------------------
          1 {"name":"Cameron","age":24,"schools":[{"name":"Lake","type":"elementary"},{"name":"Madison","type":"middle"},{"name":"Rancho","type":"high"},{"name":"UCI","type":"college"}],"job":"programmer"}
          2 {"name":"Melissa","age":23,"schools":[{"name":"Lake","type":"elementary"},{"name":"Madison","type":"middle"},{"name":"Rancho","type":"high"},{"name":"Mira Costa","type":"college"}]}
          3 {"name":"Alex","age":25,"schools":[{"name":"Lake","type":"elementary"},{"name":"Madison","type":"middle"},{"name":"Rancho","type":"high"},{"name":"CSUSM","type":"college"}],"job":"CPA"}
          4 {"name":"David","age":25,"schools":[{"name":"Lake","type":"elementary"},{"name":"Madison","type":"middle"},{"name":"Rancho","type":"high"}],"job":"small business owner"}

SELECT * FROM DATASET_Table (
	ON (SELECT id, DatasetCol FROM my_table WHERE id=1)
	USING rowexpr('$.schools[*]')  
	colexpr(
		'[ {"dotnotation" : "$.name",
		    "type" : "CHAR(20)"},
		   {"dotnotation" : "$.type",
              "type" : "VARCHAR(20)"},
		   {"dotnotation" : "$.name",
		    "type" : "VARCHAR(20)",
		    "fromRoot":true} ]')
) AS JT(id, schoolName, "type", studentName);

 *** Query completed. 4 rows found. 4 columns returned.
 *** Total elapsed time was 1 second.

         id  schoolName    type                  studentName
-----------  ------------  --------------------  ---------------
          1  Lake          elementary            Cameron
          1  Madison       middle                Cameron
          1  Rancho        high                  Cameron
          1  UCI           college               Cameron