How to extract JSON array into fields in MySQL?

1 year ago

Olivia Parker

1 minute

You can use the JSON_EXTRACT function in MySQL to extract fields from a JSON array.

Assuming you have a table named ‘data’ with a field named ‘json_data’ that stores a JSON array, you can convert the JSON array into a field using the following method:

SELECT json_data->>'$[0]' AS field1,
       json_data->>'$[1]' AS field2,
       json_data->>'$[2]' AS field3
FROM data

In the example above, json_data->>’$[0]’ indicates extracting the first element in the JSON array as the field1 field. Similarly, json_data->>’$[1]’ extracts the second element, and json_data->>’$[2]’ extracts the third element.

If there are more elements in your JSON array, you can continue to use similar syntax to extract more fields.