unnest

UNNEST

函数说明

UNNEST是一个表函数,出现在sql的from子句中,用于将json[object|array]类型的数据展开为多行,每行包含json中的一个元素.

语法结构

> UNNEST(src[, path[, outer]])

相关参数

返回表结构

示例

> select *
> from unnest('{"a":1,"b":2,"c":3}') as u;
+----------------+------+------+------+-------+-------+--------------------------+
| col            | seq  | key  | path | index | value | this                     |
+----------------+------+------+------+-------+-------+--------------------------+
| UNNEST_DEFAULT |    0 | a    | $.a  |  NULL | 1     | {"a": 1, "b": 2, "c": 3} |
| UNNEST_DEFAULT |    0 | b    | $.b  |  NULL | 2     | {"a": 1, "b": 2, "c": 3} |
| UNNEST_DEFAULT |    0 | c    | $.c  |  NULL | 3     | {"a": 1, "b": 2, "c": 3} |
+----------------+------+------+------+-------+-------+--------------------------+

> select *
> from unnest('{"a":1,"b":2,"c":3}') as u
> where u.`key` = 'b';
+----------------+------+------+------+-------+-------+--------------------------+
| col            | seq  | key  | path | index | value | this                     |
+----------------+------+------+------+-------+-------+--------------------------+
| UNNEST_DEFAULT |    0 | b    | $.b  |  NULL | 2     | {"a": 1, "b": 2, "c": 3} |
+----------------+------+------+------+-------+-------+--------------------------+

> select *
> from unnest('{"a":1,"b":2,"c":3}',"$.b") as u;
Empty set (0.01 sec)

> select *
> from unnest('{"a":1,"b":2,"c":3}',"$.b",true) as u;
+----------------+------+------+------+-------+-------+--------------------------+
| col            | seq  | key  | path | index | value | this                     |
+----------------+------+------+------+-------+-------+--------------------------+
| UNNEST_DEFAULT |    0 | NULL | $.b  |  NULL | NULL  | 2                        |

> drop table if exists t1;
> 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 unnest(t1.a, "$.b") as u;
+------+------+------+--------+-------+------------------+------------------------------+
| col  | seq  | key  | path   | index | value            | this                         |
+------+------+------+--------+-------+------------------+------------------------------+
| a    |    0 | NULL | $.b[0] |     0 | {"c": 2, "d": 3} | [{"c": 2, "d": 3}, false, 4] |
| a    |    0 | NULL | $.b[1] |     1 | false            | [{"c": 2, "d": 3}, false, 4] |
| a    |    0 | NULL | $.b[2] |     2 | 4                | [{"c": 2, "d": 3}, false, 4] |
+------+------+------+--------+-------+------------------+------------------------------+

> select * from unnest(t1.a, "$.b[0]") as u;
+------+------+------+----------+-------+-------+------------------+
| col  | seq  | key  | path     | index | value | this             |
+------+------+------+----------+-------+-------+------------------+
| a    |    0 | c    | $.b[0].c |  NULL | 2     | {"c": 2, "d": 3} |
| a    |    0 | d    | $.b[0].d |  NULL | 3     | {"c": 2, "d": 3} |
+------+------+------+----------+-------+-------+------------------+

> select distinct(f.seq) from unnest(t1.a, "$") as f;
+-------+
| f.seq |
+-------+
|     0 |
|     1 |
+-------+

注意事项

• key,index和value全为null则代表当前行是outer为true时默认添加的空行

执行流程

1. 数据源是json列 ...unnest -> project(jsonCol) -> tableScan
2. 数据源是json字符串 ...unnest -> project(default) -> valueScan(parse jsonStr)

实现细节

数据源为json字符串

1. 构建plan时将存储在tree.Unnest中的参数序列化后存储到unnestNode.TableDef.TableFunctionParam中
2. 在unnestNode中添加valueScan节点
3. 给valueScan.TableDef.TableFunctionParam赋值为tree.Unnest中存储的json字符串转化的字节切片
4. 在编译阶段首先将valueScan的TableDef.TableFunctionParam存入scope.Datasource.Bat
5. 在scope中添加vm.Unnest指令，并通过unnestNode.TableDef.TableFunctionParam构建运行参数
6. 执行阶段通过bytejson包解析json字节切片，解析path字符串，通过json,path, outer,filter参数调用bytejson.Unnest函数,返回UnnestResult结果集
7. 通过makeBatch组装UnnestResult结果集为batch

数据源为json列

1. 构建plan时将存储在tree.Unnest中的参数序列化后存储到unnestNode.TableDef.TableFunctionParam中
2. 在unnestNode中添加tableScan节点,并根据tree.Unnest中的参数初始化tableScan的TableDef
3. 编译阶段在scope中添加vm.Unnest指令，并通过unnestNode.TableDef.TableFunctionParam构建运行参数
4. 执行阶段通过bytejson包解析由tableScan传递bytejson字节切片，解析path字符串，通过json,path, outer,filter参数调用bytejson.Unnest 函数,返回UnnestResult结果集
5. 通过makeBatch组装UnnestResult结果集为batch

filter参数是根据tree.Unnest中的Attrs字段构建的string切片，其目的是为了在bytejson.Unnest函数中过滤不需要的结果集