I am new to SQL previously we were using MongoDB and now we have shifted to Postgres. basically, we have 2 tables of Questions and Options. Each question can have multiple option so I design the database just like below table.

Table : dialogues

Table : options

Now I am trying to get all the questions with their options. I have tried inner join like this

SELECT options.option_name, dialogues.*
FROM options INNER JOIN dialogues ON dialogues.dialogue_id = options.dialogue_id)

But it's not what I wanted.

Just to give you an example in Mongo we have used this query

const aggregateQuery = [
        {
          $lookup: {
            from: "options", // The collection you want to join to
            localField: "_id", // The field in orders you want to join on
            foreignField: "question_id", // The field in products you want to join on
            as: "options" // What you want to call the array that stores the joined documents from products
          }
        },
     
    
        }
      ]
      const questionList = await questionModel.aggregate(aggregateQuery)

In result I wanted all the dialogue along with field called "options" which contains all the relevant options from table "options". let me share the final JSON that I get from mongo.

[
        {
            "_id": "yyyyy",
            "question": "What is your gender?",
            "options": [
                {
                    "_id": "xxxxx",
                    "option": "Male",
                    "question_id": "yyyyy",
                    "created_at": "2020-07-04T05:57:00.293Z",
                    "updated_at": "2020-07-04T05:57:00.293Z"
                },
                {
                    "_id": "xxxx",
                    "option": "Female",
                    "question_id": "yyyyy",
                    "created_at": "2020-07-04T05:57:00.293Z",
                    "updated_at": "2020-07-04T05:57:00.293Z"
                }
            ]
        }
    ]

can anybody help ?

CodePudding user response：

This may be your solution.

select jsonb_agg(to_jsonb(t)) from
(
 select 
   d.dialogue_id "_id", 
   d.dialogue_text "question",  
   ( 
     select 
       jsonb_agg(jsonb_build_object('_id', o.option_id, 'option', o.option_name))
       from options o
       where o.dialogue_id = d.dialogue_id
   ) "options"
 from dialogues d
) t;

Here is a JOIN/GROUP BY version

select jsonb_agg(to_jsonb(t)) from
(
 select 
   d.dialogue_id "_id", 
   d.dialogue_text "question",  
   jsonb_agg(jsonb_build_object('_id', o.option_id, 'option', o.option_name)) "options"
 from dialogues d inner join options o using (option_id)
 group by d.dialogue_id, d.dialogue_text
) t;