PostgreSQL查询JSON数据

树袋熊不是熊

已于 2022-11-01 09:05:05 修改

阅读量3.2k

点赞数

CC 4.0 BY-SA版权

文章标签： postgresql json 数据库

于 2022-11-01 09:03:18 首次发布

本文链接：https://blog.csdn.net/m0_72607132/article/details/127627133

本文介绍了如何在PostgreSQL中查询和更新JSONB数据。内容包括使用JSON作为查询字段和条件，修改与增加JSON数据，多表关联更新，以及处理JSON数组。还提及了扩展功能如查看数据库表信息和生成随机数。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

JSON示例数据

content = {
    "id": "123456789",
    "name": "测试",
    "list": [
        {
            "origin": "导入1",
            "version": 1
        },
        {
            "origin": "导入2",
            "version": 2
        }
    ]
}

注释

->： 返回JSON格式数据
->>：返回文本格式数据
json_array_elements：拆分数组中的JSON数据
::json：将数据结果转化为JSON格式
::json#>>：将数据结果指向子级

1.JSONB查询

1）JSON作为查询字段：

SELECT content->>'id' as id,content->>'name' as name,json_array_elements((content::json#>>'{list}')::json)->>'origin' as origin,json_array_elements((content->>'list')::json)->>'version' as version FROM tablename;

2）JSON作为查询条件：

SELECT * FROM tablename WHERE (content::json#>>'{name}')::text='测试';

2.JSONB更新

1）JSON修改

UPDATE tablename SET content = content||'{"name":"修改测试"}';

2）JSON增加

UPDATE tablename SET content = content||'{"sex":"男"}';

3）多表关联更新（将tablename2的name赋值给tablename1的name）

UPDATE tablename1 a SET content = content||concat('{"name": "',b.name,'"}')::jsonb FROM (select id,name from tablename2) b WHERE a.id=b.id;

4）JSON数组更新（list数组索引为0的值更新为{“origin”: “导入3”, “version”: 3};TRUE：索引不存在则新增值；FALSE：索引不存在则不操作）

update tablename set content = jsonb_set(content, '{"list",0}','{"origin": "导入3", "version": 3}',TRUE);

扩展

1）查看所有表概要

select * from pg_tables where schemaname='public';

2）查看表详细信息

select * from information_schema.columns where table_schema='public' and table_name='表名';

3）随机数生成（32位）

SELECT md5(random()::VARCHAR) FROM tablename;