金仓数据库KingbaseES Json 系列十二--Json其他函数

JSON 数据类型是用来存储 JSON(JavaScript Object Notation)数据的。KingbaseES为存储JSON数据提供了两种类型:JSON和 JSONB。JSON 和 JSONB 几乎接受完全相同的值集合作为输入。

本文将主要介绍Kingbase数据库的Json其他处理函数部分。

准备数据：

CREATE TABLE "public"."jsontable" (
	"id" integer NULL,
	"jsondata" json NULL,
	"jsonvarchar" varchar NULL,
	"jsonarray" json NULL,
	"jsonrecord" json NULL,
	"jsonset" json NULL
);

INSERT INTO "public"."jsontable" ("id","jsondata","jsonvarchar","jsonarray","jsonrecord","jsonset") VALUES
	 (1,'{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','{"f2": {"f3": 1}, "f4": {"f5": 99, "f6": "foo"}}','[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false,"stringy"]','{"a":1,"b":"bcol","c":"cc"}','[{"a":1,"b":"bcol","c":"cc"},{"a":1,"b":"bcol","d":""}]'),
	 (2,'{"a":[1,2,3,4,5]}','{"a": [1, 2, 3, 4, 5]}','[1,2,3,4,5]','{"a":1,"b":"bcol","c":""}','[{"a":1,"b":"bcol","c":""},{"a":1,"b":"bcol","e":""}]'),
	 (3,'{"a":1, "b": ["2", "a b"],"c": {"d":4, "e": "ab c"}}','{"a": 1, "b": ["2", "a b"], "c": {"d": 4, "e": "ab c"}}','[{"f1":1,"f2":null},2,null,3]','{"a":1,"b":"bcol","d":"dd"}','[{"a":1,"b":"bcol","c":"cc_3_1"},{"a":1,"b":"bcol","c":"cc_3_2"}]');

json函数列表

• JSONB_TYPEOF

• JSON_SCALAR

• JSON_SERIALIZE

• JSON_TYPEOF

• JSON_VALUE

json函数简介

JSONB_TYPEOF

功能：

JSON函数，把最外层的JSON对象类型作为一个文本串返回。可能类型是： object 、 array 、 string 、number 、 boolean 以及 null 。

用法：

jsonb_typeof(json)

示例：

demo=# SELECT jsonb_typeof('true');
 jsonb_typeof 
--------------
 boolean
(1 行记录)

demo=# SELECT jsonb_typeof('"string"');
 jsonb_typeof 
--------------
 string
(1 行记录)

demo=# select jsonb_typeof('{"a": [1, 2, 3, 4, 5]}') ;
 jsonb_typeof 
--------------
 object
(1 行记录)

demo=# select jsonb_typeof('[1, 2, 3, 4, 5]') ;       
 jsonb_typeof 
--------------
 array
(1 行记录)

demo=# SELECT jsonb_typeof('-123.4');
 jsonb_typeof 
--------------
 number
(1 行记录)

JSON_SCALAR

功能：

JSON函数，使用JSON_SCALAR函数从SQL数据生成JSON标量值。

用法：

json_scalar (
  expression
  [ RETURNING json_data_type ]
)
expression：用于构造JSON标量值的数据。
[ RETURNING json_data_type ]：函数返回值，可以返回json类型或jsonb类型，默认是jsob类型。

示例：

demo=# SELECT json_scalar('{"a": [1, 2, 3, 4, 5]}') ;
        json_scalar         
----------------------------
 "{\"a\": [1, 2, 3, 4, 5]}"
(1 行记录)

demo=# SELECT json_scalar('[1, 2, 3, 4, 5]') ;       
    json_scalar    
-------------------
 "[1, 2, 3, 4, 5]"
(1 行记录)

demo=# SELECT json_scalar('1') ;              
 json_scalar 
-------------
 "1"
(1 行记录)

demo=# SELECT json_scalar('true') ;
 json_scalar 
-------------
 "true"
(1 行记录)

JSON_SERIALIZE

功能：

JSON函数，表达式用于把JSON类型的数据转换为字符串或二进制字符串。返回值可以是任意字符串类型或二进制字符串类型。

用法：

json_serialize (
  expression [ FORMAT JSON [ ENCODING UTF8 ] ]
  [ RETURNING data_type [ FORMAT JSON [ ENCODING UTF8 ] ] ]
)

