PostgreSQL操作json/jsonb方法详解

前言

PostgreSQL 9.2 中引入了对JSON类型的支持，经过几个大版本的进化，目前对JSON数字类型的支持已经比较完善。在PG中对JSON格式信息的CRUD操作，针对具体的节点建立索引，这些都可以很容易的实现。本文以 PostgreSQL 15.3 版本作为演示，希望小伙伴们能有所收获。

两种数据类型json和jsonb

PG中提供了两种不同的数据类型，分别是JSON和JSONB。顾名思义，JSON是存储字符串的原始格式，而JSONB是二进制编码版本。JSON需要存储包括空格等原始格式，所以在每次查询的时候都会有解析过程。而JSONB查询时不需要实时解析，所以更高效。

简而言之，JSON 为了准确存储，插入快查询慢；JSONB 为了高效查询，插入慢检索快。

如果没有特殊理由，最好使用JSONB类型。

json和jsonb操作符

操作符右操作数类型描述例子例子结果->integer获得 JSON 数组的第n个元素（数组元素从0开始索引，负整数从末尾开始计算）‘[{“a”:“foo”},{“b”:“bar”},{“c”:“baz”}]’::json->2{“c”:“baz”}->text通过键获得 JSON 对象域‘{“a”: {“b”:“foo”}}’::json->‘a’{“b”:“foo”}->>integer以文本形式获得 JSON 数组的第n个元素‘[1,2,3]’::json->>23->>text以文本形式获得 JSON 对象域‘{“a”:1,“b”:2}’::json->>‘b’2#>text[]获取在指定路径的 JSON 对象，其中路径元素可以是字段键或数组索引‘{“a”: {“b”:{“c”: “foo”}}}’::json#>‘{a,b}’{“c”: “foo”}#>>text[]以文本形式获取在指定路径的 JSON 对象‘{“a”:[1,2,3],“b”:[4,5,6]}’::json#>>‘{a,2}’3

注意

对json和jsonb类型，这些操作符都有其并行变体。域/元素/路径抽取操作符返回与其左手输入（json或jsonb）相同的类型，不过那些被指定为返回text的，它们的返回值会被强制为文本。如果该 JSON 输入没有匹配请求的正确结构（例如那样的元素不存在），这些域/元素/路径抽取操作符会返回 NULL 而不是失败。接受整数JSON数组下标的域/元素/路径提取操作符支持数组末尾的负下标。

额外的jsonb操作符

操作符右操作数类型描述例子例子结果@>jsonb左边的 JSON 值是否包含顶层右边JSON路径/值项？‘{“a”:1, “b”:2}’::jsonb @> ‘{“b”:2}’::jsonbtrue<@jsonb左边的JSON路径/值是否包含在顶层右边JSON值中？‘{“b”:2}’::jsonb <@ ‘{“a”:1, “b”:2}’::jsonbtrue?text字符串是否作为顶层键值存在于JSON值中？‘{“a”:1, “b”:2}’::jsonb ? ‘b’true?|text[]这些数组字符串中的任何一个是否作为顶层键值存在？‘{“a”:1, “b”:2, “c”:3}’::jsonb ?| array[‘b’, ‘c’]true?&text[]这些数组字符串是否作为顶层键值存在？‘[“a”, “b”]’::jsonb ?& array[‘a’, ‘b’]true||jsonb连接两个jsonb值到新的jsonb值‘[“a”, “b”]’::jsonb || ‘[“c”, “d”]’::jsonb[“a”, “b”, “c”, “d”]-text从左操作数中删除键/值对或字符串元素。基于键值匹配键/值对。‘{“a”: “b”}’::jsonb – ‘a’{}-text[]从左操作数中删除多个键/值对或string元素。键/值对基于其键值进行匹配。‘{“a”: “b”, “c”: “d”}’::jsonb – ‘{a,c}’::text[]{}-integer删除指定索引的数组元素（负整数结尾）。如果顶层容器不是一个数组，那么抛出错误。‘[“a”, “b”]’::jsonb – 1[“a”]#-text[]删除指定路径的域或元素（JSON数组，负整数结尾）‘[“a”, {“b”:1}]’::jsonb #- ‘{1,b}’[“a”, {}]

注意

操作符连接每个操作数顶层的元素。它不递归操作。比如，如果两个操作数都是具有共同键字段名称的对象，则结果中字段值只会是从右边操作数的值。

JSON/JSONB 创建函数

函数描述例子例子结果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,‘foo’)){“f1”:1,“f2”:“foo”}json_build_array(VARIADIC “any”)jsonb_build_array(VARIADIC “any”)从一个可变参数列表构造一个可能包含异质类型的 JSON 数组。json_build_array(1,2,‘3’,4,5)[1, 2, “3”, 4, 5]json_build_object(VARIADIC “any”)jsonb_build_object(VARIADIC “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”}

注意

array_to_json和row_to_json与to_json具有相同的行为，不过它们提供了更好的打印选项。针对to_json所描述的行为同样也适用于由其他 JSON 创建函数转换的每个值。

JSON/JSONB 处理函数

