Skip to main content

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
Last updated on
RisingWave Labs
Github/RisingWave
RisingWave 中文开源社区