JSON/JSONB函数和操作符

JSON/JSONB操作符

JSON/JSONB通用操作符参考表1，JSONB额外支持操作符参考表2。

表 1 JSON/JSONB通用操作符

操作符	左操作数类型	右操作数类型	返回类型	描述	例子	例子结果
->	Array-json(b)	int	json(b)	获得array-json元素。下标不存在返回空。	'[{“a”:“foo”},{“b”:“bar”},{“c”:“baz”}]'::json->2	{“c”:“baz”}
->	object-json(b)	text	json(b)	通过键获得值。不存在则返回空。	'{“a”: {“b”:“foo”}}'::json->'a'	{“b”:“foo”}
->>	Array-json(b)	int	text	获得 JSON 数组元素。下标不存在返回空。	'[1,2,3]'::json->>2	3
->>	object-json(b)	text	text	通过键获得值。不存在则返回空。	'{“a”:1,“b”:2}'::json->>'b'	2
#>	container-json (b)	text[]	json(b)	获取在指定路径的 JSON 对象，路径不存在则返回空。	'{“a”: {“b”:{“c”: “foo”}}}'::json #>'{a,b}'	{“c”: “foo”}
#>>	container-json (b)	text[]	text	获取在指定路径的 JSON 对象，路径不存在则返回空。	'{“a”:[1,2,3],“b”:[4,5,6]}'::json #>>'{a,2}'	3

对于 #> 和 #>> 操作符，当给出的路径无法查找到数据时，不会报错，会返回空。

表 2 JSONB额外支持操作符

操作符	右操作数类型	描述	例子
@>	jsonb	左边的 JSON的顶层是否包含右边JSON的顶层所有项。	'{“a”:1, “b”:2}'::jsonb @> '{“b”:2}'::jsonb
<@	jsonb	左边的 JSON的所有项是否全部存在于右边JSON的顶层。	'{“b”:2}'::jsonb <@ '{“a”:1, “b”:2}'::jsonb
?	text	键/元素的字符串是否存在于 JSON 值的顶层。	'{“a”:1, “b”:2}'::jsonb ? 'b'
?		text[]	这些数组字符串中的任何一个是否做为顶层键存在。
?&	text[]	是否所有这些数组字符串都作为顶层键存在。	'[“a”, “b”]'::jsonb ?& array['a', 'b']
=	jsonb	判断两个jsonb的大小关系，同函数jsonb_eq。	/
<>	jsonb	判断两个jsonb的大小关系，同函数jsonb_ne。	/
<	jsonb	判断两个jsonb的大小关系，同函数jsonb_lt。	/
>	jsonb	判断两个jsonb的大小关系，同函数jsonb_gt。	/
<=	jsonb	判断两个jsonb的大小关系，同函数jsonb_le。	/
>=	jsonb	判断两个jsonb的大小关系，同函数jsonb_ge。	/

JSON/JSONB支持的函数

array_to_json(anyarray [, pretty_bool])

描述：返回JSON类型的数组。一个多维数组成为一个JSON数组的数组。如果pretty_bool为true，将在一维元素之间添加换行符。

返回类型：json

示例：
```
SELECT array_to_json('{{1,5},{99,100}}'::int[]);
```
当结果显示如下信息，则表示函数调用成功。
```
  array_to_json   
------------------
 [[1,5],[99,100]]
(1 row)
```
row_to_json(record [, pretty_bool])

描述：返回JSON类型的行。如果pretty_bool为true，将在第一级元素之间添加换行符。

返回类型：json

示例：
```
SELECT row_to_json(row(1,'foo'));
```
当结果显示如下信息，则表示函数调用成功。
```
   row_to_json     
---------------------
{"f1":1,"f2":"foo"}   
(1 row)
```
json_array_element(array-json, integer)、jsonb_array_element(array-jsonb, integer)

描述：同操作符'->'，返回数组中指定下标的元素。

返回类型：json、jsonb

示例：
```
select json_array_element('[1,true,[1,[2,3]],null]',2);
```
当结果显示如下信息，则表示函数调用成功。
```
json_array_element
--------------------
[1,[2,3]]
(1 row)
```
json_array_element_text(array-json, integer)、jsonb_array_element_text(array-jsonb, integer)

描述：同操作符'->>'，返回数组中指定下标的元素。

返回类型：text、text

