首页 前端知识 PostgreSQL JSON/JSONB 查询与操作指南

PostgreSQL JSON/JSONB 查询与操作指南

2025-03-04 11:03:35 前端知识 前端哥 929 49 我要收藏

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


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插件-强制开启复制-护眼模式-网页乱码设置编码,让您的开发和运营工作更轻松高效。

转载请注明出处或者链接地址:https://www.qianduange.cn//article/22614.html
标签
postgresql
评论
发布的文章

图论-腐烂的橘子

2025-03-04 11:03:06

大家推荐的文章
会员中心 联系我 留言建议 回顶部
复制成功!