PostgreSQL – How to Denormalize Tables into JSON Objects for Node

postgresql

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?

Best Answer

The answer was using json_agg instead of array_agg

Related Question