示例：
```
select json_array_element_text('[1,true,[1,[2,3]],null]',2);
```
当结果显示如下信息，则表示函数调用成功。
```
json_array_element_text
-----------------------
[1,[2,3]]
(1 row)
```
json_object_field(object-json, text)、jsonb_object_field(object-jsonb, text)

描述：同操作符'->'，返回对象中指定键对应的值。

返回类型：json、json

示例：
```
select json_object_field('{"a": {"b":"foo"}}','a');
```
当结果显示如下信息，则表示函数调用成功。
```
json_object_field
-------------------
{"b":"foo"}
(1 row)
```
json_object_field_text(object-json, text)、jsonb_object_field_text(object-jsonb, text)

描述：同操作符'->>'，返回对象中指定键对应的值。

返回类型：text、text

示例：
```
select json_object_field_text('{"a": {"b":"foo"}}','a');
```
当结果显示如下信息，则表示函数调用成功。
```
json_object_field_text
----------------------
{"b":"foo"}
(1 row)
```
json_extract_path(json, VARIADIC text[])、jsonb_extract_path((jsonb, VARIADIC text[])

描述：等价于操作符'#>'。根据$2所指的路径，查找json，并返回。

返回类型：json、jsonb

示例：
```
select json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}', 'f4','f6');
```
当结果显示如下信息，则表示函数调用成功。
```
 json_extract_path
-------------------
 "stringy"
(1 row)
```
json_extract_path_op(json, text[])、jsonb_extract_path_op(jsonb, text[])

描述：同操作符'#>'。根据$2所指的路径，查找json，并返回。

返回类型：json、jsonb

示例：
```
select json_extract_path_op('{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}', ARRAY['f4','f6']);
```
当结果显示如下信息，则表示函数调用成功。
```
json_extract_path_op
----------------------
"stringy"
(1 row)
```
json_extract_path_text(json, VARIADIC text[])、jsonb_extract_path_text((jsonb, VARIADIC text[])

描述：等价于操作符'#>>'。根据$2所指的路径，查找json，并返回。

返回类型：text、text

示例：
```
select json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}', 'f4','f6');
```
当结果显示如下信息，则表示函数调用成功。
```
json_extract_path_text
-----------------------
stringy
(1 row)
```
json_extract_path_text_op(json, text[])、jsonb_extract_path_text_op(jsonb, text[])

描述：同操作符'#>>'。根据$2所指的路径，查找json，并返回。

返回类型：text、text

示例：
```
select json_extract_path_text_op('{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}', ARRAY['f4','f6']);
```
当结果显示如下信息，则表示函数调用成功。
```
json_extract_path_text_op
---------------------------
stringy
(1 row)
```
json_array_elements(array-json)、jsonb_array_elements(array-jsonb)

描述：拆分数组，每一个元素返回一行。

返回类型：json、jsonb

示例：
```
select json_array_elements('[1,true,[1,[2,3]],null]');
```
当结果显示如下信息，则表示函数调用成功。
```
json_array_elements
---------------------
1
true
[1,[2,3]]
null
(4 rows)
```
json_array_elements_text(array-json)、jsonb_array_elements_text(array-jsonb)

描述：拆分数组，每一个元素返回一行。

返回类型：text、text

示例：
```
select * from  json_array_elements_text('[1,true,[1,[2,3]],null]');
```
当结果显示如下信息，则表示函数调用成功。
```
 value
-----------
 1
 true
 [1,[2,3]]   
 (4 rows)
```
json_array_length(array-json)、jsonb_array_length(array-jsonb)

描述：返回数组长度。

返回类型：integer

示例：
```
SELECT json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4,null]');
```
当结果显示如下信息，则表示函数调用成功。
```
json_array_length
-------------------
                 6
(1 row)
```
json_each(object-json)、jsonb_each(object-jsonb)

描述：将对象的每个键值对拆分转换成一行两列。

返回类型：setof(key text, value json)、setof(key text, value jsonb)

示例：
```
select * from  json_each('{"f1":[1,2,3],"f2":{"f3":1},"f4":null}');
```
当结果显示如下信息，则表示函数调用成功。
```
 key |  value
-----+----------
 f1  | [1,2,3]
 f2  | {"f3":1}
 f4  | null
(3 rows)
```
json_each_text(object-json)、jsonb_each_text(object-jsonb)

描述：将对象的每个键值对拆分转换成一行两列。

返回类型：setof(key text, value text)、setof(key text, value text)

示例：
```
select * from  json_each_text('{"f1":[1,2,3],"f2":{"f3":1},"f4":null}');
```
当结果显示如下信息，则表示函数调用成功。
```
 key |  value
-----+----------
 f1  | [1,2,3]
 f2  | {"f3":1}
 f4  |
(3 rows)
```
json_object_keys(object-json)、jsonb_object_keys(object-jsonb)

描述：返回对象中顶层的所有键。

返回类型：SETOF text

示例：
```
select json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}, "f1":"abcd"}');
```
当结果显示如下信息，则表示函数调用成功。
```
json_object_keys
------------------
 f1
 f2
 f1
(3 rows)
```

jsonb中会有去重操作

select jsonb_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}, "f1":"abcd"}');

