How to parse json array in postgres query

Question

I need to parse json array from below table column. The result should be answer of Q2 question in below example.

id      data
1   [{"questionId":"Q1","answer":"A1"},{"questionId":"Q2","answer":"A2"}]
2   [{"questionId":"Q1","answer":"A1"},{"questionId":"Q2","answer":"A2"}]

So the result should be like this

1 A2
2 A2

I tried with data::json->'answer' as answer but doesn't seem to work on array

Answer 1

You may use json_array_elements and filter rows using a WHERE clause

select id, j->>'answer' as answer FROM t 
cross join lateral json_array_elements(data::json) as j
WHERE j->>'questionId' = 'Q2'

Demo