github.com/matrixorigin/matrixone@v0.7.0/pkg/sql/colexec/table_function/README.md (about) 1 # **UNNEST** 2 3 ## **函数说明** 4 5 `UNNEST`是一个表函数，出现在 SQL 的 from 子句中，用于将 json[object|array]类型的数据展开为多行,每行包含json中的一个元素. 6 7 ## **语法结构** 8 9 ``` 10 > UNNEST(src[, path[, outer]]) 11 ``` 12 13 ## **相关参数** 14 15 | 参数 | 说明 | 类型 | 16 |----------|------------------------------------------|--------------------------------------------------| 17 | src | 必要参数，待展开的数据源 | 类型可以是 json 列或 json 字符串 | 18 | path | 可选参数，指明待展开数据源的具体 json 路径。默认为"$",展开整个 json 数据 | [path 字符串](../../../container/bytejson/README.md) | 19 | outer | 可选参数，如果数据源展开后结果行为 0，是否加上一个空行作为标记。默认为 false | bool 类型 | 20 21 ## **返回表结构** 22 23 | 字段名 | 类型 | 说明 | 24 |-----|---------|---------------------------------------------------| 25 | col | varchar | 数据源的名称。如果数据源是列，则是列名；如果数据源是 json 字符串，则是"UNNEST_DEFAULT" | 26 | seq | int32 | 数据源中元素的索引，从 0 开始 | 27 |key |varchar | 展开元素的键名，如果父级元素是数组，则为 null | 28 |path | varchar | 展开元素的在数据源中的路径 | 29 |index | int32 | 展开元素在父级元素中的索引，如果数据源是对象，则为 null | 30 |value | varchar | 展开元素的值 | 31 |this | varchar | 展开元素的父级元素值 | 32 33 ## **示例** 34 35 ``` 36 > select * 37 > from unnest('{"a":1,"b":2,"c":3}') as u; 38 +----------------+------+------+------+-------+-------+--------------------------+ 39 | col | seq | key | path | index | value | this | 40 +----------------+------+------+------+-------+-------+--------------------------+ 41 | UNNEST_DEFAULT | 0 | a | $.a | NULL | 1 | {"a": 1, "b": 2, "c": 3} | 42 | UNNEST_DEFAULT | 0 | b | $.b | NULL | 2 | {"a": 1, "b": 2, "c": 3} | 43 | UNNEST_DEFAULT | 0 | c | $.c | NULL | 3 | {"a": 1, "b": 2, "c": 3} | 44 +----------------+------+------+------+-------+-------+--------------------------+ 45 46 > select * 47 > from unnest('{"a":1,"b":2,"c":3}') as u 48 > where u.`key` = 'b'; 49 +----------------+------+------+------+-------+-------+--------------------------+ 50 | col | seq | key | path | index | value | this | 51 +----------------+------+------+------+-------+-------+--------------------------+ 52 | UNNEST_DEFAULT | 0 | b | $.b | NULL | 2 | {"a": 1, "b": 2, "c": 3} | 53 +----------------+------+------+------+-------+-------+--------------------------+ 54 55 > select * 56 > from unnest('{"a":1,"b":2,"c":3}',"$.b") as u; 57 Empty set (0.01 sec) 58 59 > select * 60 > from unnest('{"a":1,"b":2,"c":3}',"$.b",true) as u; 61 +----------------+------+------+------+-------+-------+--------------------------+ 62 | col | seq | key | path | index | value | this | 63 +----------------+------+------+------+-------+-------+--------------------------+ 64 | UNNEST_DEFAULT | 0 | NULL | $.b | NULL | NULL | 2 | 65 66 > drop table if exists t1; 67 > create table t1 (a json,b int); 68 > insert into t1 69 > values ('{"a":1,"b":[{"c":2,"d":3},false,4],"e":{"f":true,"g":[null,true,1.1]}}',1); 70 > insert into t1 71 > values ('[1,true,false,null,"aaa",1.1,{"t":false}]',2); 72 > select * from unnest(t1.a, "$.b") as u; 73 +------+------+------+--------+-------+------------------+------------------------------+ 74 | col | seq | key | path | index | value | this | 75 +------+------+------+--------+-------+------------------+------------------------------+ 76 | a | 0 | NULL | $.b[0] | 0 | {"c": 2, "d": 3} | [{"c": 2, "d": 3}, false, 4] | 77 | a | 0 | NULL | $.b[1] | 1 | false | [{"c": 2, "d": 3}, false, 4] | 78 | a | 0 | NULL | $.b[2] | 2 | 4 | [{"c": 2, "d": 3}, false, 4] | 79 +------+------+------+--------+-------+------------------+------------------------------+ 80 81 > select * from unnest(t1.a, "$.b[0]") as u; 82 +------+------+------+----------+-------+-------+------------------+ 83 | col | seq | key | path | index | value | this | 84 +------+------+------+----------+-------+-------+------------------+ 85 | a | 0 | c | $.b[0].c | NULL | 2 | {"c": 2, "d": 3} | 86 | a | 0 | d | $.b[0].d | NULL | 3 | {"c": 2, "d": 3} | 87 +------+------+------+----------+-------+-------+------------------+ 88 89 > select distinct(f.seq) from unnest(t1.a, "$") as f; 90 +-------+ 91 | f.seq | 92 +-------+ 93 | 0 | 94 | 1 | 95 +-------+ 96 ``` 97 98 ## **注意事项** 99 100 * key,index 和 value 全为 null 则代表当前行是 outer 为 true 时默认添加的空行 101 102 ## **执行流程** 103 104 1. 数据源是 json 列 105 `...unnest -> project(jsonCol) -> tableScan` 106 2. 数据源是 json 字符串 107 `...unnest -> project(default) -> valueScan(parse jsonStr)` 108 109 ## **实现细节** 110 111 ### 数据源为 json 字符串 112 113 1. 构建 plan 时将存储在`tree.Unnest`中的参数序列化后存储到`unnestNode.TableDef.TableFunctionParam`中 114 2. 在`unnestNode`中添加`valueScan`节点 115 3. 给`valueScan.TableDef.TableFunctionParam`赋值为`tree.Unnest`中存储的 json 字符串转化的字节切片 116 4. 在编译阶段首先将`valueScan`的`TableDef.TableFunctionParam`存入`scope.Datasource.Bat` 117 5. 在 scope 中添加`vm.Unnest`指令，并通过`unnestNode.TableDef.TableFunctionParam`构建运行参数 118 6. 执行阶段通过`bytejson`包解析 json 字节切片，解析 path 字符串，通过 json,path, outer,*filter*参数调用`bytejson.Unnest`函数，返回`UnnestResult`结果集 119 7. 通过`makeBatch`组装`UnnestResult`结果集为`batch` 120 121 ### 数据源为 json 列 122 123 1. 构建 plan 时将存储在`tree.Unnest`中的参数序列化后存储到`unnestNode.TableDef.TableFunctionParam`中 124 2. 在`unnestNode`中添加`tableScan`节点，并根据`tree.Unnest`中的参数初始化`tableScan`的`TableDef` 125 3. 编译阶段在 scope 中添加`vm.Unnest`指令，并通过`unnestNode.TableDef.TableFunctionParam`构建运行参数 126 4. 执行阶段通过`bytejson`包解析由`tableScan`传递 bytejson 字节切片，解析 path 字符串，通过 json,path, outer,*filter*参数调用`bytejson.Unnest` 127 函数，返回`UnnestResult`结果集 128 5. 通过`makeBatch`组装`UnnestResult`结果集为`batch` 129 130 *filter*参数是根据`tree.Unnest`中的`Attrs`字段构建的 string 切片，其目的是为了在`bytejson.Unnest`函数中过滤不需要的结果集 131