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, 247🔥, 0💬
Popular Posts:
How to download and install SQL Server 2005 Sample Scripts in SQL Server? If you want to learn from ...
Where to find Oracle database server tutorials? Here is a collection of tutorials, tips and FAQs for...
How To Use GO Command in "sqlcmd" in SQL Server? "sqlcmd" is a command line client application to ru...
How To Turn on mysql Extension on the PHP Engine in MySQL? The "mysql" API extension is provided as ...
How To Verify Your PHP Installation in MySQL? PHP provides two execution interfaces: Command Line In...