当结果显示如下信息，则表示函数调用成功。

 jsonb_object_keys
-------------------
 f1
 f2
(2 rows)

json_populate_record(anyelement, object-json [, bool])、jsonb_populate_record(anyelement, object-jsonb [, bool])

描述：$1必须是一个复合类型的参数。将会把object-json里的每个对键值进行拆分，以键当做列名，与$1中的列名进行匹配查找，并填充到$1的格式中。

返回类型：anyelement、anyelement

示例：

1、创建类型jpop。
```
create type jpop as (a text, b int, c bool);
```
2、查询验证。
```
select * from json_populate_record(null::jpop,'{"a":"blurfl","x":43.2}');
```
当结果显示如下信息，则表示函数调用成功。
```
   a    | b | c
--------+---+---
 blurfl |   |
(1 row)
```
3、查询验证。
```
select * from json_populate_record((1,1,null)::jpop,'{"a":"blurfl","x":43.2}');
```
当结果显示如下信息，则表示函数调用成功。
```
   a    | b | c
--------+---+---
 blurfl | 1 |
(1 row)
```
json_populate_record_set(anyelement, array-json [, bool])、jsonb_populate_record_set(anyelement, array-jsonb [, bool])

描述：参考上述函数json_populate_record、jsonb_populate_record，对$2数组的每一个元素进行上述参数函数的操作，因此这也要求$2数组的每个元素都是object-json类型的。

返回类型：setof anyelement、setof anyelement

示例：

1、创建类型jpop
```
create type jpop as (a text, b int, c bool);
```
2、查询验证
```
select * from json_populate_recordset(null::jpop, '[{"a":1,"b":2},{"a":3,"b":4}]');
```
当结果显示如下信息，则表示函数调用成功。
```
a | b | c
---+---+---
1 | 2 |
3 | 4 |
(2 rows)
```

json_typeof(json)、jsonb_typeof(jsonb)

描述：检测json类型

返回类型：text、text

示例：

select value, json_typeof(value) from (values (json '123.4'), (json '"foo"'), (json 'true'), (json 'null'), (json '[1, 2, 3]'), (json '{"x":"foo", "y":123}'), (NULL::json))  as data(value);

当结果显示如下信息，则表示函数调用成功。

        value         | json_typeof
----------------------+-------------
 123.4                | number
 "foo"                | string
 true                 | boolean
 null                 | null
 [1, 2, 3]            | array
 {"x":"foo", "y":123} | object
                      |
(7 rows)

json_build_array( [VARIADIC “any”] )

描述：从一个可变参数列表构造出一个JSON数组。

返回类型：array-json

示例：

select json_build_array('a',1,'b',1.2,'c',true,'d',null,'e',json '{"x": 3, "y": [1,2,3]}','');

当结果显示如下信息，则表示函数调用成功。

                            json_build_array                               
-----------------------------------------------------------------------------
["a", 1, "b", 1.2, "c", true, "d", null, "e", {"x": 3, "y": [1,2,3]}, null]
(1 row)

json_build_object( [VARIADIC “any”] )

描述：从一个可变参数列表构造出一个JSON对象，其入参必须为偶数个，两两一组组成键值对。注意键不可为null。

返回类型：object-json

示例：
```
select json_build_object(1,2);
```
当结果显示如下信息，则表示函数调用成功。
```
 json_build_object
-------------------
 {"1" : 2}
(1 row)
```
json_to_record(object-json, bool)

描述：正如所有返回record 的函数一样，调用者必须用一个AS子句显式地定义记录的结构。会将object-json的键值对进行拆分重组，把键当做列名，去匹配填充as显示指定的记录的结构。

