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月10日T20: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ドル;

• psygo: This is a personal project at this point, and it is still a little bit far from getting into production, but you're right to talk about SQL Injections. So far, I validate requests a little bit, but nothing major, but I do have plans to shield it better against attacks in the near future.
• Charlieface: SQL injection is also about correctness not just security. You could get incorrect results.
• psygo: I'm still getting error: more than one row returned by a subquery used as an expression though.
• psygo: Thanks! Maybe node-pg isn't able to pretty-print this nested thing properly because what I get is something like [ Object, Object ]. But, upon printing this nested array, everything seems to be working.