PostgreSQL JSON/JSONB 查询与操作指南
PostgreSQL JSON/JSONB 查询与操作指南
井九
发布于 2024-12-21 20:07:57
发布于 2024-12-21 20:07:57
代码可运行
运行总次数：0
代码可运行
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 查询功能强大且灵活，适合各种复杂的数据处理场景。结合索引优化，性能可以进一步提升。
本文参与 腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。
原始发表：2024-12-16，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
目录
腾讯云开发者
