This example shreds a table of DATASET values with the CSV storage format. The row in my_table with the ID 1 has the CSV value:
Item ID,Item Name,Quantity,Price
10021,basketball,10,29.99
10029,jersey,20,15.99
10032,shoes,20,79.99
SELECT * FROM DATASET_TABLE (
ON (SELECT id, csvCol FROM my_table WHERE id=1)
USING rowexpr('')
colexpr(
'[ {"dotnotation" : "$..Item Name",
"type" : "CHAR(40)"},
{"dotnotation" : "$..Price",
"type" : "DECIMAL(9,2)"} ]')
) AS t1(id, Item_Name, Price);
The resulting table has three rows:
| ID | Item_name | Price |
|---|---|---|
| 1 | basketball | 29.99 |
| 1 | jersey | 15.99 |
| 1 | shoes | 79.99 |