PROSAGA码农传奇-postgresql-PostgreSQL在JSON数组中设置JSON对象的字段

<div class =“post-text”itemprop =“text”>
  
    这可以通过在每次更新时重新创建json数组来实现。
  
  
    用于表创建和示例数据插入的SQL：
  
   <pre>
 <code>
 CREATE TABLE test_table(
 id BIGSERIAL PRIMARY KEY ,
 game TEXT,
 players JSONB
);

INSERT INTO test_table(game, players)
    VALUES
      ('chess', '[{"name": "Joe", "role": "admin"}, {"name": "Mike", "role": "user"}]'),
      ('football', '[{"name": "Foo", "role": "user"}, {"name": "Bar", "role": "user"}]');

</code>
 </pre>
  
    插入的数据：
  
   <pre>
 <code>
 +----+----------+----------------------------------------------------------------------+
| id | game | players |
+----+----------+----------------------------------------------------------------------+
| 1 | chess | [{"name": "Joe", "role": "admin"}, {"name": "Mike", "role": "user"}] |
| 2 | football | [{"name": "Foo", "role": "user"}, {"name": "Bar", "role": "user"}] |
+----+----------+----------------------------------------------------------------------+

</code>
 </pre>
  
    更新查询：
  
   <pre>
 <code>
 WITH json_rows AS
(SELECT id, jsonb_array_elements(players) as json_data FROM test_table
WHERE game = 'chess'),
 updated_rows AS (
 SELECT
 id,
 array_to_json(array_agg(
 CASE WHEN json_data -> 'name' = '"Joe"'
 THEN jsonb_set(json_data, '{role}', '"user"')
 ELSE json_data END)) as updated_json
 FROM json_rows
 GROUP BY id
)
UPDATE test_table SET players = u.updated_json
FROM updated_rows u
WHERE test_table.id = u.id;

</code>
 </pre>
  
    查询结果：
  
   <pre>
 <code>
 +----+----------+---------------------------------------------------------------------+
| id | game | players |
+----+----------+---------------------------------------------------------------------+
| 2 | football | [{"name": "Foo", "role": "user"}, {"name": "Bar", "role": "user"}] |
| 1 | chess | [{"name": "Joe", "role": "user"}, {"name": "Mike", "role": "user"}] |
+----+----------+---------------------------------------------------------------------+

</code>
 </pre>
  
    该查询以下列方式工作：
  
  <OL>
    <LI>
      
        将json数组转换为json行并按其过滤它们
         <code>
 game
 </code>
         属性。这是通过创建
         <code>
 json_rows
 </code>
         CTE。
      
    </LI>
    <LI>
      
        更新找到用户“Joe”的json行中的json数据。
      
    </LI>
    <LI>
      
        获得新的json值后，只需根据id进行更新。
      
    </LI>
  </醇>
  
    的
      注意：
    </强>
      如您所见，在当前实现中，重新创建了json数组（仅在需要更新的行中）。这可能会导致数组内元素顺序发生变化。
  
</DIV>