官网地址:https://www.postgresql.org/docs/12/functions-json.html
# 基础操作
# json和jsonb运算符
# ->右操作数类型int,返回值为json or jsonb格式,获取JSON数组元素(从零开始索引,从末数开始为负整数
SELECT '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2;
# ->右操作数类型text,返回值为json or jsonb格式,通过键获取JSON对象字段
SELECT '{"a": {"b":"foo"}}'::json->'a';
# ->>右操作数类型int,返回值为text格式,获取JSON数组元素为 text
SELECT '[1,2,3]'::json->>2;
# ->>右操作数类型text,返回值为text格式,获取JSON数组元素为 text
SELECT '{"a":1,"b":2}'::json->>'b';
# #>右操作数类型text[],返回值为json or jsonb格式,在指定路径获取JSON对象
SELECT '{"a": {"b":{"c": "foo"}}}'::json#>'{a,b}';
# #>>右操作数类型text[],返回值为text格式,在指定路径下获取JSON对象为 text
SELECT '{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}';
# 附加jsonb操作
# @> 右操作数类型为jsonb格式,左侧的JSON值是否在顶层包含正确的JSON路径/值条目
SELECT '{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb;
# <@ 返回值为jsonb格式,左侧JSON路径/值条目是否包含在右侧JSON值的顶层
SELECT '{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb;
# ?右操作数类型为text格式,字符串是否作为JSON值中的顶级键存在
SELECT '{"a":1, "b":2}'::jsonb ? 'b';
# ?|右操作数类型为text[]格式,所有这些数组字符串都作为顶级键存在吗
SELECT '{"a":1, "b":2, "c":{"d": "foo"}}'::jsonb ?| array['c', 'D'];
# ?&返回值为text[]格式,数组元素全部都存在左边的数组中
SELECT '["a", "b", {"c":"d"}]'::jsonb ?& array['a', 'c'];
# ||右操作数类型为jsonb格式,将两个jsonb值连接成一个新jsonb值
SELECT '["a", "b"]'::jsonb || '["c", "d"]'::jsonb;
# -右操作数类型为text格式,从左操作数中删除键/值对或字符串元素。键/值对基于其键值进行匹配。
SELECT '{"a": "b"}'::jsonb - 'a';
# -右操作数类型为text[]格式,从左操作数中删除多个键/值对或字符串元素。键/值对基于其键值进行匹配。
SELECT '{"a": "b", "c": "d"}'::jsonb - '{a,c}'::text[];
# - 右操作数类型integer,删除具有指定索引的数组元素(末尾为负整数)。如果顶级容器不是数组,则会引发错误。
SELECT '["a", "b"]'::jsonb - 1;
# #- 右操作数类型为text[]格式,删除具有指定路径的字段或元素(对于JSON数组,从末尾算起负整数)
SELECT '["a", {"b":1}]'::jsonb #- '{1,b}';
# @? 右操作数类型为jsonpath JSON路径是否为指定的JSON值返回任何项目?
SELECT '{"a":[1,2,3,4,5]}'::jsonb @? '$.a[*] ? (@ > 2)';
# @@ 右操作数类型jsonpath 返回指定JSON值的JSON路径谓词检查的结果。仅考虑结果的第一项。如果结果不是布尔值,则null返回。
SELECT '{"a":[1,2,3,4,5]}'::jsonb @@ '$.a[*] > 2';
# json创建函数
# 函数to_json(anyelement)|to_jsonb(anyelement)
# 返回值为json或jsonb。
# 数组和组合被(递归)转换为数组和对象。否则,如果存在从类型到json的转换,则将使用转换功能执行转换;否则,将使用转换功能执行转换。
# 否则,产生标量值。对于除数字,布尔值或空值以外的任何标量类型,将使用文本表示形式,即它是有效值json还是jsonb值。
# "Fred said \"Hi.\""
SELECT to_json('Fred said "Hi."'::text);
# 函数array_to_json(anyarray [, pretty_bool])
# 将数组作为JSON数组返回。
# PostgreSQL多维数组变成数组的JSON数组。如果pretty_bool为true,则将在维度1元素之间添加换行符。
# [[1,5],[99,100]]
SELECT array_to_json('{{1,5},{99,100}}'::int[]);
# 函数row_to_json(record [, pretty_bool]) 以JSON对象的形式返回该行。如果pretty_bool为true,则将在1级元素之间添加换行符。
# {"f1":1,"f2":"foo"}
SELECT row_to_json(row(1,'foo'));
# 函数json_build_array(VARIADIC "any"),jsonb_build_array(VARIADIC "any")
# 根据可变参数列表构建一个可能是异构类型的JSON数组。
# [1, 2, "3", 4, 5]
SELECT json_build_array(1,2,'3',4,5);
# 函数json_build_object(VARIADIC "any"),jsonb_build_object(VARIADIC "any")
# 从可变参数列表中构建JSON对象。按照惯例,参数列表由交替的键和值组成。
# {"foo": 1, "bar": 2}
SELECT json_build_object('foo',1,'bar',2);
# 函数json_object(text[]),jsonb_object(text[])
# 从文本数组构建JSON对象。
# 数组必须具有一个具有偶数个成员的正好一维(在这种情况下,它们被视为交替的键/值对),
# 或者必须具有两个维,以使每个内部数组都具有两个正好作为键/值对的元素。
# {"a": "1", "b": "def", "c": "3.5"}
SELECT json_object('{a, 1, b, "def", c, 3.5}');
SELECT json_object('{{a, 1},{b, "def"},{c, 3.5}}');
# 函数json_object(keys text[], values text[]),jsonb_object(keys text[], values text[])
# 这种形式的json_object键和值成对地来自两个单独的数组。在所有其他方面,它与单参数形式相同。
# {"a": "1", "b": "2"}
SELECT json_object('{a, b}', '{1,2}');
# json处理函数
# 函数json_array_length(json),jsonb_array_length(jsonb)
# 返回类型int
# 返回最外面的JSON数组中的元素数。
SELECT json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]');
# 函数json_each(json),jsonb_each(jsonb)
# 返回值类型setof key text, value json | jsonb
# 将最外面的JSON对象扩展为一组键/值对。
SELECT * FROM json_each('{"a":"foo", "b":"bar"}');
# 函数json_each_text(json),jsonb_each_text(jsonb)
# 返回类型setof key text, value text
# 将最外面的JSON对象扩展为一组键/值对。返回的值将是类型text。
SELECT * FROM json_each_text('{"a":"foo", "b":"bar"}');
# 函数json_extract_path(from_json json, VARIADIC path_elems text[]),jsonb_extract_path(from_json jsonb, VARIADIC path_elems text[])
# 返回类型json,jsonb
# 返回指向的JSON值path_elems(等效于#>运算符)。
SELECT json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4');
# 函数json_extract_path_text(from_json json, VARIADIC path_elems text[]),jsonb_extract_path_text(from_json jsonb, VARIADIC path_elems text[])
# 返回类型text
# 返回path_elemsas所指向的JSON值text(等效于#>>运算符)。
SELECT json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4', 'f6');
# 函数json_object_keys(json),jsonb_object_keys(jsonb)
# 返回类型setof text
# 返回最外层JSON对象中的键集。
SELECT json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}');
# 函数json_populate_record(base anyelement, from_json json),jsonb_populate_record(base anyelement, from_json jsonb)
# 返回类型anyelement
# 将对象扩展from_json到其列与定义的记录类型相匹配的行
DROP TYPE jsonb_test;
CREATE TYPE jsonb_test AS (a INT, b VARCHAR[], c jsonb);
SELECT * FROM json_populate_record(null::jsonb_test, '{"a": 1, "b": ["2", "a b"], "c": {"d": 4, "e": "a b c"}}');
# 函数json_populate_recordset(base anyelement, from_json json),jsonb_populate_recordset(base anyelement, from_json jsonb)
# 返回类型setof anyelement
# 将对象的最外面的数组扩展from_json为一组行,这些行的列与定义的记录类型相匹配
DROP TYPE jsonb_test1;
CREATE TYPE jsonb_test1 AS (a INT, b INT);
SELECT * FROM json_populate_recordset(null::jsonb_test1, '[{"a":1,"b":2},{"a":3,"b":4}]');
# 函数json_array_elements(json),jsonb_array_elements(jsonb)
# 返回类型setof json | jsonb
# 将JSON数组扩展为一组JSON值。
SELECT * FROM json_array_elements('[1,true, [2,false]]');
# 函数json_array_elements_text(json),jsonb_array_elements_text(jsonb)
# 返回类型setof text
# 将JSON数组扩展为一组text值。
SELECT * FROM json_array_elements_text('["foo", "bar"]');
# 函数json_typeof(json),jsonb_typeof(jsonb)
# 返回类型text
# 以文字字串传回最外层JSON值的类型。可能的类型有object,array,string,number,boolean,和null。
SELECT json_typeof('-123.4');
# 函数json_to_record(json,jsonb_to_record(jsonb)
# 返回类型record
# 从JSON对象构建任意记录。与所有返回函数一样record,调用者必须使用AS子句显式定义记录的结构。
DROP TYPE jsonb_test2;
CREATE TYPE jsonb_test2 AS (a INT, b jsonb);
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 jsonb_test2);
# 函数json_to_recordset(json),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);
# 函数json_strip_nulls(from_json json),jsonb_strip_nulls(from_json jsonb)
# 返回类型json,jsonb
# 返回from_json所有具有空值的对象字段。其他空值保持不变。
SELECT json_strip_nulls('[{"f1":1,"f2":null},2,null,3]');
# 函数jsonb_set(target jsonb, path text[], new_value jsonb [, create_missing boolean])
# 返回类型jsonb
# 返回target,用指定的部分path替换为new_value,new_value如果create_missing是true(默认为true)且添加的项path不存在,则添加。
# 与面向路径的运算符一样,path从JSON数组末尾开始计数的负整数。
SELECT jsonb_set('[{"f1":1,"f2":null},2,null,3]', '{0,f1}','[2,3,4]', false);
SELECT jsonb_set('[{"f1":1,"f2":null},2]', '{0,f3}','[2,3,4]');
# 函数jsonb_insert(target jsonb, path text[], new_value jsonb [, insert_after boolean])
# 返回类型jsonb
# 返回插入了new_value的target。 如果path指定的目标节点在JSONB数组中,
# insert_after为false时(默认值为false),则new_value将在target之前插入,
# insert_after为true时,则new_value将在target之后插入。
# 如果path指定的目标节点位于JSONB对象中,则只有目标不存在时才插入new_value。
# 与面向路径的运算符一样,出现在路径中的负整数从JSON数组的末尾开始计数。
# {"a": [0, "new_value", 1, 2]}
SELECT jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"');
# {"a": [0, 1, "new_value", 2]}
SELECT jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"', true);
# 函数jsonb_pretty(from_json jsonb)
# 返回类型text
# 返回from_json缩进的JSON文本。
SELECT jsonb_pretty('[{"f1":1,"f2":null},2,null,3]');
# 函数jsonb_path_exists(target jsonb, path jsonpath [, vars jsonb [, silent bool]])
# 返回类型boolean
# 检查JSON路径是否返回指定JSON值的任何项目。
SELECT jsonb_path_exists('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2,"max":4}');
# 函数jsonb_path_match(target jsonb, path jsonpath [, vars jsonb [, silent bool]])
# 返回类型boolean
# 返回指定JSON值的JSON路径谓词检查的结果。仅考虑结果的第一项。如果结果不是布尔值,则null返回。
SELECT jsonb_path_match('{"a":[1,2,3,4,5]}', 'exists($.a[*] ? (@ >= $min && @ <= $max))', '{"min":2,"max":4}');
# 函数jsonb_path_query(target jsonb, path jsonpath [, vars jsonb [, silent bool]])
# 返回类型setof jsonb
# 获取JSON路径返回的指定JSON值的所有JSON项。
SELECT * FROM jsonb_path_query('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2,"max":4}');
# 函数jsonb_path_query_array(target jsonb, path jsonpath [, vars jsonb [, silent bool]])
# 返回类型jsonb
# 获取JSON路径返回的,用于指定JSON值的所有JSON项,并将结果包装到数组中。
SELECT jsonb_path_query_array('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2,"max":4}');
# 函数jsonb_path_query_first(target jsonb, path jsonpath [, vars jsonb [, silent bool]])
# 返回类型jsonb
# 获取JSON路径返回的指定JSON值的第一个JSON项。没有结果返回NULL。
SELECT jsonb_path_query_first('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2,"max":4}');
# 表操作
DROP TABLE test;
CREATE TABLE test(id serial, data jsonb);
INSERT INTO test(data) values ('{"name": "my-name", "tags": ["tag1", "tag2"]}');
SELECT * FROM test;
# 错误: 语法错误 在 "->" 或附近的
UPDATE test SET data->'name' = 'my-other-name' WHERE id = 1;
# 更新名称
UPDATE test SET data = jsonb_set(data, '{name}', '"my-other-name"');
# 替换标签(与添加或删除标签相反)
UPDATE test SET data = jsonb_set(data, '{tags}', '["tag3", "tag4"]');
# 替换第二个标记(0索引)
UPDATE test SET data = jsonb_set(data, '{tags,1}', '"tag5"');
# 附加一个标签
UPDATE test SET data = jsonb_set(data, '{tags,100}', '"tag6"', true);
# 删除最后一个标记
UPDATE test SET data = data #- '{tags,-1}';
# 复杂更新(删除最后一个标记,插入新标记并更改名称)
UPDATE test SET data = jsonb_set(
jsonb_set(data #- '{tags,-1}', '{tags,100}', '"tag4"', true),
'{name}', '"my-other-name1"');
CREATE TABLE test1(id serial, data jsonb);
INSERT INTO test1(data) values ('{"name": "my-name", "tags": ["tag1", "tag2"]}');
SELECT * FROM test1;
UPDATE test1
SET data = replace(data::TEXT,'"name":','"my-other-name":')::jsonb
WHERE id = 1;
# 更新'name'属性
UPDATE test1 SET data=data||'{"name":"my-other-name"}' WHERE id = 1;
# 删除例如'name'和'tags'属性
UPDATE test1 SET data=data-'{"name","tags"}'::text[] WHERE id = 1;