PostgreSQL查詢JSON資料

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;