Postgres denormalize tables into json objects for node

Question

Heres my problem. I have the following tables

CREATE TABLE inventory(
 id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
 owner text NOT NULL references profile(name)
);
CREATE TABLE inventory_item(
 id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
 description text,
 amount bigint,
 inventory uuid nOT NULL references table(id)
);

Now I would like to query for a single inventory

• all items in that inventory
• id and owner of that inventory

So I basically get a result of the following form, to send to the client:

{
 "id":"1234123-1234-1234",
 "owner":"tom",
 "items":[
 {
 "id":"2344-123412-34",
 "description":"sword",
 "amount":33
 },
 {
 "id":"6434-123412-34",
 "description":"apple",
 "amount":23
 },
 {
 "id":"45454-1123412-34",
 "description":"bow",
 "amount":32
 },
 {
 "id":"234234-1232412-34",
 "description":"arrow",
 "amount":1
 }
 ]
}

I was thinking of querying id and owner directly and then adding the items as a subquery using ARRAY(). Unfortunately, ARRAY() only works with columns of the same type.

So how can I denormalize my table structure into a JSON array for node using a postgres query?

• nbk: please add a minimal reproducible example to your question also take a look at dba.meta.stackexchange.com/questions/2976/…
• user212533: Is there a reason that a standard ODBC return set would be inadequate?
• user2741831: @bbaird I would like to get the inventory info and the items in a single query
• user212533: @user2741831 Why? The work done by the database will be less, even with two result sets. Seems like a solution in search of a problem.
• user2741831: The App runs at a different site than the DB. I've been told to minimize roundtrips. It would also be good if I could create a function that performs this task all in one. But I suppose I could try and send 2 different queries in a single request and then stich the results together in node

Answer 1

The answer was using json_agg instead of array_agg