The example shows JSON_TABLE using extra columns, in addition to the required columns of ID and JSON document. The column_expression_literal parameter requires a mapping of the columns in the row_expression_literal to the columns of the output table of this function, as this example demonstrates. To simplify this example, constants are used for the state and nation columns.
The example uses the table(s) created earlier.
SELECT * FROM JSON_Table (ON (SELECT id, jsonCol, 'CA' AS state, 'USA' AS nation FROM my_table WHERE id=1) USING rowexpr('$.schools[*]') colexpr('[ {"jsonpath" : "$.name", "type" : "CHAR(20)"}, {"jsonpath" : "$.type", "type" : "VARCHAR(20)"}]') ) AS JT(id, name, "type", State, Nation);
Result:
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