PostgreSQL: query on JSONB data

Question

I have a table storing data as below:

id       | data
serial   | jsonb 
---------+-----------------------------------------------
1        | {"files": [{"date": "2016-05-20", "name": "a"},{"date": "2016-05-21", "name": "b"}]}
2        | {"files": [{"date": "2015-04-02", "name": "c"}]}
3        | {"files": [{"date": "2016-02-12", "name": "d"},{"date": "2016-01-27", "name": "e"}]}
4        | {"files": []}

I have the following JSON file:

{
  "files": [
    {"date": "2016-05-20", "name": "Test 1"},
    {"date": "2016-05-21", "name": "Test 2"},
    {"date": "2016-05-22", "name": "Test 3"}
  ]
}

I would like to display the different dates associated with the idcolumn in a table, and to ignore rows with no date available (row 4 is not displayed in the result):

id  | date
----+-----------
1   | 2016-05-20
1   | 2016-05-21
2   | 2015-04-02
3   | 2016-02-12
3   | 2016-01-27

Here the query I have written but it returns nothing:

SELECT 
  id,
  "data" -> 'files' ->> 'date' AS "date" 

FROM myTable

WHERE "data" -> 'files' @> '{"date"}'::jsonb ;

Kamil Kukielka · Accepted Answer · 2016-05-24 15:11:15Z

1

Try this:

SELECT id, json_array_elements((data->'files')::json)->>'date' as "date" FROM myTable

edited May 24, 2016 at 15:11

answered May 24, 2016 at 15:04

Kamil Kukielka

513 bronze badges

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

1 Comment

wiltomap Over a year ago

Perfect, I didn't know this function... Thanks for your help!

Collectives™ on Stack Overflow

PostgreSQL: query on JSONB data

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related