github.com/matrixorigin/matrixone@v0.7.0/test/distributed/cases/function/func_json_extract.test (about)

     1  #Test cases of json_extract from the command line
     2  select json_extract('{"a":1,"b":2,"c":3}','$.a');
     3  select json_extract('{"a":1,"b":2,"c":3}','$.b');
     4  select json_extract('{"a":{"q":[1,2,3]}}','$.a.q[1]');
     5  select json_extract('[{"a":1,"b":2,"c":3},{"a":4,"b":5,"c":6}]','$[1].a');
     6  select json_extract('{"a":{"q":[{"a":1},{"a":2},{"a":3}]}}','$.a.q[1]');
     7  select json_extract('{"a":{"q":[{"a":1},{"a":2},{"a":3}]}}','$.a.q');
     8  select json_extract('[1,2,3]','$[*]');
     9  select json_extract('{"a":[1,2,3,{"b":4}]}','$.a[3].b');
    10  select json_extract('{"a":[1,2,3,{"b":4}]}','$.a[3].c');
    11  select json_extract('{"a":[1,2,3,{"b":4}],"c":5}','$.*');
    12  select json_extract('{"a":[1,2,3,{"a":4}]}','$**.a');
    13  select json_extract('{"a":[1,2,3,{"a":4}]}','$.a[*].a');
    14  select json_extract('{"a":1}','$[0]');
    15  select json_extract('{"a":1}','$[0].a');
    16  select json_extract('{"a":1}','$[0].b');
    17  select json_extract('{"a":1}','$[1]');
    18  select json_extract('{"af": [1, "2", {"aaf": "bb"}],"eab":"888"}','$**.f');
    19  select json_extract('{"a": [1, "2", {"a": "bb"}]}','$**.a');
    20  select  json_extract('{"a":"a1","b":"b1"}','$.**');
    21  select  json_extract('{"a":"a1","b":"b1"}','$**.1');
    22  
    23  # Test cases of json_extract from column
    24  drop table if exists t1;
    25  create table t1 (a json,b int);
    26  insert into t1(a,b) values ('{"a":1,"b":2,"c":3}',1);
    27  select json_extract(t1.a,'$.a') from t1 where t1.b=1;
    28  insert into t1(a,b) values ('{"a":4,"b":5,"c":6}',2);
    29  select json_extract(t1.a,'$.b') from t1 where t1.b=2;
    30  select json_extract(t1.a,'$.a') from t1;
    31  insert into t1(a,b) values ('{"a":{"q":[1,2,3]}}',3);
    32  select json_extract(t1.a,'$.a.q[1]') from t1 where t1.b=3;
    33  insert into t1(a,b) values ('[{"a":1,"b":2,"c":3},{"a":4,"b":5,"c":6}]',4);
    34  select json_extract(t1.a,'$[1].a') from t1 where t1.b=4;
    35  insert into t1(a,b) values ('{"a":{"q":[{"a":1},{"a":2},{"a":3}]}}',5);
    36  select json_extract(t1.a,'$.a.q[1]') from t1 where t1.b=5;
    37  select json_extract(t1.a,'$.a.q') from t1 where t1.b=5;
    38  insert into t1(a,b) values ('[1,2,3]',6);
    39  select json_extract(t1.a,'$[*]') from t1 where t1.b=6;
    40  insert into t1(a,b) values ('{"a":[1,2,3,{"b":4}]}',7);
    41  select json_extract(t1.a,'$.a[3].b') from t1 where t1.b=7;
    42  select json_extract(t1.a,'$.a[3].c') from t1 where t1.b=7;
    43  insert into t1(a,b) values ('{"a":[1,2,3,{"b":4}],"c":5}',8);
    44  select json_extract(t1.a,'$.*') from t1 where t1.b=8;
    45  insert into t1(a,b) values ('{"a":[1,2,3,{"a":4}]}',9);
    46  select json_extract(t1.a,'$**.a') from t1 where t1.b=9;
    47  select json_extract(t1.a,'$.a[*].a') from t1 where t1.b=9;
    48  drop table t1;
    49  create table t1 (a json);
    50  insert into t1(a) values ('{"a":1}'),('[1,2]'),('{"xa":1}');
    51  drop table if exists t2;
    52  create table t2 (a varchar(100));
    53  insert into t2 values ('$[0]'),('$.a');
    54  select json_extract(t1.a,t2.a) qqq,t1.a,t2.a from t2, t1;
    55  drop table if exists json_table_1;
    56  create table json_table_1 (j1 json);
    57  insert into json_table_1 values('{"key10": "value1", "key2": "value2"}'),('{"key1": "@#$_%^&*()!@", "123456": "中文mo"}'),('{"芝士面包": "12abc", "123456": "中文mo"}'),('{"": "", "123456": "中文mo"}'),('{"a 1": "b 1", "123456": "中文mo"}'),('{"d1": "2020-10-09", "d2": "2019-08-20 12:30:00"}'),('{"d1": [true,false]}'),('{}');
    58  select json_extract('{"a":"a1","b":"b1"}','$.*') from json_table_1;
    59  create view v1 as select json_extract('{"a":1}','$.a');
    60  desc v1;
    61  select json_extract('{"a":1}',null);
    62  select json_extract(null,'$');
    63  select json_extract(null,null);
    64  select json_extract('{"a":1}',null) from json_table_1;
    65  select json_extract(null,'$') from json_table_1;
    66  select json_extract('[1,2,3]','$[last]');
    67  select json_extract('[1,2,3]','$[last-1]');
    68  select json_extract('[1,2,3]','$[last-2]');
    69  select json_extract('[1,2,3]','$[last-3]');
    70  select json_extract('[1,2,3]','$[0 to 2]');
    71  select json_extract('[1,2,3]','$[0 to last]');
    72  select json_extract('[1,2,3]','$[0 to last-1]');
    73  select json_extract('[1,2,3]','$[last-2 to last]');
    74  select json_extract('[1,2,3]','$[last-1 to last-2]');
    75  select json_extract('[1,2,3]','$[last-8 to last-2]');
    76  select json_extract('[1,2,3]','$[last-2 to last-8]');
    77  select json_extract('[1,2,3]','$[0 to last-8]');
    78  
    79  # Test multiple paths
    80  select json_extract('{"a":1,"b":2,"c":3}','$.a','$.b');
    81  select json_extract('{"a":1,"b":2,"c":3}','$.a','$.b','$.c');
    82  select json_extract('{"a":1,"b":2,"c":3}','$.c','$.d');
    83  select json_extract('[0,1,2]', '$[0]', '$[1]');
    84  select json_extract('[0,1,2]', '$[1]', '$[0]');
    85  select json_extract('[0,1,2]', '$[last-1]', '$[0]', '$[2]');