在 PostgreSQL 中查询 JSON 字段

debugcn 发表于 Dev

来自 j

我的 PostgreSQL 数据库中有一个 JSON 字段，带有这样的 JSON 架构

{
   "home":{
      "lat":"37.774192",
      "long":"-92.118511",
      "address":"home address"
   },
   "work":{
      "lat":"37.774192",
      "long":"-92.118511",
      "address":"work address"
   },
   "more":[
      {
         "id":"bRuKnd",
         "name":"gym",
         "lat":"37.774192",
         "long":"-92.118511",
         "address":"gym address"
      },
      {
         "id":"eVdOlD",
         "name":"fastfood",
         "lat":"37.774192",
         "long":"-92.118511",
         "address":"fastfood addres"
      },
      {
         "id":"SwkfcL",
         "name":"Res",
         "lat":"37.774192",
         "long":"-92.118511",
         "address":"Res address"
      }
   ]
}

我可以使用此查询在“more”或“work”和“home”对象中获取每个 JSON 字段

UPDATE items FROM (SELECT loc FROM public.tbl_user where uuid = '2fa7a484-f454-4d44-94be-a4011aee47b4') as o 
   ,json_array_elements(o.loc#>'{more}') AS items where items->>'id' = 'eVdOlD' ;

我不知道如何编辑或删除数据库中的这些字段

我需要这些查询

编辑 home 对象并删除其值
编辑工作对象并删除其值
在更多对象上添加编辑和删除项目

理查德·威尔登

当您在 postgres JSON 中编辑值时，您并不是在编辑，而是用新的内容替换该行。

jsonb_set 可能就是你要找的。

请参阅https://www.postgresql.org/docs/current/functions-json.html

基于您示例的更简单形式：

sophia=> create table foo (bar jsonb);
CREATE TABLE
sophia=> insert into foo values ('{
   "home":{
      "lat":"37.774192",
      "long":"-92.118511",
      "address":"home address"
   }}'::jsonb);
INSERT 0 1
sophia=> select * from foo;
                                       bar                                       
---------------------------------------------------------------------------------
 {"home": {"lat": "37.774192", "long": "-92.118511", "address": "home address"}}
(1 row)

sophia=> update foo set bar = jsonb_set(bar, '{home,address}', '"29 Acacia Road"');
UPDATE 1
sophia=> select * from foo;
                                        bar                                        
-----------------------------------------------------------------------------------
 {"home": {"lat": "37.774192", "long": "-92.118511", "address": "29 Acacia Road"}}
(1 row)

sophia=>

与删除类似：

sophia=> update foo set bar = jsonb_set(bar, '{home}', (bar -> 'home') - 'address');
UPDATE 1
sophia=> select * from foo;
                         bar                          
------------------------------------------------------
 {"home": {"lat": "37.774192", "long": "-92.118511"}}
(1 row)

sophia=>

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。