UNNEST()

Function Description

The UNNEST function is used to expand a column or parameter of an array type within JSON type data into a table. It splits elements in arrays in JSON type data into separate rows, allowing the array elements to be processed separately or joined with other tables.

When an empty array is entered, the UNNEST function returns an empty table because there are no elements to expand.

When a NULL value is entered, the UNNEST function returns an empty table because NULL is not a valid array.

Function Syntax

> UNNEST(array_expression)

Parameter definition

Parameters	Description
array_expression	Required parameters. is an array within JSON data, which can be an array column, array constant, or return value of an array function.

Example

• Example 1:

-- Expand an array of strings containing JSON objects, '{"a":1}' is a string array containing a single element. This element is a string representing a JSON object.
> select * from unnest('{"a":1}') u;
+-------------------+------+--------+----------+-----------+------------+
| col | seq | key | path | index | value | this |
+-------------------+------+--------+----------+-----------+------------+
| UNNEST_DEFAULT | 0 | a | $.a | NULL | 1 | {"a": 1} |
+-------------------+------+--------+----------+-----------+------------+
1 row in set (0.00 sec)

-- Expand an array of strings containing integers '[1,2,3]' and use the alias u to represent the expanded column.
> select * from unnest('[1,2,3]') u;
+-------------------+------+--------+------------+---------------------+
| col | seq | key | path | index | value | this |
+-------------------+------+--------+------------+---------------------+
| UNNEST_DEFAULT | 0 | NULL | $[0] | 0 | 1 | [1, 2, 3] |
| UNNEST_DEFAULT | 0 | NULL | $[1] | 1 | 2 | [1, 2, 3] |
| UNNEST_DEFAULT | 0 | NULL | $[2] | 2 | 3 | [1, 2, 3] |
+-------------------+------+--------+------------+---------------------+
3 rows in set (0.00 sec)

-- Expand an array of strings containing integers '[1,2,3]' and select the first element of the array to return as part of the result set. '$[0]' is a path expression that specifies the array element to be selected, true is a boolean indicating whether the path is returned, and uses the alias u to represent the expanded column.
> select * from unnest('[1,2,3]','$[0]',true) u;
+-------------------+------+-------+-------+-------+------+
| col | seq | key | path | index | value | this |
+-------------------+------+-------+-------+-------+------+
| UNNEST_DEFAULT | 0 | NULL | $[0] | NULL | NULL | 1 |
+-------------------+------+-------+-------+-------+------+
1 row in set (0.00 sec)

• Example 2:

create table t1 (a json,b int);
insert into t1 values ​​('{"a":1,"b":[{"c":2,"d":3},false,4],"e":{"f":true,"g":[null,true,1.1]}}',1);
insert into t1 values ​​('[1,true,false,null,"aaa",1.1,{"t":false}]',2);
> select * from t1;
+----------------------------------------------------------------------------------------------------------------------------------+
| a | b |
+----------------------------------------------------------------------------------------------------------------------------------+
| {"a": 1, "b": [{"c": 2, "d": 3}, false, 4], "e": {"f": true, "g": [null, true, 1.1]}} | 1 |
| [1, true, false, null, "aaa", 1.1, {"t": false}] | 2 |
+----------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

-- Expand the element of array t1.a from table t1 and select the expanded element to return as part of the result set. "$a" is a path expression that specifies the array element to be selected; true is a boolean indicating whether the path is returned; f.* is used to select all columns after expansion; f is an alias for the UNNEST function, representing the expanded result
mysql> select f.* from t1,unnest(t1.a, "$.a", true) as f;
+------+-----+-----+-----+------+-----+-----+
| col | seq | key | path | index | value | this |
+------+-----+-----+-----+------+-----+-----+
| t1.a | 0 | NULL | $.a | NULL | NULL | 1 |
| t1.a | 0 | NULL | $.a | NULL | NULL | 1 |
+------+-----+-----+-----+------+-----+-----+
2 rows in set (0.00 sec)