PostgreSql数据库关于jsonb(json)类型相关函数

PostgreSql数据库关于json相关函数

前言：PostgreSql数据库对json类型也是做了很多处理，今天我们来了解下PostgreSql数据库的json及jsonb的使用及函数

json和jsonb的区别

json 类型

• 存储方式: json 类型存储 JSON 数据为纯文本格式。
• 操作: 对 json 数据的操作会直接在文本格式上进行处理，这可能会导致一些性能开销，因为每次操作都需要解析 JSON 文本。
• 功能: 提供的函数和操作符主要是基于文本的操作。

jsonb 类型

• 存储方式: jsonb 类型存储 JSON 数据为二进制格式。这种格式经过优化，适合于高效的查询和索引。
• 操作: 对 jsonb 数据的操作是直接在二进制格式上进行的，这通常比 json 类型的文本操作更快。
• 功能: 提供了更多的函数和操作符，支持更高效的操作和索引。

主要区别

1. 存储格式:
   • json: 存储为原始的 JSON 文本格式。
   • jsonb: 存储为优化后的二进制格式。
2. 性能:
   • json: 操作时需要进行额外的解析和处理，可能影响性能。
   • jsonb: 由于使用了二进制格式，支持索引，通常操作性能更好。
3. 索引支持:
   • json: 不支持索引，所有操作都需要扫描数据。
   • jsonb: 支持 GIN 和 BTREE 索引，能显著提高查询性能。
4. 函数和操作符:
   • 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即可）

1. jsonb_build_object: 创建一个 JSONB 对象。

   SELECT jsonb_build_object('name', 'John', 'age', 30);
   

2. jsonb_build_array: 创建一个 JSONB 数组。

   SELECT jsonb_build_array(1, 'a', NULL, TRUE);
   

3. jsonb_agg: 将查询结果聚合为 JSONB 数组。

   SELECT jsonb_agg(name) FROM employees;
   

4. jsonb_each: 将 JSONB 对象的键值对转换为表格。

   SELECT * FROM jsonb_each('{"a": 1, "b": 2}'::jsonb);
   

5. jsonb_each_text: 将 JSONB 对象的键值对转换为文本格式的表格。

   SELECT * FROM jsonb_each_text('{"a": "foo", "b": "bar"}'::jsonb);
   

6. jsonb_object_keys: 返回 JSONB 对象中所有键的集合。

   SELECT jsonb_object_keys('{"a": 1, "b": 2}'::jsonb);
   

7. jsonb_extract_path: 从 JSONB 对象中提取指定路径的值。

   SELECT jsonb_extract_path('{"a": {"b": 1}}'::jsonb, 'a', 'b');
   

8. jsonb_extract_path_text: 从 JSONB 对象中提取指定路径的文本值。

   SELECT jsonb_extract_path_text('{"a": {"b": "value"}}'::jsonb, 'a', 'b');
   

9. jsonb_set: 更新 JSONB 对象中的值。

   SELECT jsonb_set('{"a": 1}'::jsonb, '{a}', '2'::jsonb);
   

10. jsonb_strip_nulls: 移除 JSONB 对象中的 NULL 值。

    SELECT jsonb_strip_nulls('{"a": 1, "b": null}'::jsonb);