PostgreSQL JSON/JSONB 查询与操作指南

PostgreSQL 提供了强大的 JSON 和 JSONB 数据类型及相关操作，适用于存储和查询半结构化数据。本文将详细介绍其常用操作。

1. 基础操作

1.1 JSON 属性访问

• ->: 返回 JSON 对象中的值，结果为 JSON 格式。

SELECT '{"a": {"b": 1}}'::jsonb -> 'a';
-- 返回：{"b": 1}复制

• ->>: 返回 JSON 对象中的值，结果为文本。

SELECT '{"a": {"b": 1}}'::jsonb -> 'a' ->> 'b';
-- 返回："1" (文本)复制

1.2 JSON 数组访问

• 索引访问数组元素：

SELECT '[1, 2, 3]'::jsonb -> 1;
-- 返回：2 (JSON 格式)复制

• 获取数组中某元素的文本：

SELECT '[1, 2, 3]'::jsonb ->> 1;
-- 返回："2" (文本)复制

2. 高级操作

2.1 查询嵌套 JSON 的值

• 使用 #> 获取嵌套对象：

SELECT '{"a": {"b": {"c": 3}}}'::jsonb #> '{a,b}';
-- 返回：{"c": 3}复制

• 使用 #>> 获取嵌套对象的文本值：

SELECT '{"a": {"b": {"c": 3}}}'::jsonb #>> '{a,b,c}';
-- 返回："3" (文本)复制

2.2 条件查询

通过字段筛选数据

SELECT *
FROM example_table
WHERE jsonb_column ->> 'key' = 'value';复制

判断是否包含特定键值对

SELECT *
FROM example_table
WHERE jsonb_column @> '{"key": "value"}';复制

判断是否包含特定键

SELECT *
FROM example_table
WHERE jsonb_column ? 'key';复制

判断是否包含多个键

• 任意一个键：

SELECT *
FROM example_table
WHERE jsonb_column ?| array['key1', 'key2'];复制

• 所有键：

SELECT *
FROM example_table
WHERE jsonb_column ?& array['key1', 'key2'];复制

2.3 数组操作

判断数组是否包含元素

SELECT *
FROM example_table
WHERE jsonb_column @> '[1, 2]'; -- JSON 数组包含 [1, 2]复制

判断数组是否重叠

SELECT *
FROM example_table
WHERE jsonb_column ?| array['key1', 'key2'];复制

3. 修改 JSON 数据

3.1 添加键值

UPDATE example_table
SET jsonb_column = jsonb_column || '{"new_key": "new_value"}';复制

3.2 删除键

• 删除单个键：

UPDATE example_table
SET jsonb_column = jsonb_column - 'key_to_remove';复制

• 删除多个键：

UPDATE example_table
SET jsonb_column = jsonb_column - '{key1, key2}';复制

3.3 替换嵌套值

• 使用 jsonb_set 替换嵌套值：

UPDATE example_table
SET jsonb_column = jsonb_set(jsonb_column, '{nested,key}', '"new_value"');复制

4. 聚合操作

4.1 提取 JSON 中的字段值

SELECT jsonb_column ->> 'key', COUNT(*)
FROM example_table
GROUP BY jsonb_column ->> 'key';复制

4.2 将多个 JSON 合并

SELECT jsonb_agg(jsonb_column)
FROM example_table;复制

4.3 展开 JSON 数组

SELECT jsonb_array_elements(jsonb_column)
FROM example_table;复制

5. 索引优化

5.1 创建 JSONB 索引

创建 GIN 索引

CREATE INDEX idx_jsonb_column ON example_table USING gin (jsonb_column);复制

使用 JSONB 索引进行快速查询

SELECT *
FROM example_table
WHERE jsonb_column @> '{"key": "value"}';复制

创建键路径索引

CREATE INDEX idx_jsonb_key ON example_table USING gin ((jsonb_column -> 'key'));复制

PostgreSQL 的 JSONB 查询功能强大且灵活，适合各种复杂的数据处理场景。结合索引优化，性能可以进一步提升。

📌 开发者必备工具： 在 Tool.tushuoit.com 发现免费在线工具集！推荐 App Store 截图生成器、应用图标生成器 和 Chrome插件-强制开启复制-护眼模式-网页乱码设置编码，让您的开发和运营工作更轻松高效。