To make the example simple, constants are used when possible from a column.
SELECT * FROM DATASET_Table (
ON (SELECT id, DatasetCol, 'CA' AS state, 'USA' AS nation
FROM my_table WHERE id=1)
USING rowexpr('$.schools[*]')
colexpr(
'[ {"dotnotation" : "$.name",
"type" : "CHAR(20)"},
{"dotnotation" : "$.type",
"type" : "VARCHAR(20)"}]')
) AS JT(id, name, "type", State, Nation);
*** Query completed. 4 rows found. 5 columns returned.
*** Total elapsed time was 1 second.
id name type State Nation
----------- -------- -------------------- ----- ------
1 Lake elementary CA USA
1 Madison middle CA USA
1 Rancho high CA USA
1 UCI college CA USA