Return Subquery with Multiple Columns as JSON Array of JSON Objects

Question

In my setup, the relevant tables for this problem are:

• Items
• Users
• Collections (of Items) — each user can have multiple
• Collection Items — where I track what items are in each collection.

The items and users tables are pretty much as simple as it gets, here are the collections and collection_items tables (mi is the name of my schema):

CREATE TABLE IF NOT EXISTS mi.users (
    id UUID DEFAULT uuid_generate_v4 () CONSTRAINT users_pk PRIMARY KEY,
    created_at TIMESTAMP DEFAULT now(),
    name VARCHAR(50) UNIQUE NOT NULL CONSTRAINT min_length_users_name CHECK (char_length(name) >= 4),
    email VARCHAR UNIQUE NOT NULL,
    pic BYTEA
);

CREATE TABLE IF NOT EXISTS mi.items (
    id UUID DEFAULT uuid_generate_v4 () CONSTRAINT items_pk PRIMARY KEY,
    created_at TIMESTAMP DEFAULT now(),
    author_id UUID NOT NULL CONSTRAINT item_author_fk REFERENCES mi.users (id),
    title VARCHAR(300) NOT NULL CONSTRAINT min_length_items_title CHECK (char_length(title) >= 4),
    description VARCHAR(5000) CONSTRAINT min_length_items_description CHECK (char_length(description) >= 5)
);

CREATE TABLE IF NOT EXISTS mi.collections (
    id UUID DEFAULT uuid_generate_v4 () CONSTRAINT collections_pk PRIMARY KEY,
    created_at TIMESTAMP DEFAULT now(),
    author_id UUID NOT NULL CONSTRAINT collection_author_fk REFERENCES mi.users (id),
    title VARCHAR(300) NOT NULL CONSTRAINT min_length_items_title CHECK (char_length(title) >= 4),
    description VARCHAR(5000) CONSTRAINT min_length_items_description CHECK (char_length(description) >= 5)
);
    
CREATE TABLE IF NOT EXISTS mi.collection_items (
    id UUID DEFAULT uuid_generate_v4 () CONSTRAINT collection_items_pk PRIMARY KEY,
    created_at TIMESTAMP DEFAULT now(),
    collection_id UUID NOT NULL CONSTRAINT collection_items_collection_fk REFERENCES mi.collections (id),
    item_id UUID NOT NULL CONSTRAINT collection_items_item_id REFERENCES mi.items (id),
    CONSTRAINT you_can_only_add_an_item_once_to_a_collection UNIQUE (collection_id, item_id)
);

What I would like is to, when querying for a collection, also return its items, in a JSON array of JSON objects (I'm using node-pg, that's why there are variables with ${}):

SELECT 
    mi.collections.created_at,
    jsonb_build_object(
        'id', mi.collections.author_id,
        'name', mi.users.name,
        'pic', mi.users.pic
    ) author,
    mi.collections.title,
    mi.collections.description,
    (
        SELECT COUNT(*)
        FROM mi.collection_items
        WHERE mi.collection_items.collection_id = '${collectionId}'
    ) AS total_items,
    -- This is where I'm trying to get it done.
    jsonb_build_array(
        SELECT 
            jsonb_build_object(
                'id', mi.items.id,
                'title', mi.items.title,
                'description', mi.items.description
            ) item
        FROM mi.collection_items
        JOIN mi.items ON mi.collection_items.item_id = mi.items.id
        WHERE mi.collection_items.collection_id = '${collectionId}'
        ORDER BY mi.collection_items.created_at DESC
        OFFSET ${offset}
        LIMIT ${paginationLimit}
    ) 
FROM mi.collections
JOIN mi.users ON mi.users.id = mi.collections.author_id
WHERE mi.collections.id = '${collectionId}'

The query would result in something of this shape:

{
  "created_at": "2023-07-10T20:54:34.883Z",
  "title": "Title 1",
  "description": "A great description",
  "author": {
    "id": "906ca589-db0f-4c6e-add4-220cc3f2ea95",
    "name": "john",
    "pic": "\\x2f686f6d652f7068696c692f436f64652f6d61676e75735f696e6465782f6261636b656e642f6173736574732f737461636b6f766572666c6f775f7069635f312e706e67"
  },
  "total_items": 2,
  "items": [
    {
      "id": "906ca589-abcd-4c6e-add4-220cc3f2ea95",
      "title": "Title 1",
      "description": "Description 1"
    },
    {
      "id": "906ca589-efgh-4c6e-add4-220cc3f2ea95",
      "title": "Title 2",
      "description": "Description 2"
    }
  ]
}

Answer 1

You can't use jsonb_build_array to build an array out of a set of rows, it only accepts a scalar value. So you need to use jsonb_agg instead, within the subbquery

(
    SELECT
        jsonb_agg(
            jsonb_build_object(
                'id', mi.items.id,
                'title', mi.items.title,
                'description', mi.items.description
            )
        )
    FROM mi.collection_items
    JOIN mi.items ON mi.collection_items.item_id = mi.items.id
    WHERE mi.collection_items.collection_id = '${collectionId}'
    ORDER BY mi.collection_items.created_at DESC
    OFFSET ${offset}
    LIMIT ${paginationLimit}
)

But I'd advise you to learn how to use table aliases, to make this more readable. Also, I'm not sure about your client driver, but it looks suspiciously like dangerous SQL injection. Furthermore, you can restrict yourself to only filtering the collection_id once, by using correlated predicates.

SELECT 
    c.created_at,
    jsonb_build_object(
        'id', c.author_id,
        'name', u.name,
        'pic', u.pic
    ) AS author,
    c.title,
    c.description,
    (
        SELECT COUNT(*)
        FROM mi.collection_items ci
        WHERE ci.collection_id = c.id
    ) AS total_items,
    (
        SELECT
          jsonb_agg(
            jsonb_build_object(
                'id', i.id,
                'title', i.title,
                'description', i.description
            )
          )
        FROM mi.collection_items ci
        JOIN mi.items i ON ci.item_id = i.id
        WHERE ci.collection_id = c.id
        ORDER BY ci.created_at DESC
        OFFSET $1
        LIMIT $2
    ) AS items
FROM mi.collections c
JOIN mi.users u ON u.id = c.author_id
WHERE c.id = $3;