Converting JSON Array to Rows in MySQL: A Step-by-Step Guide

When working with large datasets that contain nested arrays or tables, it can be challenging to extract and analyze the data efficiently. In this article, we will explore how to use the `JSON_EXTRACT()` and `JSON_TABLE()` functions in MySQL 8.0 to convert JSON tables into tabular data for analysis and reporting.

Let's take a look at the Totals column in the ga_session dataset. The goal is to extract the array data from the JSON format and convert it into separate rows, one for each item in the ActionRecs array.

What are Nested Arrays/Tables?

A nested array/table is a table built with multiple key-value objects in a hierarchical format. A key-value object is a single record that consists of multiple named or indexed fields (or keys) paired with values.

JSON Data Type in MySQL

MySQL supports a native JSON data type defined by RFC 7159, which enables efficient access to data in JSON documents. JSON data can appear in two main forms: as a JSON string or as a native JSON document.

Converting JSON Array to Rows

To convert the JSON array into separate rows, we will use the `JSON_TABLE()` function, which is designed specifically for this purpose. Here's an example query that demonstrates how to do this:

```sql

SELECT *

FROM ga_sessions,

JSON_TABLE(

ga_sessions.Totals,

'$.ActionRecs'*'' COLUMNS (

ActionRecid INT PATH '$.ActionRecid',

ActionName VARCHAR(255) PATH '$.ActionName'

)

) AS ActionRecs;

```

In this example, we are selecting all columns from the `ga_sessions` table and then using the `JSON_TABLE()` function to extract the `ActionRecs` array from the JSON data. We specify the column names and paths for each field in the `COLUMNS` clause.

Benefits of Using the JSON Data Type

Using the native JSON data type provides several advantages over storing JSON-format strings in a string column. For example, it allows us to store large amounts of data efficiently, making it easier to work with complex datasets.

In this article, we have demonstrated how to use the `JSON_EXTRACT()` and `JSON_TABLE()` functions in MySQL 8.0 to convert JSON tables into tabular data for analysis and reporting. By following these steps, you can extract and analyze your JSON array data more efficiently and effectively.

Mysql json array to rows