Collections:
JSON_TABLE() - Inline Table with JSON Array
How to create an inline table with a JSON Array using the JSON_TABLE() function?
✍: FYIcenter.com
JSON_TABLE(json, path COLUMNS (column_list) AS alias) is a MySQL built-in function that creates an inline table by parsing JSON array members.
For example: the following statement creates a 1-column table with string members of a JSON array.
SELECT * FROM JSON_TABLE( '["Orange", "Banana", "Apple"]', '$[*]' COLUMNS (name VARCHAR(8) PATH '$') ) AS Fruits; -- +--------+ -- | name | -- +--------+ -- | Orange | -- | Banana | -- | Apple | -- +--------+
The following statement converts a JSON array of JSON objects into an inline table.
SELECT * FROM JSON_TABLE( '[{"n": "Orange", "p": 3.99}, {"n": "Banana", "p": 2.99}, {"n": "Apple", "p": 1.99}]', '$[*]' COLUMNS ( name VARCHAR(8) PATH '$.n', price DECIMAL(6,2) PATH '$.p') ) AS Sales; -- +--------+-------+ -- | name | price | -- +--------+-------+ -- | Orange | 3.99 | -- | Banana | 2.99 | -- | Apple | 1.99 | -- +--------+-------+
The following statement converts a JSON array of JSON objects with different keys into an inline table. As you can see, convert JSON object keys into a column is not easy. So you should avoid using JSON objects with different keys.
SELECT JSON_VALUE(JSON_KEYS(item), '$[0]') AS name, price FROM JSON_TABLE( '[{"Orange": 3.99}, {"Banana": 2.99}, {"Apple": 1.99}]', '$[*]' COLUMNS (item JSON PATH '$', price JSON PATH '$.*') ) AS Sales; -- +--------+-------+ -- | name | price | -- +--------+-------+ -- | Orange | 3.99 | -- | Banana | 2.99 | -- | Apple | 1.99 | -- +--------+-------+
If the input JSON array has child JSON arrays, you can use the NESTED PATH clause to create child tables and join them with the parent table. For example:
SELECT * FROM JSON_TABLE( '[{"n": "Orange", "m": [{"c": "USD", "p": 3.99}, {"c": "CAD", "p": 5.29}] }, {"n": "Banana", "m": [{"c": "USD", "p": 2.99}, {"c": "CAD", "p": 3.99}] }, {"n": "Apple", "m": [{"c": "USD", "p": 1.99}, {"c": "CAD", "p": 2.69}] } ]', '$[*]' COLUMNS ( name VARCHAR(8) PATH '$.n', NESTED PATH '$.m[*]' COLUMNS ( currency VARCHAR(4) PATH '$.c', price DECIMAL(6,2) PATH '$.p') ) ) AS Sales; -- +--------+----------+-------+ -- | name | currency | price | -- +--------+----------+-------+ -- | Orange | USD | 3.99 | -- | Orange | CAD | 5.29 | -- | Banana | USD | 2.99 | -- | Banana | CAD | 3.99 | -- | Apple | USD | 1.99 | -- | Apple | CAD | 2.69 | -- +--------+----------+-------+
Reference information of the JSON_TABLE() function:
JSON_TABLE(json, path COLUMNS (column_list) AS alias): rows Creates an inline table by parsing JSON array members Arguments, return value and availability: json: Required. The JSON array to be parsed. path: Required. The location of a child array to be parsed. COLUMNS (column_list): Required. The column definitions. AS alias: Required: The inline table name. rows: Return value. The output rows of parsed result. Available since MySQL 8.0. COLUMNS (column_list) supports the following syntaxes: name FOR ORDINALITY: INTEGER column for row sequence starting from 1. name type PATH path on_empty on_error: Extracted value at a given location. name type EXISTS PATH path: 1 if value exists at a given location. NESTED PATH path COLUMNS (column_list): Outer join with a child inline table.
⇒ JSON_TYPE() - Detecting Type of JSON Value
⇐ JSON_STORAGE_SIZE() - Storage Size of JSON Value
2023-12-16, 246🔥, 0💬
Popular Posts:
How to download and install Microsoft SQL Server Management Studio Express in SQL Server? Microsoft ...
How To Create a Dynamic Cursor with the DYNAMIC Option in SQL Server Transact-SQL? If the underlying...
What To Do If the StartDB.bat Failed to Start the XE Instance in Oracle? If StartDB.bat failed to st...
How To Recover a Dropped Index in Oracle? If you have the recycle bin feature turned on, dropped ind...
How To Fix the INSERT Command Denied Error in MySQL? The reason for getting the "1142: INSERT comman...