github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/function/table_func_unnest.test (about)

     1  #Test for str case
     2  select * from unnest('{"a":1}') as f;
     3  select * from unnest('{"a":1}', '$.a') as f;
     4  select * from unnest('{"a":1}', '$.a', true) as f;
     5  select * from unnest('{"a":1}');
     6  select * from unnest('{"a":1}', '$.a');
     7  select * from unnest('{"a":1}', '$.a', true);
     8  
     9  #Test for col case
    10  drop table if exists t1;
    11  create table t1 (a json,b int);
    12  insert into t1 values ('{"a":1,"b":[{"c":2,"d":3},false,4],"e":{"f":true,"g":[null,true,1.1]}}',1);
    13  insert into t1 values ('[1,true,false,null,"aaa",1.1,{"t":false}]',2);
    14  select * from t1;
    15  select * from unnest(t1.a) as f;
    16  select f.* from t1,unnest(t1.a) as f;
    17  select f.* from t1,unnest(t1.a, "$.b") as f;
    18  select f.* from t1,unnest(t1.a, "$.a") as f;
    19  select f.* from t1,unnest(t1.a, "$.a", true) as f;
    20  select * from t1,unnest(t1.a);
    21  select f.* from t1,unnest(t1.a, "$.b");
    22  select * from t1,unnest(t1.a, "$.a", true) as f;
    23  select col from t1,unnest(t1.a, "$.b") as f;
    24  select f.col from t1,unnest(t1.a, "$.b") as f;
    25  select f.seq, f.col from t1,unnest(t1.a, "$.b") as f;
    26  select distinct(f.seq) from t1,unnest(t1.a, "$") as f;
    27  select `index`,path from t1,unnest(t1.a, "$") u;
    28  select `index`,path from t1,unnest(t1.a, "$") u where u.`index` = 1;
    29  select `index`,path from t1,unnest(t1.a, "$") u where u.`index` is null or u.path='$.b';
    30  select `index`,path from t1,unnest(t1.a, "$") u where u.`index` is null and u.path='$.b';
    31  select * from t1,unnest(t1.a, "$") u where u.`index` = 1;
    32  select `key`,path,value from t1, unnest(t1.a, "$") u where u.`index` is null or u.path='$.b';
    33  select seq,col,b from t1, unnest(t1.a, "$") u where u.`index` is null;
    34  select count(b),b from t1, unnest(t1.a, "$") u where u.`index` is null group by b;
    35  insert into t1 values ('{"add":1}',1);
    36  select count(seq),seq from t1, unnest(t1.a, "$") u where u.`index` is null group by seq;
    37  select count(seq),seq from t1, unnest(t1.a, "$") u where u.`index` is null group by seq order by seq desc;
    38  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;
    39  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;
    40  with myt as (select a from t1 where t1.b>1) select * from myt,unnest(myt.a) u;
    41  
    42  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;
    43  create view v1 as (select * from t1,unnest(t1.a) u);
    44  select count(seq),seq from v1 where `index` is null or json_unquote(value)='false' group by seq;
    45  
    46  select `key`,path,value from unnest(substring('{"a":1}...',1,7), '$') u;
    47  drop table if exists t2;
    48  create table t2 (a varchar(100));
    49  insert into t2 values ('{"a":1}.');
    50  with tmpt as (select substring(a,1,7) as a from t2) select * from tmpt, unnest(tmpt.a, '$') u;
    51  select * from t2,unnest(substring(t2.a,1,7)) u;
    52  create view v3 as (select * from unnest(substring('{"a":1}...',1,7), '$') u);
    53  desc v3;
    54  drop table if exists t1;
    55  create table t1(a int,b json);
    56  insert into t1 values(1,'{"people":[{"name":"a1","address":"a2"},{"name":"b1","address":"b2"}]}');
    57  insert into t1 values(2,'{"people":[{"name":"c1","address":"c2"},{"name":"d1","address":"d2"}]}');
    58  select a,e.* from t1,unnest(t1.b,'$.people[0]') e where seq=0 and e.this=json_extract(t1.b,'$.people[0]');