JSON 函数和运算符
JSON 函数
jsonb_array_elements
可将顶级 JSON 数组扩展为一组 JSON 值。
jsonb_array_elements ( jsonb ) → setof jsonb
SELECT * FROM jsonb_array_elements('[1,true, [2,false]]'::jsonb);
------返回结果
1
true
[2, false]
jsonb_array_elements_text
可将顶级 JSON 数组扩展为一组文本(varchar)值。
jsonb_array_elements_text ( jsonb ) → setof varchar
SELECT * FROM jsonb_array_elements_text('["foo", "bar"]'::jsonb)
------返回结果
foo
bar
jsonb_array_length
可返回顶级 JSON 数组中元素的个数。
jsonb_array_length ( jsonb ) → integer
SELECT jsonb_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]');
------返回结果
5
jsonb_build_array
可使用可变参数列表构造 JSON 数组。每个参数都会按照 to_jsonb
进行转换。
jsonb_build_array ( VARIADIC "any" ) → jsonb
SELECT jsonb_build_array(1, 2, 'foo', 4, 5);
------返回结果
[1, 2, "foo", 4, 5]
jsonb_build_object
可根据可变参数列表构造 JSON 对象。按照惯例,该参数列表由交替 出现的键和值组成。键参数会被强制转换为文本;值参数则按照 to_jsonb
进行转换。
jsonb_build_object ( VARIADIC "any" ) → jsonb
SELECT jsonb_build_object('foo', 1, 2, row(3,'bar'));
------返回结果
{"2": {"f1": 3, "f2": "bar"}, "foo": 1}
jsonb_each
可将顶级 JSON 对象扩展为一组键值对。
jsonb_each ( jsonb ) → setof record ( key varchar, value jsonb )
SELECT * FROM jsonb_each('{"a":"foo", "b":"bar"}'::jsonb);
------返回结果
a "foo"
b "bar"
jsonb_each_text
可将顶级 JSON 对象扩展为一组键值对。返回值的类型为 varchar。
jsonb_each_text ( jsonb ) → setof record ( key varchar, value varchar )
SELECT * FROM jsonb_each_text('{"a":"foo", "b":"bar"}'::jsonb);
------返回结果
key | value
-----+-------
a | foo
b | bar
jsonb_extract_path
可提取指定路径下的 JSON 子对象。
该函数的功能等同于 #>
运算符。
jsonb_extract_path ( from_json jsonb, VARIADIC path_elems text[] ) → jsonb
from_json
是要从中提取子对象的输入 JSON 值。path_elems
是指定所需子对象在 JSON 结构中位置的路径元素。可以作为单独的参数提供多个路径元素。
SELECT json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}', 'f4', 'f6')
------返回结果
"foo"
jsonb_extract_path_text
可提取指定路径上的 JSON 子对象,并以文本形式返回。
该函数的功能等同于 #>>
运算符。
jsonb_extract_path_text ( from_json jsonb, VARIADIC path_elems text[] ) → text
SELECT jsonb_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"string"}}', 'f4', 'f6');
------返回结果
string
jsonb_object_keys
可返回顶级 JSON 对象中的键集。
jsonb_object_keys ( jsonb ) → setof varchar
SELECT * FROM jsonb_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}'::jsonb);
------返回结果
f1
f2
jsonb_strip_nulls
可从给定的 JSONB 值中递归删除所有具有空值的对象字段。空对象或不属于对象字段的空值不会被删除。
jsonb_strip_nulls ( jsonb ) → jsonb
-- 处理非空值
SELECT jsonb_strip_nulls('{"a": 1, "b": null, "c": {"d": null, "e": 2}}');
------返回结果
{"a": 1, "c": {"e": 2}}
-- 保留空对象
SELECT jsonb_strip_nulls('{"a": {"b": null, "c": null}, "d": {} }');
------返回结果
{"a": {}, "d": {}}
jsonb_path_exists
可检查 JSON 路径是否从 JSON 值返回任何项。
如果提供了 vars
参数,它必须是一个 JSON 对象。其字段会作为命名值被替换到 path
表达式中。如果指定了 silent
参数并将其设置为 true
,函数将抑制类似 @?
和 @
运算符的错误。
有关 SQL/JSON 路径句法的信息,请参阅 PostgreSQL 文档。
jsonb_path_exists ( target jsonb, path varchar [, vars jsonb [, silent boolean ]] ) → boolean
SELECT jsonb_path_exists('{"a":1, "b":2, "c":3}', '$.b');
------返回结果
t
jsonb_path_match
可检查指定 JSON 值的 JSON 路径 Predicate,并以布尔值返回结果。只考虑结果的第一项。
jsonb_path_match ( target jsonb, path varchar [, vars jsonb [, silent boolean ]] ) → boolean
SELECT jsonb_path_match('{"employee":{"name":"John","age":30}}', 'exists($.employee.age ? (@ > 25))');
------返回结果
t
jsonb_path_query
可从与 JSON 路径匹配的 JSON 值中提取项,并以集合形式返回结果。
jsonb_path_query ( target jsonb, path varchar [, vars jsonb [, silent boolean ]] ) → setof jsonb
SELECT jsonb_path_query('{
"employees": [
{
"name": "John",
"age": 30
},
{
"name": "Jane",
"age": 25
},
{
"name": "David",
"age": 35
},
{
"name": "Michael",
"age": 32
}
]
}', '$.employees[*] ? (@.age >= 25 && @.age <= 30)');
------返回结果
jsonb_path_query
------------------------------
{"age": 30, "name": "John"}
{"age": 25, "name": "Jane"}
jsonb_path_query_array
可提取匹配的 JSON 路径项,并以数组形式返回结果。
jsonb_path_query_array ( target jsonb, path varchar [, vars jsonb [, silent boolean ]] ) → jsonb
SELECT jsonb_path_query_array('{
"employees": [
{
"name": "John",
"age": 30
},
{
"name": "Alice",
"age": 35
},
{
"name": "Bob",
"age": 25
}
]
}', '$.employees[*] ? (@.age >= $min && @.age <= $max)', '{"min": 24, "max": 32}');
------返回结果
jsonb_path_query_array
-----------------------------------------------------------
[{"age": 30, "name": "John"}, {"age": 25, "name": "Bob"}]
jsonb_path_query_first
可使用 JSON 路径从 JSON 值中提取第一个匹配项。
jsonb_path_query_first ( target jsonb, path varchar [, vars jsonb [, silent boolean ]] ) → jsonb
SELECT jsonb_path_query_first('{
"employees": [
{
"name": "John",
"age": 30
},
{
"name": "Jane",
"age": 25
},
{
"name": "David",
"age": 35
}
]
}', '$.employees[0]');
------返回结果
jsonb_path_query_first
-----------------------------
{"age": 30, "name": "John"}
jsonb_typeof
可以文本字符串形式返回顶级 JSON 值的类型。
jsonb_typeof ( jsonb ) → varchar
SELECT jsonb_typeof ('-123.4');
------返回结果
number
jsonb_pretty
该函数可接收一个 jsonb
值,并返回该 JSON 值格式化且缩进的文本。
jsonb_pretty ( jsonb JSONB ) → TEXT
SELECT jsonb_pretty('[{"f1":1,"f2":null}, 2]');
------返回结果
[
{
"f1": 1,
"f2": null
},
2
]
jsonb_object
该函数可接收一个文本元素数组并返回一个 jsonb
对象,其中相邻的值对被视为对象属性的键和值。
jsonb_object ( text_array TEXT[] ) → JSONB
jsonb_object('{a, 1, b, def, c, 3.5}' :: text[]) → {"a": "1", "b": "def", "c": "3.5"}
jsonb_object(array['a', null]) → {"a": null}
to_jsonb
可将任何 SQL 值转换为 JSONB 数据类型。可将数组和组合递归转换为结果 JSON 表示中的数组和对象。如果可以从 SQL 数据类型直接转换为 JSON,则使用该转换。否则,将生成一个标量 json 值,并对文本表示进行适当转义,确保其成为有效的 JSON 字符串值。
to_jsonb ( any ) → JSONB
to_jsonb(array['apple', 'banana', 'cherry']) → ["apple", "banana", "cherry"]
to_jsonb('Products labeled "expired"'::string) → "Products labeled \"expired\""
JSON 运算符
jsonb -> integer → jsonb
可提取 JSON 数组中的第 n 个元素(数组元素从 0 开始索引,但负整数从末尾开始计数)。
SELECT'[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::jsonb -> 2;
------返回结果
{"c":"baz"}
jsonb -> varchar → jsonb
可用给定的键提取 JSON 对象字段。
SELECT '{"a": {"b":"foo"}}'::jsonb -> 'a';
------返回结果
{"b": "foo"}
jsonb ->> integer → varchar
可提取 JSON 数组中的第 n 个元素,并以文本形式返回。
SELECT '[1,2,3]'::jsonb ->> 2;
------返回结果
3
jsonb ->> varchar → varchar
可用给定的键提取 JSON 对象字段,并以文本形式返回。
SELECT '{"a":1,"b":2}'::jsonb ->> 'b';
------返回结果
2
jsonb - text → jsonb
可从 JSON 对象中删除键(及其值),或从 JSON 数组中删除匹配的字符串值。
'{"a": "b", "c": "d"}'::jsonb - 'a' → {"c": "d"}
'["a", "b", "c", "b"]'::jsonb - 'b' → ["a", "c"]
jsonb - text[] → jsonb
可删除 JSON 对象中所有匹配的键或数组元素。
'{"a": "b", "c": "d"}'::jsonb - '{a,c}'::text[] → {}
jsonb - integer → jsonb
可删除指定索引的数组元素(负整数从末尾计数)。如果 JSON 对象不是数组,则显示错误信息。
'["a", "b"]'::jsonb - 1 → ["a"]
jsonb #- text[] → jsonb
可删除指定路径上的字段或数组元素,路径元素可以是字段键或数组索引。
'["a", {"b":1}]'::jsonb #- '{1,b}' → ["a", {}]
(jsonb || jsonb) → jsonb
连接 jsonb 数据。
SELECT '["a", "b"]'::jsonb || '["a", "d"]'::jsonb;
SELECT '{"a": "b"}'::jsonb || '{"c": "d"}'::jsonb;
SELECT '[1, 2]'::jsonb || '3'::jsonb;
SELECT '{"a": "b"}'::jsonb || '42'::jsonb;
------返回结果
["a", "b", "a", "d"]
{"a": "b", "c": "d"}
[1, 2, 3]
[{"a": "b"}, 42]
jsonb @> jsonb → boolean
该运算符可检查左侧的 jsonb
值是否包含右侧的 jsonb
值。有关包含和存在的详细说明和示例,请参阅 PostgreSQL 文档中的 jsonb 包含和存在。
'[1, 2, 3]'::jsonb @> '[1, 3]'::jsonb → t
'{"product": "PostgreSQL", "version": 9.4, "jsonb": true}'::jsonb @> '{"version": 9.4}'::jsonb → t
'{"foo": {"bar": "baz"}}'::jsonb @> '{"bar": "baz"}'::jsonb → f
'{"foo": {"bar": "baz"}}'::jsonb @> '{"foo": {}}'::jsonb → t
jsonb <@ jsonb → boolean
该运算符可检查左侧的 jsonb
值是否包含在右侧的 jsonb
值中。有关包含和存在的详细说明和示例,请参阅 PostgreSQL 文档中的 jsonb 包含和存在。
'{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb → t
jsonb ? text → boolean
该运算符可检查字符串是否作为顶级数组元素或对象键存在于 jsonb
值中。
'["foo", "bar", "baz"]'::jsonb ? 'bar' → t
'{"foo": "bar"}'::jsonb ? 'foo' → t
'{"foo": "bar"}'::jsonb ? 'bar' → f
'{"foo": {"bar": "baz"}}'::jsonb ? 'bar' → f
'"foo"'::jsonb ? 'foo' → t
jsonb ?| text[] → boolean
该运算符可检查数组中的字符串是否作为顶级数组元素或对象键存在于 jsonb
值中。
'{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'd'] → t
'["a", "b", "c"]'::jsonb ?| array['b', 'd'] → t
'"b"'::jsonb ?| array['b', 'd'] → t
json ?& text[] → boolean
该运算符可检查数组中的所有字符串是否都作为顶级数组元素或对象键存在于 jsonb
值中。
'{"a":1, "b":2, "c":3}'::jsonb ?& array['a', 'b'] → t
'["a", "b", "c"]'::jsonb ?& array['a', 'b'] → t
'["a", "b", "c"]'::jsonb ?& array['a', 'd'] → f
jsonb #> text[] → jsonb
此运算符可使用键或索引的文本数组从 JSONB 对象中提取嵌套值。
'{"a": {"b": ["foo","bar"]}}'::jsonb #> '{a,b,1}'::text[] → "bar"
'{"a": {"b": ["foo","bar"]}}'::jsonb #> '{a,b,null}'::text[] → NULL
jsonb #>> text[] → text
该运算符可使用键或索引的文本数组从 JSONB 对象中提取嵌套值,并以文本形式返回。
'{"a": {"b": ["foo","bar"]}}'::jsonb #>> '{a,b,1}'::text[] → bar
'{"a": {"b": ["foo",null]}}'::jsonb #>> '{a,b,1}'::text[] → NULL
'{"a": {"b": ["foo","bar"]}}'::jsonb #>> '{a,b,null}'::text[] → NULL
jsonb @? varchar → boolean
可确定 JSON 路径是否为指定的 JSON 值返回了任何项。
SELECT '{"a":1, "b":2, "c":3}'::jsonb @? '$.a';
------返回结果
t
SELECT '{"a":1, "b":2, "c":3}'::jsonb @? '$.d';
------返回结果
f
jsonb @@ varchar → boolean
可返回对指定 JSON 值的 JSON 路径 Predicate 检查的结果。只考虑结果的第一项。如果结果不是布尔值,则返回 NULL。
SELECT '{"numbers":[1,2,3,4,5]}'::jsonb @@ '$.numbers[*] == 5';
------返回结果
t
IS JSON
Predicate
可测试表达式是否能被解析为 JSON,可以是指定类型的 JSON(可选)。它对 JSON 结构进行求值并返回一个布尔结果,表明该值是否与指定的 JSON 类型相匹配。
expression IS [ NOT ] JSON [ VALUE | ARRAY | OBJECT | SCALAR ] → bool
如果指定了 SCALAR、ARRAY 或 OBJECT,则测试 JSON 是否属于该特定类型。
SELECT js,
js IS JSON "json?",
js IS JSON SCALAR "scalar?",
js IS JSON OBJECT "object?",
js IS JSON ARRAY "array?"
FROM (VALUES
('123'), ('"abc"'), ('{"a": "b"}'), ('[1,2]'),('abc')) foo(js);
js | json? | scalar? | object? | array?
------------+-------+---------+---------+---------
123 | t | t | f | f
"abc" | t | t | f | f
{"a": "b"} | t | f | t | f
[1,2] | t | f | f | t
abc | f | f | f | f