Postgres index into an array column using another column

Question

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>;

How is this different from your previous question? Please avoid posting duplicates, instead you can wait for answers, or update your question to improve it somehow. — GMB
– GMB, Commented Jan 13, 2020 at 21:25
@GMB Well, I simplified it for myself and others by taking out the filter part. Seemed different enough. — wheresmycookie
– wheresmycookie, Commented Jan 13, 2020 at 21:27
I suspect that proper sample data for both tables, along with the expected results (as tabular text), would make your question more appealing. Also your other question was showing your attemped SQL query, which is always a good thing; you might want to reincorporate it here. — GMB
– GMB, Commented Jan 13, 2020 at 21:29

Bergi · Accepted Answer · 2020-01-13 23:30:17Z

1

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")

edited Jan 13, 2020 at 23:30

user330315

answered Jan 13, 2020 at 21:42

Bergi

670k162 gold badges1k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

wheresmycookie Over a year ago

Thanks! So the final part is that I need to get "correct" from those objects. I'm able to pull out the array item, but now I need to get the field from that item!

Bergi Over a year ago

Use the json ->/->> operators for that, e.g. (options[choice]->>'correct')::boolean

Collectives™ on Stack Overflow

Postgres index into an array column using another column

Setup

Question

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Setup

Question

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related