时间戳

首页 > 解决方案 > 查询以更新 JSON 值

sql - 查询以更新 JSON 值

问题描述

我想查询以更新具有 JSON 数据的表。表明细中列明细的数据如下。

id     | 1

detail | {
   myCompany: [{ email: 'company@company.com', inProfit: true }],
   myData: [
      { email: 'example@example.com'},
      { email: 'test@test.com'},
      { email: 'test@example.com' },
   ],
};

列明细的JSON值如下

{
   "myCompany":[
      {
         "email":"company@company.com",
         "inProfit":true
      }
   ],
   "myData":[
      {
         "email":"example@example.com"
      },
      {
         "email":"test@test.com"
      },
      {
         "email":"test@example.com",
      }
   ]
}

我想要一个更新myData. 为afterpersonalUse:true的所有字段添加一个新字段。例如，运行更新查询后，该字段应如下所示，myDataemailmyData

"myData":[
      {
         "email":"example@example.com",
         "personalUse":true
      },
      {
         "email":"test@test.com",
         "personalUse":true
      },
      {
         "email":"test@example.com",
         "personalUse":true
      }
   ]

您可以使用以下查询在本地尝试。

CREATE TABLE details (id bigserial primary key, detail json not null);

INSERT INTO details (detail) 
VALUES 
  ('{"myCompany":[{"email":"company@company.com", "inProfit":true } ],  
     "myData":[{"email":"example@example.com" }, 
               {"email":"test@test.com"},
               {"email":"test@example.com"} ] }');

请有人帮帮我。

标签： sqljsonpostgresql

解决方案

有几个功能可能会有所帮助：

  • ->运算符：按键获取 JSON 对象字段。
  • jsonb_agg: 将值聚合为 JSON 数组。
  • json_array_elements: 将 JSON 数组扩展为一组 JSON 值。
  • jsonb_set: 用一个值替换 path 指定的 JSON 的一部分（或者如果部分丢失，则插入它）。

你可以在这里阅读更多：

询问

update details
  set detail = jsonb_set(detail::jsonb, '{myData}', myData)
  from (
    select id, jsonb_agg(myData) as myData
      from (
        select
          id,
          jsonb_set(
            json_array_elements(detail->'myData')::jsonb,
            '{personalUse}',
            'true') as myData
          from details
      ) x
      group by id
  ) z
  where details.id = z.id;

这个怎么运作

myData将数组与id表格一起展开。

user=# select id, json_array_elements(detail->'myData') as myData from details;
 id |                        mydata                         
----+-------------------------------------------------------
  1 | {"email": "example@example.com"}
  1 | {"email": "test@test.com"}
  1 | {"email": "test@example.com"}
  2 | {"email": "tt@test.com"}
  2 | {"email": "tt@example.com"}

用于jsonb_set更新每个对象。

user=# select
user-#   id,
user-#   jsonb_set(
user(#     json_array_elements(detail->'myData')::jsonb,
user(#     '{personalUse}',
user(#     'true'
user(#   ) as myData
user-# from details;
 id |                        mydata                         
----+-------------------------------------------------------
  1 | {"email": "example@example.com", "personalUse": true}
  1 | {"email": "test@test.com", "personalUse": true}
  1 | {"email": "test@example.com", "personalUse": true}
  2 | {"email": "tt@test.com", "personalUse": true}
  2 | {"email": "tt@example.com", "personalUse": true}

用于jsonb_agg将电子邮件收集回 JSON 数组。

user=# select id, jsonb_agg(myData) as myData from (
user(#   select
user(#     id,
user(#     jsonb_set(
user(#       json_array_elements(detail->'myData')::jsonb,
user(#       '{personalUse}',
user(#       'true'
user(#     ) as myData
user(#   from details
user(# ) x group by id;
 id |
----+-------------------------------------------------------------
  2 | [{"email": "tt@test.com", "personalUse": true}, ...
  1 | [{"email": "example@example.com", "personalUse": true}, ...

现在您可以jsonb_set再次使用更新原始 JSON。

推荐阅读