Returning array of JSON objects from related fields in PostgreSQL

Question

I have the following schema and some data inserted into the DB.

Schema (PostgreSQL v14)

CREATE TABLE users(
    id INT NOT NULL PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    password TEXT NOT NULL,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    is_active BOOLEAN DEFAULT FALSE,
    is_staff BOOLEAN DEFAULT FALSE,
    is_superuser BOOLEAN DEFAULT FALSE
);
INSERT INTO users 
                (id, email, password, first_name, last_name, is_active, is_staff, is_superuser) 
            VALUES (1, '[email protected]', '23456ses', 'John', 'Idogun', true, true, true);
INSERT INTO users 
                (id, email, password, first_name, last_name, is_active, is_staff, is_superuser) 
            VALUES (2, '[email protected]', '23456ses', 'Nelson', 'Idogun', true, true, true);
-- articles table
CREATE TABLE IF NOT EXISTS articles(
    id INT NOT NULL PRIMARY KEY,
    fore_image TEXT NULL,
    title TEXT NOT NULL,
    slug TEXT NOT NULL UNIQUE,
    content TEXT NOT NULL,
    is_published BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    user_id INT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
INSERT INTO articles (id, title, slug, content, is_published, user_id) VALUES (1, 'Upgrading from v0.4 to v0.5 of rust rocket', 'Upgrading from v0.4 to v0.5 of rust rocket', 'Upgrading from v0.4 to v0.5 of rust rocket', true, 1);
INSERT INTO articles (id, title, slug, content, is_published, user_id) VALUES (2, 'Upgrading from v0.3 to v0.4 of rust rocket', 'Upgrading from v0.3 to v0.4 of rust rocket', 'Upgrading from v0.3 to v0.4 of rust rocket', true, 1);
INSERT INTO articles (id, title, slug, content, is_published, user_id) VALUES (3, 'Upgrading from v0.2 to v0.3 of rust rocket', 'Upgrading from v0.2 to v0.3 of rust rocket', 'Upgrading from v0.2 to v0.3 of rust rocket', true, 2);
-- Tags table
CREATE TABLE IF NOT EXISTS tags(
    id INT NOT NULL PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    description TEXT NULL
);
INSERT INTO tags (id, name, description)
    VALUES (1, 'dev', 'Internet dev...'), (2,
            'rust',
            'Confident and safe systems language.'
        ),(3,
            'python',
            'import this. Beautiful is better than ugly...'
        );
-- articles and tags table
CREATE TABLE IF NOT EXISTS articles_tags(
    article_id INT NOT NULL references articles(id) ON DELETE CASCADE,
    tag_id INT NOT NULL references tags(id) ON DELETE CASCADE,
    PRIMARY KEY (article_id, tag_id)
);

INSERT INTO articles_tags(article_id, tag_id) VALUES (1, 1), (1, 2), (2, 3), (2, 2), (2, 1), (3, 1), (3, 3);

---

I also have this query to get a tag and then output all articles having that tag in a list of objects. Each article should in turn have its respective authors. The query did output that but what I want is slightly different.

Query and Current Result

SELECT
    t.id AS t_id,
    t.name AS t_name,
    t.description AS t_description,
    json_agg(
    jsonb_build_object(
        'id', a.id,
        'fore_image', a.fore_image,
        'title', a.title,
        'slug', a.slug,
        'content', a.content,
        'is_published', a.is_published,
        'created_at', a.created_at,
        'updated_at', a.updated_at, 
        'author', jsonb_build_object(
          'id', u.id,
          'email', u.email,
          'first_name', u.first_name,
          'last_name', u.last_name,
          'is_active', u.is_active,
          'is_staff', u.is_staff,
          'is_superuser', u.is_superuser
        )
    )) AS articles
FROM tags AS t
JOIN articles_tags AS a_t ON a_t.tag_id = t.id
JOIN articles AS a ON a_t.article_id = a.id
JOIN users AS u ON a.user_id = u.id
GROUP BY t.id, a.id
HAVING t.id = 1;

t_id	t_name	t_description	articles
1	dev	Internet dev...	[{"id":1,"slug":"Upgrading from v0.4 to v0.5 of rust rocket","title":"Upgrading from v0.4 to v0.5 of rust rocket","author":{"id":1,"email":"[email protected]","is_staff":true,"is_active":true,"last_name":"Idogun","first_name":"John","is_superuser":true},"content":"Upgrading from v0.4 to v0.5 of rust rocket","created_at":"2023-02-11T08:07:56.098667+00:00","fore_image":null,"updated_at":"2023-02-11T08:07:56.098667+00:00","is_published":true}]
1	dev	Internet dev...	[{"id":2,"slug":"Upgrading from v0.3 to v0.4 of rust rocket","title":"Upgrading from v0.3 to v0.4 of rust rocket","author":{"id":1,"email":"[email protected]","is_staff":true,"is_active":true,"last_name":"Idogun","first_name":"John","is_superuser":true},"content":"Upgrading from v0.3 to v0.4 of rust rocket","created_at":"2023-02-11T08:07:56.10037+00:00","fore_image":null,"updated_at":"2023-02-11T08:07:56.10037+00:00","is_published":true}]
1	dev	Internet dev...	[{"id":3,"slug":"Upgrading from v0.2 to v0.3 of rust rocket","title":"Upgrading from v0.2 to v0.3 of rust rocket","author":{"id":2,"email":"[email protected]","is_staff":true,"is_active":true,"last_name":"Idogun","first_name":"Nelson","is_superuser":true},"content":"Upgrading from v0.2 to v0.3 of rust rocket","created_at":"2023-02-11T08:07:56.100741+00:00","fore_image":null,"updated_at":"2023-02-11T08:07:56.100741+00:00","is_published":true}]

---

Expected result

t_id	t_name	t_description	articles
1	dev	Internet dev...	[{"id":1,"slug":"Upgrading from v0.4 to v0.5 of rust rocket","title":"Upgrading from v0.4 to v0.5 of rust rocket","author":{"id":1,"email":"[email protected]","is_staff":true,"is_active":true,"last_name":"Idogun","first_name":"John","is_superuser":true},"content":"Upgrading from v0.4 to v0.5 of rust rocket","created_at":"2023-02-11T08:07:56.098667+00:00","fore_image":null,"updated_at":"2023-02-11T08:07:56.098667+00:00","is_published":true}, {"id":2,"slug":"Upgrading from v0.3 to v0.4 of rust rocket","title":"Upgrading from v0.3 to v0.4 of rust rocket","author":{"id":1,"email":"[email protected]","is_staff":true,"is_active":true,"last_name":"Idogun","first_name":"John","is_superuser":true},"content":"Upgrading from v0.3 to v0.4 of rust rocket","created_at":"2023-02-11T08:07:56.10037+00:00","fore_image":null,"updated_at":"2023-02-11T08:07:56.10037+00:00","is_published":true}, {"id":3,"slug":"Upgrading from v0.2 to v0.3 of rust rocket","title":"Upgrading from v0.2 to v0.3 of rust rocket","author":{"id":2,"email":"[email protected]","is_staff":true,"is_active":true,"last_name":"Idogun","first_name":"Nelson","is_superuser":true},"content":"Upgrading from v0.2 to v0.3 of rust rocket","created_at":"2023-02-11T08:07:56.100741+00:00","fore_image":null,"updated_at":"2023-02-11T08:07:56.100741+00:00","is_published":true}]

How can I achieve this result with optimal performance? Any modification to the query?

View on SQL Fiddle

Answer 1

There seems no need to group by a.id. And that HAVING should probably be a WHERE, although that itself won't make a difference to the results, only the performance.

So simply change this

GROUP BY t.id, a.id
HAVING t.id = 1;

to this:

WHERE t.Id = 1
GROUP BY t.id;