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 查询功能强大且灵活，适合各种复杂的数据处理场景。结合索引优化，性能可以进一步提升。
