官网地址：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;
