Convert JSON to Arrays in MySQL

In MySQL, you can convert JSON strings to arrays by directly accessing elements within a JSON array using the JSON_EXTRACT() function or by iterating over JSON data with MySQL functions like JSON_ARRAYAGG() for aggregation.

In this tutorial, you will learn how to use the JSON_EXTRACT() function in MySQL to access elements within a JSON array.

Working with JSON Arrays in MySQL

In MySQL, JSON data can be stored and manipulated using JSON functions. JSON arrays can be queried to extract elements at specific positions or to retrieve all elements in an array. The JSON_EXTRACT() function allows you to target specific values within a JSON array, making it useful for data retrieval and processing.

Examples: Extracting Elements from JSON Arrays in MySQL

Here are examples demonstrating how to use JSON_EXTRACT() to access and manipulate JSON arrays in MySQL.

1. Extracting a Single Element from a JSON Array

To extract the second element from a JSON array '["apple", "banana", "cherry"]':

</>
Copy
SELECT JSON_EXTRACT('["apple", "banana", "cherry"]', '$[1]') AS second_fruit;

This query returns "banana", as it is the element located at index 1 in the JSON array (arrays are zero-indexed in JSON).

Convert JSON to Arrays in MySQL - Example: Extracting a Single Element from a JSON Array

2. Extracting All Elements of a JSON Array as Rows

If you want to retrieve all elements within a JSON array, such as '["apple", "banana", "cherry"]', you can use JSON_TABLE() to parse the array into rows (available in MySQL 8.0 or later):

</>
Copy
SELECT jt.fruit
FROM JSON_TABLE('["apple", "banana", "cherry"]', '$[*]' COLUMNS (fruit VARCHAR(20) PATH '$')) AS jt;

This query outputs each element in the array as a separate row in the result set, with one row for each fruit.

Convert JSON to Arrays in MySQL - Example: Extracting All Elements of a JSON Array as Rows

3. Extracting Elements from JSON Array in a Table Column

Suppose you have a table orders with a JSON column items containing arrays of products. To retrieve the first item in each array, use JSON_EXTRACT() as follows:

</>
Copy
SELECT order_id, JSON_EXTRACT(items, '$[0]') AS first_item
FROM orders;

This query extracts the first element of the items array for each row in the orders table.

4. Counting Elements in a JSON Array

To count the number of elements in a JSON array '["apple", "banana", "cherry"]', use the JSON_LENGTH() function:

</>
Copy
SELECT JSON_LENGTH('["apple", "banana", "cherry"]') AS array_length;

This query returns 3, indicating the number of elements in the JSON array.

Convert JSON to Arrays in MySQL - Example: Counting Elements in a JSON Array