返回类型：record

示例：
```
select * from json_to_record('{"a":1,"b":"foo","c":"bar"}',true) as x(a int, b text, d text);
```
当结果显示如下信息，则表示函数调用成功。
```
 a |  b  | d
---+-----+---
 1 | foo |
(1 row)
```
json_to_recordset(array-json, bool)

描述：参考函数json_to_record，对数组内个每个元素，执行上述函数的操作，因此这要求数组内的每个元素都得是object-json。

返回类型：setof record

示例：
```
select * from json_to_recordset('[{"a":1,"b":"foo","d":false},{"a":2,"b":"bar","c":true}]',false) as x(a int, b text, c boolean);
```
当结果显示如下信息，则表示函数调用成功。
```
 a |  b  | c
---+-----+---
 1 | foo |
 2 | bar | t
(2 rows)
```
json_object(text[])、json_object(text[], text[])

描述：从一个文本数组构造一个object-json。这是个重载函数，当入参为一个文本数组的时候，其数组长度必须为偶数，成员被当做交替出现的键/值对。两个文本数组的时候，第一个数组认为是键，第二个认为是值，两个数组长度必须相等。键不可为null。

返回类型：object-json

示例1：
```
select json_object('{a,1,b,2,3,NULL,"d e f","a b c"}');
```
当结果显示如下信息，则表示函数调用成功。
```
                      json_object
-------------------------------------------------------
 {"a" : "1", "b" : "2", "3" : null, "d e f" : "a b c"}
(1 row)
```
示例2：
```
select json_object('{a,b,"a b c"}', '{a,1,1}');
```
当结果显示如下信息，则表示函数调用成功。
```
              json_object
---------------------------------------
 {"a" : "a", "b" : "1", "a b c" : "1"}
(1 row)
```

json_agg(any)

描述：将值聚集为json数组。

返回类型：array-json

示例：

1、创建测试表class，并插入测试数据。

CREATE TABLE class(name varchar,score int);
INSERT INTO class(name,score) values ('A' ,'2');
INSERT INTO class(name,score) values ('A' ,'3');
INSERT INTO class(name,score) values ('D' ,'5');
INSERT INTO class(name,score) values ('D' ,'');

2、调用函数进行验证。

select name, json_agg(score) score from class group by name order by name;

当结果显示如下信息，则表示函数调用成功。

 name |   score
------+-----------
 A    | [2, 3]
 D    | [5, null]
(2 rows)

json_object_agg(any, any)

描述：将值聚集为json对象。

返回类型：object-json

示例：

1、创建测试表class，并插入测试数据。

CREATE TABLE class(name varchar,score int);
INSERT INTO class(name,score) values ('A' ,'2');
INSERT INTO class(name,score) values ('A' ,'3');
INSERT INTO class(name,score) values ('D' ,'5');
INSERT INTO class(name,score) values ('D' ,'');

2、调用函数进行验证。

select json_object_agg(name, score) from class group by name order by name;

当结果显示如下信息，则表示函数调用成功。

     json_object_agg
-------------------------
 { "A" : 2, "A" : 3 }
 { "D" : 5, "D" : null }
(2 rows)

jsonb_contained(jsonb, jsonb)

描述：同操作符 '<@'，判断$1中的所有元素是否在$2的顶层存在。

返回类型：bool

示例：
```
select jsonb_contained('[1,2,3]', '[1,2,3,4]');
```
当结果显示如下信息，则表示函数调用成功。
```
 jsonb_contained
-----------------
 t
(1 row)
```
jsonb_contains(jsonb, jsonb)

描述：同操作符 '@>'，判断$1中的顶层所有元素是否包含在$2的所有元素。

返回类型：bool

示例：
```
select jsonb_contains('[1,2,3,4]', '[1,2,3]');
```
当结果显示如下信息，则表示函数调用成功。
```
 jsonb_contains
----------------
 t
(1 row)
```
jsonb_exists(jsonb, text)

描述：同操作符 '?'，字符串$2是否存在$1的顶层以keyelemscalar的形式存在。

返回类型：bool

示例：
```
select jsonb_exists('["1",2,3]', '1');
```
当结果显示如下信息，则表示函数调用成功。
```
 jsonb_exists
--------------
 t
(1 row)
```
jsonb_exists_all(jsonb, text[])

