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
2024-11-23, 849🔥, 0💬
Popular Posts:
How To Connect to a MySQL Server with a Port Number in MySQL? If you want to connect a MySQL server ...
Where to find answers to frequently asked questions on Conditional Statements and Loops in SQL Serve...
What Is Program Global Area (PGA) in Oracle? A Program Global Area (PGA) is a memory buffer that is ...
How To Generate CREATE TABLE Script on an Existing Table in SQL Server? If you want to know how an e...
How to set database to be READ_ONLY in SQL Server? Databases in SQL Server have two update options: ...