json函数相关的使用

360以上版本支持json类型及常用函数
create table test_json(id int,json_text varchar(4000));
insert into test_json values(1,'aa');
 insert into test_json values(2,'{"key1":100,"key2":20}');
  insert into test_json values(3,'');
   insert into test_json values(4,null);
    select * from test_json where json_text is json;
    select * from test_json where json_text is not json;

SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]','$[2]') from dual;

1.json_value函数可以使用bson_extract如下命令替代：
bson_extract(order_request::varchar(32739)::json::bson,"orderType")::bson::json from tob_service_order_info_load limit 1;
2.json_table和json_query可以考虑使用bson_extract构造临时结果集处理。
select bson_extract(order_request::varchar(32739)::json::bson,"dnnInfoList.0.dnnName")::bson::json from tob_service_order_info_load limit 1;

当执行插入数据存在错误的JSON数据格式：
JSON数据类型
create table tab_json(c1 json);
insert into tab_json values('{person:{givenname:"Jim",surname:""Flynn"",age:29,cars:["dodge","olds"]}}'::JSON);
1 row(s) inserted.
BSON数据类型
create table tab_bson(c1 bson);
insert into tab_bson values('{person:{givenname:"Jim",surname:""Flynn"",age:29,cars:["dodge","olds"]}}'::JSON);
(U0001) - JSON Syntax error at character 35
Error in line 1
Near character position 110
GBase 8s JSON数据类型对数据格式没有严格校验，而BSON类型对数据格式进行了校验导致此问题。

362版本下面支持以下json函数
> SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]','$[2]') from dual;
(constant)

[20,[30,40]]

1 row(s) retrieved.

> SELECT JSON_REPLACE('{ "a": 1, "b": [2, 3]}', '$.a', 10, '$.b', '[true, false]') from dual;

(constant)

{"a":10,"b":[true,false]}

1 row(s) retrieved.

> SELECT JSON_UNQUOTE('{ "a": 1, "b": [2, 3]}') from dual;

(constant)  {
       "a":    1,
       "b":    [2, 3]
}

1 row(s) retrieved.