PostgreSQL中JSON函数和操作符
json和jsonb 操作符
操作符右操作数类型描述例⼦例⼦结果
->int 获得 JSON 数组元素（索引从 0 开始，负整
数从末尾开始计）
'[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2{"c":"baz"}
->text通过键获得 JSON 对象域'{"a": {"b":"foo"}}'::json->'a'{"b":"foo"} ->>int以text形式获得 JSON 数组元素'[1,2,3]'::json->>23
->>text以text形式获得 JSON 对象域'{"a":1,"b":2}'::json->>'b'2
#>text[]获取在指定路径的 JSON 对象'{"a": {"b":{"c": "foo"}}}'::json#>'{a,b}'{"c": "foo"} #>>text[]以text形式获取在指定路径的 JSON 对象'{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}'3
额外的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[]这些数组字符串中的任何⼀个是否做为顶层键存在？'{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'c']
&text[]是否所有这些数组字符串都作为顶层键存在？'["a", "b"]'::jsonb ?& array['a', 'b']
||jsonb把两个jsonb值串接成⼀个新的jsonb值'["a", "b"]'::jsonb || '["c", "d"]'::jsonb
-text从左操作数删除键/值对或者string 元素。键/值对基于它们的键值来匹配。'{"a": "b"}'::jsonb - 'a'
-text[]从左操作数中删除多个键/值对或者string元素。键/值对基于它们的键值来匹配。'{"a": "b", "c": "d"}'::jsonb - '{a,c}'::text[]
-
integer 删除具有指定索引（负值表⽰倒数）的数组元素。如果 顶层容器不是数组则抛出⼀个错
误。
'["a", "b"]'::jsonb - 1
#-text[]删除具有指定路径的域或者元素（对于 JSON 数组，负值 表⽰倒数）'["a", {"b":1}]'::jsonb #- '{1,b}' JSON 创建函数
函数描述例⼦例⼦结果
to_json(anyelement) to_jsonb(anyelement)把该值返回为json或者jsonb。数组和组合 会被（递归）转换成数组和对象；对于
不是数组和组合的值，如果有 从该类型到json的造型，造型函数将被⽤来执⾏该
转换；否则将产⽣⼀个标量值。对于任何不是数字、布尔、空值的标 量类型，将使
⽤⽂本表达，在这种风格下它是⼀个合法的 json或者jsonb值。
to_json('Fred said
"Hi."'::text)
"Fred said
\"Hi.\""
array_to_json(anyarray [, pretty_bool])把数组作为⼀个 JSON 数组返回。⼀个 PostgreSQL 多维数组会成为⼀个数组 的
JSON 数组。如果pretty_bool为真，将在 第 1 维度的元素之间增加换⾏。
array_to_json('{{1,5},
{99,100}}'::int[])
[[1,5],[99,100]]
row_to_json(record [, pretty_bool])把⾏作为⼀个 JSON 对象返回。如果pretty_bool为真，将在第1层元素之间增加换
⾏。
row_to_json(row(1,'fo
o'))
{"f1":1,"f2":"foo
"}
json_build_array(VARI
ADIC "any")
jsonb_build_array(VAR IADIC "any")从⼀个可变参数列表构造⼀个可能包含异质类型的 JSON 数组。
json_build_array(1,2,'
3',4,5)
[1, 2, "3", 4, 5]
IADIC "any")
json_build_object(VAR
IADIC "any")
jsonb_build_object(VA RIADIC "any")从⼀个可变参数列表构造⼀个 JSON 对象。通过转换，该参数列表由交替 出现的
键和值构成。
json_build_object('foo
',1,'bar',2)
{"foo": 1, "bar":
2}
json_object(text[]) jsonb_object(text[])从⼀个⽂本数组构造⼀个 JSON 对象。该数组必须可以是具有偶数个成员的 ⼀维
数组（成员被当做交替出现的键/值对），或者是⼀个⼆维数组（每⼀个 内部数组
刚好有 2 个元素，可以被看做是键/值对）。
json_object('{a, 1,
b, "def", c, 3.5}')
json_object('{{a, 1},
{b, "def"},{c, 3.5}}')
{"a": "1", "b":
"def", "c":
"3.5"}
json_object(keys text[],
values text[]) jsonb_object(keys text[], values text[])json_object的这种形式从两个独⽴的数组得到键/值对。在其 他⽅⾯和⼀个参数的
形式相同。
json_object('{a, b}',
'{1,2}')
{"a": "1", "b":
"2"}
函数描述例⼦例⼦结果
JSON 处理
函数返回值描述例⼦例⼦结果json_array_length(j
son)
jsonb_array_length( jsonb)int
返回最外层 JSON 数组中的元素数
量。
json_array_length('[1,2,3,{"f1":1,"f2":
[5,6]},4]')
5
json_each(json) jsonb_each(jsonb)setof key text,
value json
setof key text,
value jsonb
扩展最外层的 JSON 对象成为⼀组键/
值对。
select * from json_each('{"a":"foo",
"b":"bar"}')
key | value
-----+-------
a  | "foo"
b  | "bar"
json_each_text(json
)
jsonb_each_text(jso nb)setof key text,
value text
扩展最外层的 JSON 对象成为⼀组键/
值对。返回值将是text类型。
select * from json_each_text('{"a":"foo",
"b":"bar"}')
key | value
-----+-------
a  | foo
b  | bar
json_extract_path(fr om_json json, VARIADIC
path_elems text[]) jsonb_extract_path(f rom_json jsonb, VARIADIC
path_elems text[])json
jsonb
返回由path_elems指向的 JSON 值
（等效于#>操作符）。
json_extract_path('{"f2":{"f3":1},"f4":
{"f5":99,"f6":"foo"}}','f4')
{"f5":99,"f6":"foo"}
json_extract_path_t ext(from_json json, VARIADIC
path_elems text[]) jsonb_extract_path_ text(from_json jsonb, VARIADIC path_elems text[])text
以text返回由path_elems指向的 JSON
值（等效于#>>操作符）。
json_extract_path_text('{"f2":{"f3":1},"f4":
{"f5":99,"f6":"foo"}}','f4', 'f6')
foo
json_object_key
json_object_keys(js
on)
jsonb_object_keys(j sonb)setof text返回最外层 JSON 对象中的键集合。
json_object_keys('{"f1":"abc","f2":{"f3":"a",
"f4":"b"}}')
json_object_key
s
------------------
f1
f2
json_populate_reco rd(base anyelement,
from_json json) jsonb_populate_rec ord(base anyelement,
from_json jsonb)anyelement
扩展from_json中的对象成⼀个⾏，它
的列匹配由base定义的记录类型（见
下⽂的注释）。
select * from
json_populate_record(null::myrowtype,
'{"a": 1, "b": ["2", "a b"], "c": {"d": 4, "e": "a b
c"}}')
a |
b      |      c
-
--+-----------+----
---------
1 | {2,"a b"} |
(4,"a b c")
json_populate_reco rdset(base anyelement,
from_json json) jsonb_populate_rec ordset(base anyelement,
from_json jsonb)setof anyelement
扩展from_json中最外的对象数组为⼀
个集合，该集合的列匹配由base定义
的记录类型。
select * from
json_populate_recordset(null::myrowtype,
'[{"a":1,"b":2},{"a":3,"b":4}]')
a | b
---+---
1 | 2
3 | 4
json_array_element
s(json)
jsonb_array_eleme nts(jsonb)setof json
setof jsonb
把⼀个 JSON 数组扩展成⼀个 JSON
值的集合。
select * from json_array_elements('[1,true,
[2,false]]')
value
-----------
1
true
[2,false]
json_array_element
s_text(json) jsonb_array_eleme nts_text(jsonb)setof text
把⼀个 JSON 数组扩展成⼀个text值集
合。
select * from
json_array_elements_text('["foo", "bar"]')
value
-----------
foo
bar
json_typeof(json) jsonb_typeof(jsonb)text
把最外层的 JSON 值的类型作为⼀个
⽂本字符串返回。可能的类型
是： object、array、string、number、
boolean以及null。
json_typeof('-123.4')number
json_to_record(json
)
jsonb_to_record(jso nb)record
从⼀个 JSON 对象（见下⽂的注解）
构建⼀个任意的记录。正如所有返
回record 的函数⼀样，调⽤者必须⽤
⼀个AS⼦句显式地定义记录的结构。
select * from json_to_record('{"a":1,"b":
[1,2,3],"c":[1,2,3],"e":"bar","r": {"a": 123, "b":
"a b c"}}') as x(a int, b text, c int[], d text, r
myrowtype)
a |
b    |
c    |
d |      r
---+---------+------
---+---+------------
---
1 | [1,2,3] |
{1,2,3} |  |
(123,"a b c")
json_to_recordset(js
on)
jsonb_to_recordset( jsonb)setof record
从⼀个 JSON 对象数组（见下⽂的注
解）构建⼀个任意的记录集合。正如所
有返回record 的函数⼀样，调⽤者必
须⽤⼀个AS⼦句显式地定义记录的结
构。
select * from
json_to_recordset('[{"a":1,"b":"foo"},
{"a":"2","c":"bar"}]') as x(a int, b text);
a |  b
---+-----
1 | foo
2 |
函数返回值描述例⼦例⼦结果
json_strip_nulls(fro
m_json json) jsonb_strip_nulls(fro m_json jsonb)json
jsonb
返回from_json，其中所有具有空值的
对象域都被省略。其他空值不动。
json_strip_nulls('[{"f1":1,"f2":null},2,null,3]')[{"f1":1},2,null,3]
jsonb_set(target jsonb, path text[],
new_value
jsonb[, create_missi ng boolean])jsonb
返回target，其中由 path指定的节
⽤ new_value替换，如果 path指定的
项不存在并且 create_missing为真
（默认为 true）则加上 new_value。
正如⾯向路径的 操作符⼀样，出现
在path中的 负整数表⽰从 JSON 数组
的末尾开始数。
jsonb_set('[{"f1":1,"f2":null},2,null,3]',
'{0,f1}','[2,3,4]', false)
jsonb_set('[{"f1":1,"f2":null},2]',
'{0,f3}','[2,3,4]')
[{"f1":
[2,3,4],"f2":null},2,
null,3]
[{"f1": 1, "f2": null,
"f3": [2, 3, 4]}, 2]
jsonb_insert(target jsonb, path text[],
new_value jsonb, [insert_after boolea n])jsonb
返回被插⼊了new_value的target。如
果path指定的target节在⼀个 JSONB
数组中，new_value将被插⼊到⽬标之
前（insert_after为false，默认情况）
或者之后（insert_after为真）。如
果path指定的target节在⼀个 JSONB
对象内，则只有当target不存在时才插
⼊new_value。对于⾯向路径的操作符
来说，出现在path中的负整数表⽰从
JSON 数组的末尾开始计数。
jsonb_insert('{"a": [0,1,2]}', '{a, 1}',
'"new_value"')
jsonb_insert('{"a": [0,1,2]}', '{a, 1}',
'"new_value"', true)
{"a": [0,
"new_value", 1,
2]}
{"a": [0, 1,
"new_value", 2]}
jsonb_pretty(from_js on jsonb)text把from_json返回成⼀段 缩进后的
JSON ⽂本。
jsonb_pretty('[{"f1":1,"f2":null},2,null,3]')
[
{
"f1": 1,
"f2": null
},
2,
null,
3
]
函数返回值描述例⼦例⼦结果
上⾯内容看起来⽐较晦涩，其实postgreSQL中json函数的操作在实际⽤的时候也是⽐较⽅便的。
发⽂笔记本没有安装数据库⼯具，公司不能访问外⽹，就不上代码了
我在csdn已经到同僚分析的代码部分⽰例，相信你看过我转载的内容，就能对json有个新的认知，⼀通百通。理解这⼀个，其他的函数，⾃⼰尝试验证⼀下。
⼀、 -> 和 ->> ：
-> 表⽰获取⼀个JSON数组元素，⽀持下标值(下标从0开始)、Key获取。->> 表⽰获取⼀个JSON对象字符串。
代码：
SELECT '[{"a":1},{"b":2},{"c":3}]'::JSON -> 1;
结果：
{"b":2}
以上，::JSON 表⽰声明前⾯的字符串为⼀个JSON字符串对象，⽽且PostgreSQL中的JSON、JSONB对象 Key的声明必须是字符串 。同时，1表⽰获取JSON数组中下标值为1的JSON对象。
接下来，看下 ->> 的⽤法：
代码：
SELECT '[{"a":1},{"b":2},{"c":3}]'::JSON ->> 1;
结果：
{"b":2}
以上，->> 的查询结果和 -> 对⽐不太直观，我们可以进⼀步验证。
代码：
SELECT '[{"a":1},{"b":2},{"c":3}]'::JSON -> 1 -> 'b';
结果：
2
以上，我们可以看到⾸先我们使⽤下标的⽅式，获取JSON数组中下标值为1的JSON对象 {"b":2}。然后，我们通过Key的⽅式来获取这个JSON对象的Value值，结果是 2。
接下来，我们测试下 ->> 的⽅式来获取：
代码：
SELECT '[{"a":1},{"b":2},{"c":3}]'::JSON ->> 1 -> 'b';
报错：
[SQL]SELECT '[{"a":1},{"b":2},{"c":3}]'::JSON ->> 1 -> 'b';
[Err] 错误:  操作符不存在: text -> unknown
LINE 1: SELECT '[{"a":1},{"b":2},{"c":3}]'::JSON ->> 1 -> 'b';
^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.
以上，可以看到错误提⽰操作符不存在，因为第⼀步查询出来的是⼀个字符串，不是JSON对象。当然，我们可以改造下这个查询语句：
代码：
SELECT ('[{"a":1},{"b":2},{"c":3}]'::JSON ->> 1)::JSON -> 'b';
结果：
2typeof array
以上，可以看到现在可以正确查询出结果，我们将第⼀步查询出来的字符串转成JSON对象，然后通过 Key 的⽅式来获取 Value。不过，这种查询⽅式相对于 -> 来说还是⽐较繁琐的。
⼆、 #> 和 #>> ：
在前⼀步，我们在⼀个JSON数组中可以使⽤ -> 下标值的⽅式来获取⼀个JSON对象。但是，如果我们我们检索的不是JSON数组，⽽是⼀个JSON对象中的JSON对象。很显然，这种下标获取的⽅式不再适⽤。不过，我们可以使⽤下⾯的⽅式来获取。
#> 表⽰获取指定路径的⼀个JSON对象，#>>表⽰获取指定路径的⼀个JSON对象的字符串。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。