假设你可以完全替换 index 价值观 jsonindexdocument 表格：

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