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

     1  select json_extract('{"a":1,"b":2,"c":3}','$.a');
     2  json_extract({"a":1,"b":2,"c":3}, $.a)
     3  1
     4  select json_extract('{"a":1,"b":2,"c":3}','$.b');
     5  json_extract({"a":1,"b":2,"c":3}, $.b)
     6  2
     7  select json_extract('{"a":{"q":[1,2,3]}}','$.a.q[1]');
     8  json_extract({"a":{"q":[1,2,3]}}, $.a.q[1])
     9  2
    10  select json_extract('[{"a":1,"b":2,"c":3},{"a":4,"b":5,"c":6}]','$[1].a');
    11  json_extract([{"a":1,"b":2,"c":3},{"a":4,"b":5,"c":6}], $[1].a)
    12  4
    13  select json_extract('{"a":{"q":[{"a":1},{"a":2},{"a":3}]}}','$.a.q[1]');
    14  json_extract({"a":{"q":[{"a":1},{"a":2},{"a":3}]}}, $.a.q[1])
    15  {"a": 2}
    16  select json_extract('{"a":{"q":[{"a":1},{"a":2},{"a":3}]}}','$.a.q');
    17  json_extract({"a":{"q":[{"a":1},{"a":2},{"a":3}]}}, $.a.q)
    18  [{"a": 1}, {"a": 2}, {"a": 3}]
    19  select json_extract('[1,2,3]','$[*]');
    20  json_extract([1,2,3], $[*])
    21  [1, 2, 3]
    22  select json_extract('{"a":[1,2,3,{"b":4}]}','$.a[3].b');
    23  json_extract({"a":[1,2,3,{"b":4}]}, $.a[3].b)
    24  4
    25  select json_extract('{"a":[1,2,3,{"b":4}]}','$.a[3].c');
    26  json_extract({"a":[1,2,3,{"b":4}]}, $.a[3].c)
    27  null
    28  select json_extract('{"a":[1,2,3,{"b":4}],"c":5}','$.*');
    29  json_extract({"a":[1,2,3,{"b":4}],"c":5}, $.*)
    30  [[1, 2, 3, {"b": 4}], 5]
    31  select json_extract('{"a":[1,2,3,{"a":4}]}','$**.a');
    32  json_extract({"a":[1,2,3,{"a":4}]}, $**.a)
    33  [[1, 2, 3, {"a": 4}], 4]
    34  select json_extract('{"a":[1,2,3,{"a":4}]}','$.a[*].a');
    35  json_extract({"a":[1,2,3,{"a":4}]}, $.a[*].a)
    36  4
    37  select json_extract('{"a":1}','$[0]');
    38  json_extract({"a":1}, $[0])
    39  {"a": 1}
    40  select json_extract('{"a":1}','$[0].a');
    41  json_extract({"a":1}, $[0].a)
    42  1
    43  select json_extract('{"a":1}','$[0].b');
    44  json_extract({"a":1}, $[0].b)
    45  null
    46  select json_extract('{"a":1}','$[1]');
    47  json_extract({"a":1}, $[1])
    48  null
    49  select json_extract('{"af": [1, "2", {"aaf": "bb"}],"eab":"888"}','$**.f');
    50  json_extract({"af": [1, "2", {"aaf": "bb"}],"eab":"888"}, $**.f)
    51  null
    52  select json_extract('{"a": [1, "2", {"a": "bb"}]}','$**.a');
    53  json_extract({"a": [1, "2", {"a": "bb"}]}, $**.a)
    54  [[1, "2", {"a": "bb"}], "bb"]
    55  select  json_extract('{"a":"a1","b":"b1"}','$.**');
    56  invalid input: invalid json path '$.**'
    57  select  json_extract('{"a":"a1","b":"b1"}','$**.1');
    58  invalid input: invalid json path '$**.1'
    59  drop table if exists t1;
    60  create table t1 (a json,b int);
    61  insert into t1(a,b) values ('{"a":1,"b":2,"c":3}',1);
    62  select json_extract(t1.a,'$.a') from t1 where t1.b=1;
    63  json_extract(t1.a, $.a)
    64  1
    65  insert into t1(a,b) values ('{"a":4,"b":5,"c":6}',2);
    66  select json_extract(t1.a,'$.b') from t1 where t1.b=2;
    67  json_extract(t1.a, $.b)
    68  5
    69  select json_extract(t1.a,'$.a') from t1;
    70  json_extract(t1.a, $.a)
    71  1
    72  4
    73  insert into t1(a,b) values ('{"a":{"q":[1,2,3]}}',3);
    74  select json_extract(t1.a,'$.a.q[1]') from t1 where t1.b=3;
    75  json_extract(t1.a, $.a.q[1])
    76  2
    77  insert into t1(a,b) values ('[{"a":1,"b":2,"c":3},{"a":4,"b":5,"c":6}]',4);
    78  select json_extract(t1.a,'$[1].a') from t1 where t1.b=4;
    79  json_extract(t1.a, $[1].a)
    80  4
    81  insert into t1(a,b) values ('{"a":{"q":[{"a":1},{"a":2},{"a":3}]}}',5);
    82  select json_extract(t1.a,'$.a.q[1]') from t1 where t1.b=5;
    83  json_extract(t1.a, $.a.q[1])
    84  {"a": 2}
    85  select json_extract(t1.a,'$.a.q') from t1 where t1.b=5;
    86  json_extract(t1.a, $.a.q)
    87  [{"a": 1}, {"a": 2}, {"a": 3}]
    88  insert into t1(a,b) values ('[1,2,3]',6);
    89  select json_extract(t1.a,'$[*]') from t1 where t1.b=6;
    90  json_extract(t1.a, $[*])
    91  [1, 2, 3]
    92  insert into t1(a,b) values ('{"a":[1,2,3,{"b":4}]}',7);
    93  select json_extract(t1.a,'$.a[3].b') from t1 where t1.b=7;
    94  json_extract(t1.a, $.a[3].b)
    95  4
    96  select json_extract(t1.a,'$.a[3].c') from t1 where t1.b=7;
    97  json_extract(t1.a, $.a[3].c)
    98  null
    99  insert into t1(a,b) values ('{"a":[1,2,3,{"b":4}],"c":5}',8);
   100  select json_extract(t1.a,'$.*') from t1 where t1.b=8;
   101  json_extract(t1.a, $.*)
   102  [[1, 2, 3, {"b": 4}], 5]
   103  insert into t1(a,b) values ('{"a":[1,2,3,{"a":4}]}',9);
   104  select json_extract(t1.a,'$**.a') from t1 where t1.b=9;
   105  json_extract(t1.a, $**.a)
   106  [[1, 2, 3, {"a": 4}], 4]
   107  select json_extract(t1.a,'$.a[*].a') from t1 where t1.b=9;
   108  json_extract(t1.a, $.a[*].a)
   109  4
   110  drop table t1;
   111  create table t1 (a json);
   112  insert into t1(a) values ('{"a":1}'),('[1,2]'),('{"xa":1}');
   113  drop table if exists t2;
   114  create table t2 (a varchar(100));
   115  insert into t2 values ('$[0]'),('$.a');
   116  select json_extract(t1.a,t2.a) qqq,t1.a,t2.a from t2, t1;
   117  qqq    a    a
   118  {"a": 1}    {"a": 1}    $[0]
   119  1    {"a": 1}    $.a
   120  1    [1, 2]    $[0]
   121  null    [1, 2]    $.a
   122  {"xa": 1}    {"xa": 1}    $[0]
   123  null    {"xa": 1}    $.a
   124  drop table if exists json_table_1;
   125  create table json_table_1 (j1 json);
   126  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]}'),('{}');
   127  select json_extract('{"a":"a1","b":"b1"}','$.*') from json_table_1;
   128  json_extract({"a":"a1","b":"b1"}, $.*)
   129  ["a1", "b1"]
   130  ["a1", "b1"]
   131  ["a1", "b1"]
   132  ["a1", "b1"]
   133  ["a1", "b1"]
   134  ["a1", "b1"]
   135  ["a1", "b1"]
   136  ["a1", "b1"]
   137  create view v1 as select json_extract('{"a":1}','$.a');
   138  desc v1;
   139  Field    Type    Null    Key    Default    Extra    Comment
   140  json_extract({"a":1}, $.a)    JSON(0)    NO        null
   141  select json_extract('{"a":1}',null);
   142  json_extract({"a":1}, null)
   143  null
   144  select json_extract(null,'$');
   145  json_extract(null, $)
   146  null
   147  select json_extract(null,null);
   148  json_extract(null, null)
   149  null
   150  select json_extract('{"a":1}',null) from json_table_1;
   151  json_extract({"a":1}, null)
   152  null
   153  null
   154  null
   155  null
   156  null
   157  null
   158  null
   159  null
   160  select json_extract(null,'$') from json_table_1;
   161  json_extract(null, $)
   162  null
   163  null
   164  null
   165  null
   166  null
   167  null
   168  null
   169  null
   170  select json_extract('[1,2,3]','$[last]');
   171  json_extract([1,2,3], $[last])
   172  3
   173  select json_extract('[1,2,3]','$[last-1]');
   174  json_extract([1,2,3], $[last-1])
   175  2
   176  select json_extract('[1,2,3]','$[last-2]');
   177  json_extract([1,2,3], $[last-2])
   178  1
   179  select json_extract('[1,2,3]','$[last-3]');
   180  json_extract([1,2,3], $[last-3])
   181  null
   182  select json_extract('[1,2,3]','$[0 to 2]');
   183  json_extract([1,2,3], $[0 to 2])
   184  [1, 2, 3]
   185  select json_extract('[1,2,3]','$[0 to last]');
   186  json_extract([1,2,3], $[0 to last])
   187  [1, 2, 3]
   188  select json_extract('[1,2,3]','$[0 to last-1]');
   189  json_extract([1,2,3], $[0 to last-1])
   190  [1, 2]
   191  select json_extract('[1,2,3]','$[last-2 to last]');
   192  json_extract([1,2,3], $[last-2 to last])
   193  [1, 2, 3]
   194  select json_extract('[1,2,3]','$[last-1 to last-2]');
   195  invalid input: invalid json path '$[last-1 to last-2]'
   196  select json_extract('[1,2,3]','$[last-8 to last-2]');
   197  json_extract([1,2,3], $[last-8 to last-2])
   198  1
   199  select json_extract('[1,2,3]','$[last-2 to last-8]');
   200  invalid input: invalid json path '$[last-2 to last-8]'
   201  select json_extract('[1,2,3]','$[0 to last-8]');
   202  json_extract([1,2,3], $[0 to last-8])
   203  null
   204  select json_extract('{"a":1,"b":2,"c":3}','$.a','$.b');
   205  json_extract({"a":1,"b":2,"c":3}, $.a, $.b)
   206  [1, 2]
   207  select json_extract('{"a":1,"b":2,"c":3}','$.a','$.b','$.c');
   208  json_extract({"a":1,"b":2,"c":3}, $.a, $.b, $.c)
   209  [1, 2, 3]
   210  select json_extract('{"a":1,"b":2,"c":3}','$.c','$.d');
   211  json_extract({"a":1,"b":2,"c":3}, $.c, $.d)
   212  [3]
   213  select json_extract('[0,1,2]', '$[0]', '$[1]');
   214  json_extract([0,1,2], $[0], $[1])
   215  [0, 1]
   216  select json_extract('[0,1,2]', '$[1]', '$[0]');
   217  json_extract([0,1,2], $[1], $[0])
   218  [1, 0]
   219  select json_extract('[0,1,2]', '$[last-1]', '$[0]', '$[2]');
   220  json_extract([0,1,2], $[last-1], $[0], $[2])
   221  [1, 0, 2]
   222  select json_extract('[0,1,2]','$[4]');
   223  json_extract([0,1,2], $[4])
   224  null
   225  select json_extract('[0,1,2]','$[100]');
   226  json_extract([0,1,2], $[100])
   227  null
   228  select json_extract('[0,234,32432,423,5234,11443242,44242342424,23424323]','$[2000]');
   229  json_extract([0,234,32432,423,5234,11443242,44242342424,23424323], $[2000])
   230  null