函数返回值描述例子例子结果json_array_length(json)jsonb_array_length(jsonb)int返回最外层 JSON 数组中的元素数量。json_array_length(‘[1,2,3,{“f1”:1,“f2”:[5,6]},4]’)5json_each(json)jsonb_each(jsonb)setof key text, value jsonsetof 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(jsonb)setof key text, value text扩展最外层的 JSON 对象成为一组键/值对。返回值将是文本类型。select * from json_each_text(‘{“a”:“foo”, “b”:“bar”}’)key | value—–±—— a | foo b | barjson_extract_path(from_json json, VARIADIC path_elems text[])jsonb_extract_path(from_json jsonb, VARIADIC path_elems text[])jsonjsonb返回由path_elems指向的 JSON 值（等效于#>操作符）。json_extract_path(‘{“f2”:{“f3”:1},“f4”:{“f5”:99,“f6”:“foo”}}’,‘f4’){“f5”:99,“f6”:“foo”}json_extract_path_text(from_json json, VARIADIC path_elems text[])jsonb_extract_path_text(from_json jsonb, VARIADIC path_elems text[])text以文本返回由path_elems指向的 JSON 值（等效于 #>> 操作符）。json_extract_path_text(‘{“f2”:{“f3”:1},“f4”:{“f5”:99,“f6”:“foo”}}’,‘f4’, ‘f6’)foojson_object_keys(json)jsonb_object_keys(jsonb)setof text返回最外层 JSON 对象中的键集合。json_object_keys(‘{“f1”:“abc”,“f2”:{“f3”:“a”, “f4”:“b”}}’)json_object_keys——————f1f2json_populate_record(base anyelement, from_json json)jsonb_populate_record(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_recordset(base anyelement, from_json json)jsonb_populate_recordset(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 | 4json_array_elements(json)jsonb_array_elements(jsonb)setof jsonsetof jsonb把一个 JSON 数组扩展成一个 JSON 值的集合。select * from json_array_elements(‘[1,true, [2,false]]’)value———–1true[2,false]json_array_elements_text(json)jsonb_array_elements_text(jsonb)setof text把一个 JSON 数组扩展成一个text值集合。select * from json_array_elements_text(‘[“foo”, “bar”]’)value———–foobarjson_typeof(json)jsonb_typeof(jsonb)text把最外层的 JSON 值的类型作为一个文本字符串返回。可能的类型是： object、array、string、number、 boolean以及null。json_typeof(‘-123.4’)numberjson_to_record(json)jsonb_to_record(jsonb)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(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);a | b—±—-1 | foo2 |json_strip_nulls(from_json json)jsonb_strip_nulls(from_json jsonb)jsonjsonb返回具有空值对象域的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_missing boolean])jsonb如果create_missing是真的（缺省是true）并且通过path 指定部分不存在，那么返回target，它具有path指定部分， new_value替换部分，或者new_value添加部分。正如路径导向的操作符，负整数出现在JSON数组结尾的path>计数中。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 boolean])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_json jsonb)text作为缩进JSON文本返回from_json。jsonb_pretty(‘[{“f1”:1,“f2”:null},2,null,3]’)[{“f1”: 1,“f2”: null},2,null,3]jsonb_concat(jsonb, jsonb)jsonb连接两个jsonb值到新的jsonb值。select jsonb_concat(‘{“a”: “b”,“test”:“v”}’::jsonb,‘{“test”:[{“name”: “r”}]}’::jsonb);{“a”: “b”, “test”: [{“name”: “r”}]}jsonb_contains(jsonb, jsonb)boolean判断第1个jsonb是否包含第2个jsonb（等效于 || 操作符）。select jsonb_contains(‘{“a”: “b”,“test”: [{“name”: “v”}]}’::jsonb,‘{“test”:[{“name”: “v”}]}’::jsonb);true

注意

在json_populate_record、json_populate_recordset、json_to_record和json_to_recordset中，来自 JSON 的类型强制是“尽力而为”并且对于某些类型可能得不到想要的值。JSON 键会被匹配目标行类型中相同的列名。没有出现在目标行类型中的 JSON 域将会被从输出中忽略，并且不匹配任何 JSON 域的目标列将被简单地作为 NULL。

所有jsonb_set的path参数项必须存在于target中，除非create_missing是真的，在这种情况下，除了最后一项都存在。如果这些条件未满足target返回不变。

如果最后路径项是对象键，如果它不存在并且给予新值，则被创建。如果最后路径项是一个数组索引，如果通过从左边计数发现设置项是正数，如果从右边 -1计数负数指定右边的元素，等等。如果项超出-array_length … array_length -1范围，并且create_missing是真，如果项是负数，则在数组开头添加新值，如果是正数，则添加到数组末尾。

不要把json_typeof函数的null返回值与 SQL 的 NULL 弄混。虽然调用json_typeof(‘null’::json)将会返回null，但调用 json_typeof(NULL::json)将会返回一个 SQL 的 NULL。

如果参数到json_strip_nulls包含任何对象的复制字段名，其结果可能是语义上不同，依赖于它们所产生的顺序。这不是jsonb_strip_nulls的问题，因为jsonb值从不复制对象字段名。