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
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'
Try the ># operator.
create temporary table t (id serial primary key, data json);
insert into t (
data
)
values (
'[{"questionId":"Q1","answer":"A1"},{"questionId":"Q2","answer":"A2"}]'
);
insert into t (
data
)
values (
'[{"questionId":"Q1","answer":"A1"},{"questionId":"Q2","answer":"A2"}]'
);
-- The Q1 ist the 2nd element in the array and has index 1.
select id, data::json#>'{1,answer}' from t;
Output:
+------+------------+
| id | ?column? |
|------+------------|
| 1 | "A2" |
| 2 | "A2" |
+------+------------+