JSON_TABLE – The Best of Both Worlds

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:

CREATE TABLE t1(json_col JSON);
 
INSERT INTO t1 VALUES (
    '{ "people": [
        { "name":"John Smith",  "address":"780 Mission St, San Francisco, CA 94103"}, 
        { "name":"Sally Brown",  "address":"75 37th Ave S, St Cloud, MN 94103"}, 
        { "name":"John Johnson",  "address":"1262 Roosevelt Trail, Raymond, ME 04071"}
     ] }'
);

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

SELECT people.* 
FROM t1, 
     JSON_TABLE(json_col, '$.people[*]' COLUMNS (
                name VARCHAR(40)  PATH '$.name',
                address VARCHAR(100) PATH '$.address')
     ) people;

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:

SELECT people.* 
FROM t1, 
     JSON_TABLE(json_col, '$.people[*]' COLUMNS (
                name VARCHAR(40)  PATH '$.name',
                address VARCHAR(100) PATH '$.address')
     ) people;
WHERE people.name LIKE '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:

SELECT JSON_OBJECT("people", 
       JSON_ARRAYAGG(JSON_OBJECT("name", name, "address", address))) json_doc
FROM t1, 
     JSON_TABLE(json_col, '$.people[*]' COLUMNS (
                name VARCHAR(40)  PATH '$.name',
                address VARCHAR(100) PATH '$.address')
     ) people;
WHERE people.name LIKE 'John%';

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:

[
  {
    "father": "John",
    "mother": "Mary",
    "children": [
      {
        "age": 12,
        "name": "Eric"
      },
      {
        "age": 10,
        "name": "Beth"
      }
    ],
    "marriage_date": "2003-12-05"
  },
  {
    "father": "Paul",
    "mother": "Laura",
    "children": [
      {
        "age": 9,
        "name": "Sarah"
      },
      {
        "age": 3,
        "name": "Noah"
      },
      {
        "age": 1,
        "name": "Peter"
      }
    ]
  }
]

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:

JSON_TABLE (families, '$[*]' COLUMNS (	
            id FOR ORDINALITY,
            father VARCHAR(30) PATH '$.father',
            married INTEGER EXISTS PATH '$.marriage_date',
            NESTED PATH '$.children[*]' COLUMNS (
              child_id FOR ORDINALITY,
              child VARCHAR(30) PATH '$.name',
              age INTEGER PATH '$.age') )	
)

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:

SELECT father, COUNT(*) "#children", AVG(age) "age average"
FROM t,
     JSON_TABLE (families, '$[*]' COLUMNS (
                 id FOR ORDINALITY,
                 father VARCHAR(30) PATH '$.father',
                 NESTED PATH '$.children[*]' COLUMNS (
                    age INTEGER PATH '$.age' ) ) 
     ) fam
GROUP BY id, father; 

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:

SELECT JSON_ARRAYAGG(fam_obj) families
FROM (
  SELECT JSON_MERGE_PATCH(family,
           JSON_OBJECT("#children", COUNT(*), "avg_age" , AVG(age))) fam_obj
  FROM t, 
       JSON_TABLE (families, '$[*]' COLUMNS (
                   id FOR ORDINALITY,
                   family JSON PATH '$',
                   NESTED PATH '$.children[*]' COLUMNS (
                     age INTEGER PATH '$.age' ) ) 
       ) fam
   GROUP BY id, family) fams;

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 !