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

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;

Result:

 *** 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);

Result:

 *** 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