CODEWITHSUNDEEP
postgresql
user1298426
user1298426

Reputation: 3717

How to parse json array in postgres query

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

Upvotes: 3

Views: 2695

Answers (2)

Kaushik Nayak
Kaushik Nayak

Reputation: 31656

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

Upvotes: 3

user11044402
user11044402

Reputation:

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

Upvotes: 1

Related Questions