CODEWITHSUNDEEP
postgresql
wheresmycookie
wheresmycookie

Reputation: 763

Postgres index into an array column using another column

Setup

I have the following tables:

Questions
 - id      (INT)
 - quizId  (INT)
 - text    (TEXT)          
 - options (JSON[])      

Answers
 - id      (INT)
 - questionId (INT)
 - choice  (INT)

"options" is an array of JSON objects:

{"{\"text\": \"Text for option 1\", \"correct\": false}", "{\"text\": \"Text for option 2\", \"correct\": true}"}

Question

I would basically like to get the value of "correct", given "choice" (an index), after joining those two tables.

The pseudocode for what I'm trying to achieve would be:

select "Questions"."options"["Answers"."choice"] from <JOIN THOSE TABLES>;

Upvotes: 0

Views: 145

Answers (1)

Bergi
Bergi

Reputation: 664548

Not just pseudocode, that's exactly how you write array subscripts. Now only the JOIN condition is missing:

SELECT "Questions"."options"["Answers"."choice"] AS choosen_answer_option
FROM "Questions" JOIN "Answers" ON ("Questions".id = "Answers"."questionId")

Upvotes: 1

Related Questions