Posgresql join two tables where the foriegn key is an array of ids

Question

I am new to SQL and I have three table

Templates Table

CREATE TABLE templates (
    template_id serial PRIMARY KEY,
    template_name VARCHAR ( 15 ) UNIQUE NOT NULL,
    FOREIGN KEY (developer_id) REFERENCES users(user_id),
    FOREIGN KEY (category_id) REFERENCES categories(category_id),
    tag_ids int[],
    FOREIGN KEY (EACH ELEMENT OF tag_ids) REFERENCES tags(tag_id)
    );

Categories Table

CREATE TABLE categories (
    category_id serial PRIMARY KEY,
    category_name VARCHAR ( 15 ) UNIQUE NOT NULL
    );  

Tags Table

CREATE TABLE tags (
    tag_id serial PRIMARY KEY,
    tag_name VARCHAR ( 100 ) NOT NULL,
    );

I want to Select all templates where each template has a category object and a tags object.

Each template has one category but may have multiple tags.

I want to have the tags as an array attribute in the template object

I have tried this query, it does what i want but it creates multiple objects for the same template. So it simply creates n objects where n is the number of tags.

  let query = `SELECT t.*, to_json(c) "category", ${developerJson} "developer", json_agg(tgs) "tags" FROM templates t INNER JOIN categories c ON t.category_id = c.category_id INNER JOIN users d ON t.developer_id = d.user_id JOIN tags tgs ON tgs.tag_id = ANY(t.tags_id) ${condition} ${groupBy}`;

Can anyone help me?

Answer 1

I have found the solution. I was passing the tag_id in the group elements.

Once I removed it, I got what I was expecting

const developerJson = `json_build_object( 'first_name',first_name, 'last_name', last_name, 'avatar_link', avatar_link, 'slug', d.slug ,'date_joined',date_joined)`;
  const groupBy = `GROUP BY t.template_id, c.*, d.first_name, d.last_name, d.avatar_link, d.slug, d.date_joined`;
  let query = `SELECT t.*, to_json(c) "category", ${developerJson} "developer", json_agg(tgs) "tags" FROM templates t INNER JOIN categories c ON t.category_id = c.category_id JOIN users d ON t.developer_id = d.user_id JOIN tags tgs ON tgs.tag_id = ANY(t.tags_id) ${groupBy}`;