One of the new JSON functions in MySQL 8.0 is JSON_TABLE. It is also MySQL’s first table function. That is, the return value is not a scalar value, but a result set. JSON_TABLE will convert (part of) a JSON document into a relational table. In this blog post, I will show you how to do this and discuss how JSON_TABLE enables new ways of processing JSON data with SQL.

JSON_TABLE Example

First, I will create a table t1 with a column of type JSON and insert a single JSON document into this table:

I can use the following SQL query to convert this document into a relational table:

Since JSON_TABLE returns a result set, it may be used in the FROM clause. JSON_TABLE takes the following arguments:

1. The JSON data source: This expression may refer to columns of preceding tables in the FROM list. In this example, json_col refers to the column that contains our JSON document. (Note that, as specified by the SQL standard, there is an implicit lateral join between the preceding table and the JSON_TABLE referring to this table. In other words, the JSON_TABLE function will be “called” once for each row of the preceding table.)
2. Path to the JSON array which should be converted into a table. In this example, it is the objects of the people array.
3. The columns of the table to be returned with column name, type and the path within the JSON objects to where the value should be found.

We also need to give the returned table a name, and here we call it people.

The above query will return the following result:

name	address
John Smith	780 Mission St, San Francisco, CA 94103
Sally Brown	75 37th Ave S, St Cloud, MN 9410
John Johnson	1262 Roosevelt Trail, Raymond, ME 04071

Relational operations on JSON documents

When we have used JSON_TABLE to convert our JSON document to a table, we can use the “SQL tool set” and perform relational operations like selection and aggregation on our data. For example, we can use this query to select only people called John:

name	address
John Smith	780 Mission St, San Francisco, CA 94103
John Johnson	1262 Roosevelt Trail, Raymond, ME 04071

Using another new function in MySQL 8.0, JSON_ARRAYAGG, we can now convert our result back into a JSON document:

json_doc
{"people": [{"name": "John Smith", "address": "780 Mission St, San Francisco, CA 94103"}, {"name": "John Johnson", "address": "1262 Roosevelt Trail, Raymond, ME 04071"}]}

As shown here, we can combine JSON_TABLE and JSON_ARRAYAGG to perform relational operations on a JSON document.

Nested JSON Arrays

JSON_TABLE can also handle nested JSON arrays. Given the following JSON array with family objects that have arrays with children objects:

We want to convert this document to a table with one row for each child:

id	father	married	child_id	child	age
1	John	1	1	Eric	12
1	John	1	2	Beth	10
2	Paul	0	1	Sarah	9
2	Paul	0	2	Noah	3
2	Paul	0	3	Peter	1

We can use the following JSON_TABLE call to extract this information from the nested JSON arrays:

We use NESTED PATH to specify which values should be extracted from the sub-array. This examples also shows that we can assign IDs to rows by specifying FOR ORDINALITY instead of a path. We can also use EXISTS PATH to check whether a path exists. Here, married will be 1 if a marriage date is found, 0 otherwise.

SQL aggregation on JSON data

Once we have used JSON_TABLE to convert JSON data to relational tables, we can leverage SQL aggregation to calculate count, sum, average etc of the JSON data. Using the latest example above, this query will compute the average age of children in each family:

father	#children	age average
John	2	11.0000
Paul	3	4.3333

We can even put this computed data back into the JSON document using the JSON_MERGE_PATCH function:

Here we merge the existing family object with an object constructed from the computed count and average. JSON_ARRAYAGG will then put all the merged objects back into an array.

Conclusion

In this blog post, I have shown how you can use JSON_TABLE to perform relational operations on JSON data. Using JSON_ARRAYAGG, you can go in the opposite direction and convert a result set into a JSON document. This enables you to get the best of both worlds; you can store your data in JSON, but a the same time leverage the power of SQL.

Thank you for using MySQL !