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函数列表
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使用示例
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 行记录)