JSON_TABLE

MySQL

JSON_TABLE(expr, path COLUMNS (column_list) [AS] alias)

• expr: This parameter represents the JSON document that needs to be converted to a relational form. It is an SQL expression that returns a JSON value. Usually, a subquery or column name is used.
• path: It is a JSON path expression that specifies the object or the array in the JSON data to extract. ’$’ refers to the entire JSON document.
• columns (column_list): This option defines a list of columns to be produced by the function. Each column in the list should have a name and a data type. The syntax is “name type PATH string”.
• [as] alias: This optional parameter is used to set an alias name for the JSON_TABLE. This can be particularly useful in situations when querying multiple tables at once, as it helps to avoid any confusion emanating from having columns with the same name in different tables.

Example

SELECT *
FROM JSON_TABLE(
  '{"partner": "Google", "value": 18}',
  '$' COLUMNS(
    partner VARCHAR(255) PATH '$.partner',
    value VARCHAR(255) PATH '$.value'
  )
) AS jt;

Output

+--------+-------+
| partner | value |
+--------+-------+
| Google  | 18    |
+--------+-------+

Explanation

The JSON_TABLE function in MySQL takes a JSON object and transforms it into a relational format. In this example, it takes a JSON object with keys ‘partner’ and ‘value’, and creates a table where each key corresponds to a separate column. The key value pairs in the JSON object become the column name and value in the output table.

Oracle

JSON_TABLE( json_source CLOB, json_path VARCHAR2, [nested CLAUSE…], [COLUMNS column_definitions])RETURN JSON_tableview

• json_source clob: The input JSON document. It can be a literal string or an SQL expression that returns a JSON document.
• json_path varchar2: The point of interest within the JSON document specified by a JSON path expression. It extracts data from the given json_source based on this path.
• [nested clause…]: Optional parameter defining the hierarchical relationship between the derived JSON tables. It helps to handle nested data by specifying the structure of the JSON data and its relationship to the resultant rows and columns.
• [columns column_definitions]: Describes the columns of the resulting table. Each column has a name, datatype, path expression relative to the json_path values, and optionally an ON ERROR or ON EMPTY clause.
• return json_tableview: This clause is not actually a part of the function parameters. It is the result that is derived from transforming a JSON string into a relational format. The output has a JSON_tableview schema and each row corresponds to a distinct JSON object that matches the json_path.

Example

SELECT * FROM JSON_TABLE( '{"team": ["Jim", "John", "Jill"]}' , '$'
NESTED PATH '$.team[*]'
COLUMNS ( player VARCHAR2(30) PATH '$' )) ;

Output

PLAYER
------
Jim
John
Jill

Explanation

The JSON_TABLE function in the example allows to convert JSON data into rows and columns. The function is taking a JSON object that contains a single array named ‘team’. The function then creates a row for each element within the team array, assigning the value of each element to a column ‘player’.