expression [ FORMAT JSON [ ENCODING UTF8 ] ]：转需要换为字符或二进制字符串的json值。
[ RETURNING data_type [ FORMAT JSON [ ENCODING UTF8 ] ] ]：json数据转换后的数据类型(text, char, bytea)等，默认是text类型。

示例：

demo=# select json_serialize('{"a": [1, 2, 3, 4, 5]}') ;                       
     json_serialize     
------------------------
 {"a": [1, 2, 3, 4, 5]}
(1 行记录)

demo=# select json_serialize('{"a": [1, 2, 3, 4, 5]}' returning varchar(10)) ;
 json_serialize 
----------------
 {"a": [1, 
(1 行记录)

demo=# select json_serialize('{"a": [1, 2, 3, 4, 5]}' returning bytea) ;      
                 json_serialize                 
------------------------------------------------
 \x7b2261223a205b312c20322c20332c20342c20355d7d
(1 行记录)

JSON_TYPEOF

功能：

JSON函数，把最外层的JSON对象类型作为一个文本串返回。可能类型是： object 、 array 、 string 、number 、 boolean 以及 null 。

用法：

json_typeof(json)

示例：

参照JSONB_TYPEOF使用示例

• JSONB_TYPEOF

JSON_VALUE

功能：

JSON函数，从一段给定的json文本中获取所需要的值，返回为标量值或者用户自定义的object或者collection类型 。

用法：

json_value(
  expression [ format json [ encoding utf8 ] ],
  json_basic_path_expression
  [returning json_value_return_type]
  [{ null|error|default literal}  on error]
  [{ null|error|default literal}  on empty]
)

expression：输入的json文本，完整的支持可以为字符常量、函数、或者列名(数据类型为clob,blob或者varchar2)，V8R6C7版本只支持输入类型为JSONB类型，其他类型后续补充；
format json：是在expression为字符串类型时将expression格式化成json格式。
encoding utf8：指定输入为二进制类型时的字符集。
json_basic_path_expression：用于指定json文件所要查询的路径。
returning json_value_return_type：指定返回值的数据类型。若没有使用此子句，返回数据类型为text。支出返回类型：CHAR,VARCHAR,NCHAR,TEXT,NUMERIC,INT2,INT4,INT8,FLOAT4,FLOAT8,DATE,TIME,TIMESTAMP,TIMETZ,TIMESTAMPTZ,JSON,JSONB,BOOL
{ null|error|default literal}  on error：指定错误发生时的返回值。
{ null|error|default literal}  on empty：指定没有结果相匹配时的返回值。

示例：

-- 获取值
demo=# select json_value('{"a": 1, "b": ["2", "a b"], "c": {"d": 4, "e": "ab c"}}'::jsonb,'$.a');
 json_value 
------------
 1
(1 行记录)

demo=# select json_value('{"a": 1, "b": ["2", "a b"], "c": {"d": 4, "e": "ab c"}}'::jsonb,'$.b');
 json_value 
------------
 
(1 行记录)

demo=# select json_value('{"a": 1, "b": ["2", "a b"], "c": {"d": 4, "e": "ab c"}}'::jsonb,'$.b[0]');
 json_value 
------------
 2
(1 行记录)

demo=# select json_value('{"a": 1, "b": ["2", "a b"], "c": {"d": 4, "e": "ab c"}}'::jsonb,'$.b.size()');
 json_value 
------------
 2
(1 行记录)

demo=# select json_value('{"a": 1, "b": ["2", "a b"], "c": {"d": 4, "e": "ab c"}}'::jsonb,'$.*.size() ? (@ > 1)');
 json_value 
------------
 2
(1 行记录)

-- 指定返回值的数据类型。若没有使用此子句，返回数据类型为text。

demo=# select json_value('{"a": 1, "b": ["2", "a b"], "c": {"d": 4, "e": "ab c"}}'::jsonb,'$.*.size() ? (@ > 1)' returning numeric);
 json_value 
------------
          2
(1 行记录)

demo=# select json_value('{"a": 1, "b": ["2", "a b"], "c": {"d": 4, "e": "ab c"}}'::jsonb,'$.*.size() ? (@ > 1)' returning text);   
 json_value 
------------
 2
(1 行记录)