How the JSON_TABLE() function works in Mariadb?

The JSON_TABLE() function is a powerful and versatile tool for extracting data from JSON documents and converting them into tabular format. It allows you to specify a path expression to navigate through the JSON document, and a set of columns to project the JSON values into. You can also apply filters, aliases, and default values to the columns. The JSON_TABLE() function returns a relational table that can be joined with other tables or queried further.

Syntax

The syntax of the JSON_TABLE() function is as follows:

JSON_TABLE(
  json_doc,
  path COLUMNS(
    column_definition [,
    column_definition] ...
  )
)

The parameters are:

json_doc: The JSON document from which to extract data. It can be a column, a variable, or a literal.
path: The path expression that specifies the JSON array or object to be converted into a table. It must start with ‘$’ and follow the JSON path syntax.
column_definition: The definition of a column to be projected from the JSON document. It consists of four parts: the column name, the data type, the path expression, and the optional modifiers. The column name can be any valid identifier or a quoted string. The data type can be any valid SQL data type, such as INT, VARCHAR, DATE, etc. The path expression can be an absolute path starting with ‘$’, or a relative path starting with ‘.’ or ‘..’. The optional modifiers are:
- AS PATH: This modifier indicates that the column should store the path expression of the JSON value, rather than the value itself.
- AS JSON: This modifier indicates that the column should store the JSON value as a JSON document, rather than converting it to the specified data type.
- DEFAULT default_value ON EMPTY: This modifier specifies a default value to be used when the JSON value is empty (null, empty array, or empty object).
- DEFAULT default_value ON ERROR: This modifier specifies a default value to be used when the JSON value cannot be converted to the specified data type, or the path expression is invalid.
- NULL ON EMPTY: This modifier indicates that the column should be null when the JSON value is empty.
- ERROR ON ERROR: This modifier indicates that the function should raise an error when the JSON value cannot be converted to the specified data type, or the path expression is invalid.

Examples

In this section, we will show some examples of using the JSON_TABLE() function with different JSON documents and column definitions.

Example 1: Simple array

Suppose we have a JSON document that contains an array of numbers, as follows:

SET @json = '[1, 2, 3, 4, 5]';

We can use the JSON_TABLE() function to convert this array into a table with one column, as follows:

SELECT *
FROM JSON_TABLE(
  @json,
  '$[*]'
  COLUMNS(
    num INT PATH '$'
  )
) AS jt;

The output is:

+------+
| num  |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+

The path expression '$[*]' means to match all the elements of the root array. The column definition num INT PATH '$' means to project the JSON value as an integer column named num.

Example 2: Nested array

Suppose we have a JSON document that contains an array of arrays, as follows:

SET @json = '[[1, 2], [3, 4], [5, 6]]';

We can use the JSON_TABLE() function to convert this nested array into a table with two columns, as follows:

SELECT *
FROM JSON_TABLE(
  @json,
  '$[*]'
  COLUMNS(
    a INT PATH '$[0]',
    b INT PATH '$[1]'
  )
) AS jt;

The output is:

+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
|    3 |    4 |
|    5 |    6 |
+------+------+

The path expression '$[*]' means to match all the elements of the root array. The column definitions a INT PATH '$[0]' and b INT PATH '$[1]' mean to project the first and second elements of each subarray as integer columns named a and b.

Example 3: Object array

Suppose we have a JSON document that contains an array of objects, as follows:

SET @json = '[{"name": "Alice", "age": 25}, {"name": "Bob", "age": 30}, {"name": "Charlie", "age": 35}]';

We can use the JSON_TABLE() function to convert this object array into a table with two columns, as follows:

SELECT *
FROM JSON_TABLE(
  @json,
  '$[*]'
  COLUMNS(
    name VARCHAR(20) PATH '$.name',
    age INT PATH '$.age'
  )
) AS jt;

The output is:

+---------+------+
| name    | age  |
+---------+------+
| Alice   |   25 |
| Bob     |   30 |
| Charlie |   35 |
+---------+------+

The path expression '$[*]' means to match all the elements of the root array. The column definitions name VARCHAR(20) PATH '$.name' and age INT PATH '$.age' mean to project the name and age properties of each object as varchar and integer columns named name and age.

Example 4: Nested object

Suppose we have a JSON document that contains a nested object, as follows:

SET @json = '{"person": {"name": "David", "address": {"city": "New York", "zip": "10001"}}}';

We can use the JSON_TABLE() function to convert this nested object into a table with three columns, as follows:

SELECT *
FROM JSON_TABLE(
  @json,
  '$.person'
  COLUMNS(
    name VARCHAR(20) PATH '$.name',
    city VARCHAR(20) PATH '$.address.city',
    zip VARCHAR(10) PATH '$.address.zip'
  )
) AS jt;

The output is:

+-------+----------+-------+
| name  | city     | zip   |
+-------+----------+-------+
| David | New York | 10001 |
+-------+----------+-------+

The path expression '$person' means to match the person property of the root object. The column definitions name VARCHAR(20) PATH '$.name', city VARCHAR(20) PATH '$.address.city', and zip VARCHAR(10) PATH '$.address.zip' mean to project the name, city, and zip properties of the person object and its nested address object as varchar columns named name, city, and zip.

The JSON_TABLE() function is not the only function that can extract data from JSON documents. There are some other related functions that can be used for different purposes, such as:

JSON_VALUE(): This function extracts a scalar value from a JSON document, given a path expression. It returns the value as a SQL type, such as INT, VARCHAR, DATE, etc. If the path expression does not match a scalar value, or the value cannot be converted to the specified type, the function returns null or an error, depending on the ON EMPTY and ON ERROR clauses.
JSON_EXTRACT(): This function extracts a value or values from a JSON document, given one or more path expressions. It returns the value or values as a JSON document, preserving the original type and format. If the path expression does not match any value, the function returns null.
JSON_SEARCH(): This function searches for a given string in a JSON document, and returns the path expression of the first or all matches. It can perform a case-sensitive or case-insensitive search, and can specify a scope of one or all to limit the search to the first or all occurrences. If the string is not found, the function returns null.

Conclusion

The JSON_TABLE() function is a useful function for transforming JSON data into relational tables that can be queried and manipulated using SQL. It offers a lot of flexibility and control over how to extract and project the JSON values into columns. It can also handle various scenarios of missing, empty, or invalid values by applying default values or raising errors. The JSON_TABLE() function is not the only function that can work with JSON data, as there are other related functions that can perform different tasks, such as extracting, searching, or modifying JSON values. By combining these functions, you can leverage the power of both JSON and SQL to handle complex and diverse data sources.

Syntax

Examples

Example 1: Simple array

Example 2: Nested array

Example 3: Object array

Example 4: Nested object

Related Functions

Conclusion