描述：同操作符 '?&'，字符串数组$2里面，是否所有的元素，都在$1的顶层以keyelemscalar的形式存在。

返回类型：bool

示例：
```
select jsonb_exists_all('["1","2",3]', '{1, 2}');
```
当结果显示如下信息，则表示函数调用成功。
```
 jsonb_exists_all
------------------
 t
(1 row)
```
jsonb_exists_any(jsonb, text[])

描述：同操作符 '?|'，字符串数组$2里面，是否存在的元素，在$1的顶层以keyelemscalar的形式存在。

返回类型：bool

示例：
```
select jsonb_exists_any('["1","2",3]', '{1, 2, 4}');
```
当结果显示如下信息，则表示函数调用成功。
```
 jsonb_exists_any
------------------
 t
(1 row)
```
jsonb_cmp(jsonb, jsonb)

描述：比较大小，正数代表大于，负数代表小于，0表示相等。

返回类型：integer

示例：
```
select jsonb_cmp('["a", "b"]', '{"a":1, "b":2}');
```
当结果显示如下信息，则表示函数调用成功。
```
jsonb_cmp
-----------
       -1
(1 row)
```
jsonb_eq(jsonb, jsonb)

描述：同操作符 '='，比较两个值的大小。

返回类型：bool

示例：
```
select jsonb_eq('["a", "b"]', '{"a":1, "b":2}');
```
当结果显示如下信息，则表示函数调用成功。
```
jsonb_eq
----------
f
(1 row)
```
jsonb_ne(jsonb, jsonb)

描述：同操作符 '<>'，比较两个值的大小。

返回类型：bool

示例：
```
select jsonb_ne('["a", "b"]', '{"a":1, "b":2}');
```
当结果显示如下信息，则表示函数调用成功。
```
jsonb_ne
----------
t
(1 row)
```
jsonb_gt(jsonb, jsonb)

描述：同操作符 '>'，比较两个值的大小。

返回类型：bool

示例：
```
select jsonb_gt('["a", "b"]', '{"a":1, "b":2}');
```
当结果显示如下信息，则表示函数调用成功。
```
 jsonb_gt
----------
 f
(1 row)
```
jsonb_ge(jsonb, jsonb)

描述：同操作符 '>='，比较两个值的大小。

返回类型：bool

示例：
```
select jsonb_ge('["a", "b"]', '{"a":1, "b":2}');
```
当结果显示如下信息，则表示函数调用成功。
```
 jsonb_ge
----------
 f
(1 row)
```
jsonb_lt(jsonb, jsonb)

描述：同操作符 '<'，比较两个值的大小。

返回类型：bool

示例：
```
select jsonb_lt('["a", "b"]', '{"a":1, "b":2}');
```
当结果显示如下信息，则表示函数调用成功。
```
 jsonb_lt
----------
 t
(1 row)
```
jsonb_le(jsonb, jsonb)

描述：同操作符 '<='，比较两个值的大小。

返回类型：bool

示例：
```
select jsonb_le('["a", "b"]', '{"a":1, "b":2}');
```
当结果显示如下信息，则表示函数调用成功。
```
 jsonb_le
----------
 t
(1 row)
```
to_json(anyelement)

描述：把参数转换为'json'。

返回类型：json

示例：
```
select to_json('{1,5}'::text[]);
```
当结果显示如下信息，则表示函数调用成功。
```
 to_json
-----------
["1","5"]
(1 row)
```
jsonb_hash(jsonb)

描述：对jsonb进行hash运算。

返回类型：integer

示例：
```
select jsonb_hash('[1,2,3]');
```
当结果显示如下信息，则表示函数调用成功。
```
jsonb_hash
------------
-559968547
(1 row)
```
其他函数

描述：gin索引以及jsonjsonb聚集函数所用到的内部函数，功能不过多赘述。
- gin_compare_jsonb
- gin_consistent_jsonb
- gin_consistent_jsonb_hash
- gin_extract_jsonb
- gin_extract_jsonb_hash
- gin_extract_jsonb_query
- gin_extract_jsonb_query_hash
- gin_triconsistent_jsonb
- gin_triconsistent_jsonb_hash
- json_agg_transfn
- json_agg_finalfn
- json_object_agg_transfn
- json_object_agg_finalfn

VastbaseG100

JSON/JSONB函数和操作符

JSON/JSONB操作符

JSON/JSONB支持的函数

弹框头部