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

本文是小编为大家收集整理的关于PostgreSQL在JSON数组中设置JSON对象的字段的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

本文来自:IT宝库(https://www.itbaoku.cn)

我有这样的桌子:

| 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}] |
+-------------+-------------+-----------------+

我想在某个游戏(国际象棋)中将玩家(JOE)的角色设置为某个值(用户),因此结果应该看起来像:

| 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}] |
+-------------+-------------+-----------------+

是否可以通过单个查询来实现此目标?

推荐答案

通过在每个更新上重新创建JSON数组,这是可能的.

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

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_rowscte.

  2. 来完成的
  3. 在找到用户" joe"的JSON行中更新JSON数据.

  4. 拥有新的JSON值后,只需根据ID进行更新.

注意:如您所见,在当前实现中,JSON数组被重新创建(仅在需要更新的行中).这可能会导致阵列内部元素的顺序更改.

本文地址:https://www.itbaoku.cn/post/1765584.html

问题描述

I have a table like this:

| 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?

推荐答案

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

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

The inserted data:

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

Update query:

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;

Results of the query:

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

The query works in the following way:

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

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

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

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.