PostgreSql数据库关于json相关函数
前言:PostgreSql数据库对json类型也是做了很多处理,今天我们来了解下PostgreSql数据库的json及jsonb的使用及函数
json和jsonb的区别
json
类型
- 存储方式:
json
类型存储 JSON 数据为纯文本格式。 - 操作: 对
json
数据的操作会直接在文本格式上进行处理,这可能会导致一些性能开销,因为每次操作都需要解析 JSON 文本。 - 功能: 提供的函数和操作符主要是基于文本的操作。
jsonb
类型
- 存储方式:
jsonb
类型存储 JSON 数据为二进制格式。这种格式经过优化,适合于高效的查询和索引。 - 操作: 对
jsonb
数据的操作是直接在二进制格式上进行的,这通常比json
类型的文本操作更快。 - 功能: 提供了更多的函数和操作符,支持更高效的操作和索引。
主要区别
- 存储格式:
json
: 存储为原始的 JSON 文本格式。jsonb
: 存储为优化后的二进制格式。
- 性能:
json
: 操作时需要进行额外的解析和处理,可能影响性能。jsonb
: 由于使用了二进制格式,支持索引,通常操作性能更好。
- 索引支持:
json
: 不支持索引,所有操作都需要扫描数据。jsonb
: 支持 GIN 和 BTREE 索引,能显著提高查询性能。
- 函数和操作符:
json
: 支持基本的 JSON 处理函数和操作符,更多依赖于文本处理。jsonb
: 提供了更丰富的函数和操作符,支持更复杂的查询和操作。
操作符
->
:提取 JSON 对象字段或数组元素(返回 JSON 类型)。
SELECT '{"a": {"b": 1}}'::json->'a'; -- {"b": 1}
->>
:提取 JSON 对象字段或数组元素的文本值(返回 TEXT 类型)。
SELECT '{"a": {"b": 1}}'::json->>'a'; -- '{"b": 1}'
#>
:提取指定路径的 JSON 值(返回 JSON 类型)。
SELECT '{"a": {"b": 1}}'::json#>'{a,b}'; -- 1
#>>
:提取指定路径的 JSON 值的文本值(返回 TEXT 类型)。
SELECT '{"a": {"b": 1}}'::json#>>'{a,b}'; -- '1'
@>
:左操作数包含右操作数(包含)。
SELECT '{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb; -- true
<@
:右操作数包含左操作数(被包含)。
SELECT '{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb; -- true
?
:检查 JSON 对象是否包含指定键(返回布尔值)。
SELECT '{"a":1, "b":2}'::jsonb ? 'b'; -- true
?|
:检查 JSON 对象是否包含任意一个指定键(返回布尔值)。
SELECT '{"a":1, "b":2}'::jsonb ?| array['b', 'c']; -- true
?&
:检查 JSON 对象是否包含所有指定键(返回布尔值)。
SELECT '{"a":1, "b":2}'::jsonb ?& array['a', 'b']; -- true
||
:连接两个 JSON 对象(合并)。
SELECT '{"a":1}'::jsonb || '{"b":2}'::jsonb; -- {"a": 1, "b": 2}
-
:从 JSON 对象中删除指定键。
SELECT '{"a":1, "b":2}'::jsonb - 'a'; -- {"b": 2}
#-
:从 JSON 对象中删除指定路径。
SELECT '["a", {"b":1}]'::jsonb #- '{1,b}'; -- ["a", {}]
JSONB
常用函数(操作JSON也是一样的,JSONB
换成JSON
即可)
-
jsonb_build_object
: 创建一个 JSONB 对象。SELECT jsonb_build_object('name', 'John', 'age', 30);
-
jsonb_build_array
: 创建一个 JSONB 数组。SELECT jsonb_build_array(1, 'a', NULL, TRUE);
-
jsonb_agg
: 将查询结果聚合为 JSONB 数组。SELECT jsonb_agg(name) FROM employees;
-
jsonb_each
: 将 JSONB 对象的键值对转换为表格。SELECT * FROM jsonb_each('{"a": 1, "b": 2}'::jsonb);
-
jsonb_each_text
: 将 JSONB 对象的键值对转换为文本格式的表格。SELECT * FROM jsonb_each_text('{"a": "foo", "b": "bar"}'::jsonb);
-
jsonb_object_keys
: 返回 JSONB 对象中所有键的集合。SELECT jsonb_object_keys('{"a": 1, "b": 2}'::jsonb);
-
jsonb_extract_path
: 从 JSONB 对象中提取指定路径的值。SELECT jsonb_extract_path('{"a": {"b": 1}}'::jsonb, 'a', 'b');
-
jsonb_extract_path_text
: 从 JSONB 对象中提取指定路径的文本值。SELECT jsonb_extract_path_text('{"a": {"b": "value"}}'::jsonb, 'a', 'b');
-
jsonb_set
: 更新 JSONB 对象中的值。SELECT jsonb_set('{"a": 1}'::jsonb, '{a}', '2'::jsonb);
-
jsonb_strip_nulls
: 移除 JSONB 对象中的NULL
值。SELECT jsonb_strip_nulls('{"a": 1, "b": null}'::jsonb);