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