Add data from many-to-many to selection data-CodePudding

I have 3 tables:

"tags"
id serial
name text

"questions"
id serial
content jsonb

"questions_tags" (many-to-many)
question_id integer
tag_id integer

I can select all data from questions:

SELECT * FROM questions;

I can select tags of specific question:

SELECT t.*
FROM tags t
JOIN questions_tags qt ON t.id = qt.tag_id
JOIN questions q ON qt.question_id = q.id
WHERE q.id = 157;

I need to select all questions and add to this selection tags for every question. I'm using nodejs and node-postgres and I need to get something like:

[
  {
    id: 1,
    content: { someProp: 'someValue' },
    tags: [
      { id: 58, name: 'sometag58' },
      { id: 216, name: 'sometag216' }
    ]
  },
  ...
]

How can I do this without ORM, only with the help of vanilla SQL?

CodePudding user response：

I would let PostgreSQL handle it for me:

with tagjson as (
  select q.id, q.content, jsonb_agg(to_jsonb(tags)) as tags
    from questions q
         left join questions_tags qt on qt.question_id = q.id
         left join tags on tags.id = qt.tag_id
   group by q.id, q.content
)
select jsonb_pretty(jsonb_agg(to_jsonb(tagjson))) as result
  from tagjson;

Result:

[
    {
        "id": 1,
        "tags": [
            {
                "id": 58,
                "name": "sometag58"
            },
            {
                "id": 216,
                "name": "sometag216"
            }
        ],
        "content": "some question 1 content"
    }
]

db<>fiddle here

EDIT TO ADD:

Based on a conversation in the comments, the final query should be modified to:

select * from tagjson;

Or the CTE could be promoted to be a stand-alone query.

This allowed the conventional node syntax while preserving the aggregated tags array under the tags key:

const questions: Question[] = result.rows;