github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/function/table_func_unnest.result (about) 1 select * from unnest('{"a":1}') as f; 2 col seq key path index value this 3 UNNEST_DEFAULT 0 a $.a null 1 {"a": 1} 4 select * from unnest('{"a":1}', '$.a') as f; 5 col seq key path index value this 6 select * from unnest('{"a":1}', '$.a', true) as f; 7 col seq key path index value this 8 UNNEST_DEFAULT 0 null $.a null null 1 9 select * from unnest('{"a":1}'); 10 SQL parser error: No tables used 11 select * from unnest('{"a":1}', '$.a'); 12 SQL parser error: No tables used 13 select * from unnest('{"a":1}', '$.a', true); 14 SQL parser error: No tables used 15 drop table if exists t1; 16 create table t1 (a json,b int); 17 insert into t1 values ('{"a":1,"b":[{"c":2,"d":3},false,4],"e":{"f":true,"g":[null,true,1.1]}}',1); 18 insert into t1 values ('[1,true,false,null,"aaa",1.1,{"t":false}]',2); 19 select * from t1; 20 a b 21 {"a": 1, "b": [{"c": 2, "d": 3}, false, 4], "e": {"f": true, "g": [null, true, 1.1]}} 1 22 [1, true, false, null, "aaa", 1.1, {"t": false}] 2 23 select * from unnest(t1.a) as f; 24 invalid input: missing FROM-clause entry for table 't1' 25 select f.* from t1,unnest(t1.a) as f; 26 col seq key path index value this 27 t1.a 0 a $.a null 1 {"a": 1, "b": [{"c": 2, "d": 3}, false, 4], "e": {"f": true, "g": [null, true, 1.1]}} 28 t1.a 1 b $.b null [{"c": 2, "d": 3}, false, 4] {"a": 1, "b": [{"c": 2, "d": 3}, false, 4], "e": {"f": true, "g": [null, true, 1.1]}} 29 t1.a 2 e $.e null {"f": true, "g": [null, true, 1.1]} {"a": 1, "b": [{"c": 2, "d": 3}, false, 4], "e": {"f": true, "g": [null, true, 1.1]}} 30 t1.a 0 null $[0] 0 1 [1, true, false, null, "aaa", 1.1, {"t": false}] 31 t1.a 1 null $[1] 1 true [1, true, false, null, "aaa", 1.1, {"t": false}] 32 t1.a 2 null $[2] 2 false [1, true, false, null, "aaa", 1.1, {"t": false}] 33 t1.a 3 null $[3] 3 null [1, true, false, null, "aaa", 1.1, {"t": false}] 34 t1.a 4 null $[4] 4 "aaa" [1, true, false, null, "aaa", 1.1, {"t": false}] 35 t1.a 5 null $[5] 5 1.1 [1, true, false, null, "aaa", 1.1, {"t": false}] 36 t1.a 6 null $[6] 6 {"t": false} [1, true, false, null, "aaa", 1.1, {"t": false}] 37 t1.a 0 a $.a null 1 {"a": 1, "b": [{"c": 2, "d": 3}, false, 4], "e": {"f": true, "g": [null, true, 1.1]}} 38 t1.a 1 b $.b null [{"c": 2, "d": 3}, false, 4] {"a": 1, "b": [{"c": 2, "d": 3}, false, 4], "e": {"f": true, "g": [null, true, 1.1]}} 39 t1.a 2 e $.e null {"f": true, "g": [null, true, 1.1]} {"a": 1, "b": [{"c": 2, "d": 3}, false, 4], "e": {"f": true, "g": [null, true, 1.1]}} 40 t1.a 0 null $[0] 0 1 [1, true, false, null, "aaa", 1.1, {"t": false}] 41 t1.a 1 null $[1] 1 true [1, true, false, null, "aaa", 1.1, {"t": false}] 42 t1.a 2 null $[2] 2 false [1, true, false, null, "aaa", 1.1, {"t": false}] 43 t1.a 3 null $[3] 3 null [1, true, false, null, "aaa", 1.1, {"t": false}] 44 t1.a 4 null $[4] 4 "aaa" [1, true, false, null, "aaa", 1.1, {"t": false}] 45 t1.a 5 null $[5] 5 1.1 [1, true, false, null, "aaa", 1.1, {"t": false}] 46 t1.a 6 null $[6] 6 {"t": false} [1, true, false, null, "aaa", 1.1, {"t": false}] 47 select f.* from t1,unnest(t1.a, "$.b") as f; 48 col seq key path index value this 49 t1.a 0 null $.b[0] 0 {"c": 2, "d": 3} [{"c": 2, "d": 3}, false, 4] 50 t1.a 1 null $.b[1] 1 false [{"c": 2, "d": 3}, false, 4] 51 t1.a 2 null $.b[2] 2 4 [{"c": 2, "d": 3}, false, 4] 52 t1.a 0 null $.b[0] 0 {"c": 2, "d": 3} [{"c": 2, "d": 3}, false, 4] 53 t1.a 1 null $.b[1] 1 false [{"c": 2, "d": 3}, false, 4] 54 t1.a 2 null $.b[2] 2 4 [{"c": 2, "d": 3}, false, 4] 55 select f.* from t1,unnest(t1.a, "$.a") as f; 56 col seq key path index value this 57 select f.* from t1,unnest(t1.a, "$.a", true) as f; 58 col seq key path index value this 59 t1.a 0 null $.a null null 1 60 t1.a 0 null $.a null null 1 61 select * from t1,unnest(t1.a); 62 SQL syntax error: Every table function must have an alias 63 select f.* from t1,unnest(t1.a, "$.b"); 64 SQL syntax error: Every table function must have an alias 65 select * from t1,unnest(t1.a, "$.a", true) as f; 66 a b col seq key path index value this 67 {"a": 1, "b": [{"c": 2, "d": 3}, false, 4], "e": {"f": true, "g": [null, true, 1.1]}} 1 t1.a 0 null $.a null null 1 68 [1, true, false, null, "aaa", 1.1, {"t": false}] 2 t1.a 0 null $.a null null 1 69 select col from t1,unnest(t1.a, "$.b") as f; 70 col 71 t1.a 72 t1.a 73 t1.a 74 t1.a 75 t1.a 76 t1.a 77 select f.col from t1,unnest(t1.a, "$.b") as f; 78 col 79 t1.a 80 t1.a 81 t1.a 82 t1.a 83 t1.a 84 t1.a 85 select f.seq, f.col from t1,unnest(t1.a, "$.b") as f; 86 seq col 87 0 t1.a 88 1 t1.a 89 2 t1.a 90 0 t1.a 91 1 t1.a 92 2 t1.a 93 select distinct(f.seq) from t1,unnest(t1.a, "$") as f; 94 f.seq 95 0 96 1 97 2 98 3 99 4 100 5 101 6 102 select `index`,path from t1,unnest(t1.a, "$") u; 103 index path 104 null $.a 105 null $.b 106 null $.e 107 0 $[0] 108 1 $[1] 109 2 $[2] 110 3 $[3] 111 4 $[4] 112 5 $[5] 113 6 $[6] 114 null $.a 115 null $.b 116 null $.e 117 0 $[0] 118 1 $[1] 119 2 $[2] 120 3 $[3] 121 4 $[4] 122 5 $[5] 123 6 $[6] 124 select `index`,path from t1,unnest(t1.a, "$") u where u.`index` = 1; 125 index path 126 1 $[1] 127 1 $[1] 128 select `index`,path from t1,unnest(t1.a, "$") u where u.`index` is null or u.path='$.b'; 129 index path 130 null $.a 131 null $.b 132 null $.e 133 null $.a 134 null $.b 135 null $.e 136 select `index`,path from t1,unnest(t1.a, "$") u where u.`index` is null and u.path='$.b'; 137 index path 138 null $.b 139 null $.b 140 select * from t1,unnest(t1.a, "$") u where u.`index` = 1; 141 a b col seq key path index value this 142 {"a": 1, "b": [{"c": 2, "d": 3}, false, 4], "e": {"f": true, "g": [null, true, 1.1]}} 1 t1.a 1 null $[1] 1 true [1, true, false, null, "aaa", 1.1, {"t": false}] 143 [1, true, false, null, "aaa", 1.1, {"t": false}] 2 t1.a 1 null $[1] 1 true [1, true, false, null, "aaa", 1.1, {"t": false}] 144 select `key`,path,value from t1, unnest(t1.a, "$") u where u.`index` is null or u.path='$.b'; 145 key path value 146 a $.a 1 147 b $.b [{"c": 2, "d": 3}, false, 4] 148 e $.e {"f": true, "g": [null, true, 1.1]} 149 a $.a 1 150 b $.b [{"c": 2, "d": 3}, false, 4] 151 e $.e {"f": true, "g": [null, true, 1.1]} 152 select seq,col,b from t1, unnest(t1.a, "$") u where u.`index` is null; 153 seq col b 154 0 t1.a 1 155 1 t1.a 1 156 2 t1.a 1 157 0 t1.a 2 158 1 t1.a 2 159 2 t1.a 2 160 select count(b),b from t1, unnest(t1.a, "$") u where u.`index` is null group by b; 161 count(b) b 162 3 1 163 3 2 164 insert into t1 values ('{"add":1}',1); 165 select count(seq),seq from t1, unnest(t1.a, "$") u where u.`index` is null group by seq; 166 count(seq) seq 167 6 0 168 3 1 169 3 2 170 select count(seq),seq from t1, unnest(t1.a, "$") u where u.`index` is null group by seq order by seq desc; 171 count(seq) seq 172 3 2 173 3 1 174 6 0 175 select count(`index`),`index`,sum(`index`) from t1, unnest(t1.a, "$") u where u.`index` is not null group by `index` order by sum(`index`) desc; 176 count(index) index sum(index) 177 3 6 18 178 3 5 15 179 3 4 12 180 3 3 9 181 3 2 6 182 3 1 3 183 3 0 0 184 with u_table as (select * from t1,unnest(t1.a,"$.b",true) u where seq>1) select a,`index` from u_table where b=1; 185 a index 186 {"a": 1, "b": [{"c": 2, "d": 3}, false, 4], "e": {"f": true, "g": [null, true, 1.1]}} 2 187 {"add": 1} 2 188 with myt as (select a from t1 where t1.b>1) select * from myt,unnest(myt.a) u; 189 a col seq key path index value this 190 [1, true, false, null, "aaa", 1.1, {"t": false}] myt.a 0 null $[0] 0 1 [1, true, false, null, "aaa", 1.1, {"t": false}] 191 [1, true, false, null, "aaa", 1.1, {"t": false}] myt.a 1 null $[1] 1 true [1, true, false, null, "aaa", 1.1, {"t": false}] 192 [1, true, false, null, "aaa", 1.1, {"t": false}] myt.a 2 null $[2] 2 false [1, true, false, null, "aaa", 1.1, {"t": false}] 193 [1, true, false, null, "aaa", 1.1, {"t": false}] myt.a 3 null $[3] 3 null [1, true, false, null, "aaa", 1.1, {"t": false}] 194 [1, true, false, null, "aaa", 1.1, {"t": false}] myt.a 4 null $[4] 4 "aaa" [1, true, false, null, "aaa", 1.1, {"t": false}] 195 [1, true, false, null, "aaa", 1.1, {"t": false}] myt.a 5 null $[5] 5 1.1 [1, true, false, null, "aaa", 1.1, {"t": false}] 196 [1, true, false, null, "aaa", 1.1, {"t": false}] myt.a 6 null $[6] 6 {"t": false} [1, true, false, null, "aaa", 1.1, {"t": false}] 197 with myt as (select a from t1 where t1.b>1) select * from myt,unnest(myt.a) u where json_unquote(value)='false' or `index`=5; 198 a col seq key path index value this 199 [1, true, false, null, "aaa", 1.1, {"t": false}] myt.a 2 null $[2] 2 false [1, true, false, null, "aaa", 1.1, {"t": false}] 200 [1, true, false, null, "aaa", 1.1, {"t": false}] myt.a 5 null $[5] 5 1.1 [1, true, false, null, "aaa", 1.1, {"t": false}] 201 create view v1 as (select * from t1,unnest(t1.a) u); 202 select count(seq),seq from v1 where `index` is null or json_unquote(value)='false' group by seq; 203 count(seq) seq 204 6 0 205 3 1 206 6 2 207 select `key`,path,value from unnest(substring('{"a":1}...',1,7), '$') u; 208 key path value 209 a $.a 1 210 drop table if exists t2; 211 create table t2 (a varchar(100)); 212 insert into t2 values ('{"a":1}.'); 213 with tmpt as (select substring(a,1,7) as a from t2) select * from tmpt, unnest(tmpt.a, '$') u; 214 a col seq key path index value this 215 {"a":1} tmpt.a 0 a $.a null 1 {"a": 1} 216 select * from t2,unnest(substring(t2.a,1,7)) u; 217 a col seq key path index value this 218 {"a":1}. substring(t2.a, 1, 7) 0 a $.a null 1 {"a": 1} 219 create view v3 as (select * from unnest(substring('{"a":1}...',1,7), '$') u); 220 desc v3; 221 Field Type Null Key Default Extra Comment 222 col VARCHAR(65535) YES null 223 index INT(4) YES null 224 key VARCHAR(65535) YES null 225 path VARCHAR(65535) YES null 226 seq INT(4) YES null 227 this JSON(0) YES null 228 value JSON(0) YES null 229 drop table if exists t1; 230 create table t1(a int,b json); 231 insert into t1 values(1,'{"people":[{"name":"a1","address":"a2"},{"name":"b1","address":"b2"}]}'); 232 insert into t1 values(2,'{"people":[{"name":"c1","address":"c2"},{"name":"d1","address":"d2"}]}'); 233 select a,e.* from t1,unnest(t1.b,'$.people[0]') e where seq=0 and e.this=json_extract(t1.b,'$.people[0]'); 234 a col seq key path index value this 235 1 t1.b 0 address $.people[0].address null "a2" {"address": "a2", "name": "a1"} 236 2 t1.b 0 address $.people[0].address null "c2" {"address": "c2", "name": "c1"}