I have the following schema in PostgreSQL database:
CREATE TABLE survey_results (
id integer NOT NULL,
raw jsonb DEFAULT '{}'::jsonb,
created_at timestamp without time zone,
updated_at timestamp without time zone
);
INSERT INTO survey_results (id, raw, created_at, updated_at)
VALUES (1, '{ "slides": [{"id": "1", "name": "Test", "finished_at": 1517421628092}, {"id": "2", "name": "Test", "finished_at": 1517421894736}]}', now(), now());
I want to get data from raw['slides']
. I want to have query which return every raw['slides'] id
and raw['slides'] finished_at
. So result of a query should look like this:
id finished_at
1 1517421628092
2 1517421894736
Here is sqlfiddle to experiment with:
http://sqlfiddle.com/#!17/ae504
How can I do this in PostgreSQL?
You need to unnest the array, then you can access each element:
select s.slide ->> 'id' as id,
s.slide ->> 'finished_at' as finished_at
from survey_results, jsonb_array_elements(raw -> 'slides') as s (slide)
http://sqlfiddle.com/#!17/ae504/80
For more details see JSON Functions and Operators in the manual.