How Can MySQL\'s JSON_TABLE Function Convert JSON Arrays into Rows?

Converting JSON Arrays to Rows in MySQL

In MySQL 5.7 and earlier versions, manipulating JSON can be challenging, especially when converting JSON arrays into rows. However, in MySQL 8, the new JSON_TABLE function provides a straightforward solution.

JSON_TABLE Function

The JSON_TABLE function allows you to easily extract data from a JSON document and map it to a relational table schema. To convert a JSON array into rows, use the following syntax:

SELECT *
FROM JSON_TABLE(
  <json_array>,
  "$[*]"
  COLUMNS(
    <column_name> <data_type> PATH "$"
  )
) <table_alias>;</table_alias></data_type></column_name></json_array>

Example

Consider a JSON array:

[5, 6, 7]

You can convert it into a table using JSON_TABLE:

SELECT *
FROM JSON_TABLE(
  '[5, 6, 7]',
  "$[*]"
  COLUMNS(
    Value INT PATH "$"
  )
) data;

The output will be:

| Value |
|---|---|
| 5 |
| 6 |
| 7 |

General String Splitting

MySQL lacks a native string splitting function. However, you can use JSON_TABLE to achieve a similar result:

set @delimited = 'a,b,c';

SELECT *
FROM JSON_TABLE(
  CONCAT('["', REPLACE(@delimited, ',', '", "'), '"]'),
  "$[*]"
  COLUMNS(
    Value varchar(50) PATH "$"
  )
) data;

This will split the delimited string into a JSON array and then convert it into a table.

The above is the detailed content of How Can MySQL\'s JSON_TABLE Function Convert JSON Arrays into Rows?. For more information, please follow other related articles on the PHP Chinese website!