PostgreSQL在JSON数组中设置JSON对象的字段 [英] PostgreSQL set field of JSON object in JSON array

问题描述

我有一个这样的表：

| id (SERIAL) | game (TEXT) | players (JSONB) |
+-------------+-------------+-----------------+
|      1      | chess       | [{name: Joe, role: admin}, {name: Mike, role: user}] |
|      2      | football    | [{name: Foo, role: user}, {name: Bar, role: user}] |
+-------------+-------------+-----------------+

我想在以下位置将玩家（乔）的角色设置为某个值（用户）某个游戏（棋），因此结果应如下所示：

I want to set the role of a player (Joe) to a certain value (user) in a certain game (chess), so the result should look like this:

| id (SERIAL) | game (TEXT) | players (JSONB) |
+-------------+-------------+-----------------+
|      1      | chess       | [{name: Joe, role: user}, {name: Mike, role: user}] |
|      2      | football    | [{name: Foo, role: user}, {name: Bar, role: user}] |
+-------------+-------------+-----------------+

是否可以通过单个查询来实现？

Is it possible to achieve this with a single query?

推荐答案

可以通过在每次更新时重新创建json数组来实现。

This is possible by recreating the json array on each update.

用于创建表和示例数据的SQL插入：

SQL for table creation and example data insertion:

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"}]');

插入的数据：

+----+----------+----------------------------------------------------------------------+
| id |   game   |                               players                                |
+----+----------+----------------------------------------------------------------------+
|  1 | chess    | [{"name": "Joe", "role": "admin"}, {"name": "Mike", "role": "user"}] |
|  2 | football | [{"name": "Foo", "role": "user"}, {"name": "Bar", "role": "user"}]   |
+----+----------+----------------------------------------------------------------------+

更新查询：

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;

查询结果：

+----+----------+---------------------------------------------------------------------+
| id |   game   |                               players                               |
+----+----------+---------------------------------------------------------------------+
|  2 | football | [{"name": "Foo", "role": "user"}, {"name": "Bar", "role": "user"}]  |
|  1 | chess    | [{"name": "Joe", "role": "user"}, {"name": "Mike", "role": "user"}] |
+----+----------+---------------------------------------------------------------------+

查询以以下方式工作：

1. 将json数组转换为json行，并通过 game 属性对其进行过滤。这是通过创建 json_rows CTE来完成的。

1. Convert the json array to json rows and filter them by the game property. This is done by creating the json_rows CTE.

更新json行中的json数据，其中找到用户 Joe。

Update the json data in the json rows where the user "Joe" is found.

一旦有了新的json值，只需根据ID进行更新即可。

Once you have the new json values, just do an update based on the id.

注意：如您所见，在当前实现中，将重新创建json数组（仅在需要进行更新）。这可能会导致数组内部元素顺序的更改。

Note: As you can see, in the current implementation the json array gets recreated (only in the rows that need to be updated). This may cause a change in the order of the elements inside the array.

这篇关于PostgreSQL在JSON数组中设置JSON对象的字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！