How to select many rows from 1 table and insert into a specific JSONB field of a specific row in another table? But in a single raw SQL query

user_80353 0
Viewed 42

postgres 10.3

I have about 1000 rows inside a table called sites

If I query like this

SELECT id, name from sites;

I will get the 1000 rows.

I also have another table called jsonindexdocument with a single row where the id is 1 and a field called index that is JSONB

Is it possible that in a single query I take out all the 1000 rows in sites table and then update the field called index under id 1?

The format of the json would be

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

I am also okay if it uses more than 1 raw SQL statement.

UPDATE

I want to add that if the result is empty set, then default to empty array in the json field

postgresql arrays sql json
1 Answers

Assuming you're OK with fully replacing the index value in the 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;

As an example:

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;

returns

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