How to create an inline table with a JSON Array using the JSON_TABLE() function?

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

⇑ MySQL Functions on JSON Values

⇑⇑ MySQL Function References