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

Kaushik Nayak · Accepted Answer · 2019-02-13 10:01:49Z

3

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

answered Feb 13, 2019 at 10:01

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

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

Comments

user11044402 · Accepted Answer · 2019-02-13 09:51:42Z

1

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

answered Feb 13, 2019 at 9:51

user11044402

Collectives™ on Stack Overflow

How to parse json array in postgres query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related