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

问题描述

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.