sql - 如何从 1 个表中选择多行并插入另一个表中特定行的特定 JSONB 字段？但是在单个原始 SQL 查询中

Question

postgres 10.3

我在名为 sites 的表中有大约 1000 行。

如果我这样查询

SELECT id, name from sites;

我会得到 1000 行。

我还有一张 table 叫 jsonindexdocument单行，其中 id 为 1 和一个名为 index 的字段那是 JSONB

是否有可能在单个查询中取出站点表中的所有 1000 行，然后更新名为 index 的字段？在 ID 1 下？

json 的格式是

[
  {
     "id": 10,
     "name": "somename"
  },
  {
     "id": 11,
     "name": "another name"
  } // and the rest of the 1000 rows
]

如果它使用超过 1 个原始 SQL 语句，我也可以。

更新

我想补充一点，如果结果为空集，则json字段中默认为空数组

Answer 1

假设您可以完全更换 index jsonindexdocument 中的值 table :

UPDATE jsonindexdocument
SET index = (
    -- using json_agg(row_to_json(sites.*)) would also work here, if you want to copy
    -- all columns from the sites table into the json value
    SELECT COALESCE(json_agg(json_build_object(
        'id', id,
        'name', name
    )), '[]'::json)
    FROM sites
)
WHERE id = 1;

举个例子:

CREATE TEMP TABLE sites (
    id   INT,
    name TEXT
);

CREATE TEMP TABLE jsonindexdocument (
    id    INT,
    index JSON
);

INSERT INTO sites
VALUES (1, 'name1')
     , (2, 'name2');

INSERT INTO jsonindexdocument
VALUES (1, NULL);

UPDATE jsonindexdocument
SET index = (
    SELECT COALESCE(json_agg(json_build_object(
        'id', id,
        'name', name
    )), '[]'::json)
    FROM sites
)
WHERE id = 1;

SELECT * FROM jsonindexdocument;

返回

+--+------------------------------------------------------------+
|id|index                                                       |
+--+------------------------------------------------------------+
|1 |[{"id" : 1, "name" : "name1"}, {"id" : 2, "name" : "name2"}]|
+--+------------------------------------------------------------+