时间戳

首页 > 解决方案 > 在 PostgreSQL 中查询 JSON 字段

arrays - 在 PostgreSQL 中查询 JSON 字段

问题描述

我的 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"
      }
   ]
}

我可以使用此查询在“更多”或“工作”和“家庭”对象中获取每个 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' ;

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

我需要这些查询

标签： arraysjsonpostgresql

解决方案

当您在 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=>

推荐阅读