How to filter records by specific attribute within jsonb field that is an array of objects

1
votes

I have a table with a column named "settings" with type jsonb (nullable) with following pattern:

[
  {
     att1: "test"
     "start": 2019-02-01 00:00:00
     "end": 2019-02-20 00:00:00
  },
  {
     att1: "test2"
     "start": 2019-03-01 00:00:00
     "end": 2019-03-15 00:00:00
  }
]

Example of a table:

id,settings
1,"[{"end": "2019-02-01 00:00:00"},{"2019-02-05 00:00:00"}]"
2,"[{"end" : '2019-02-20 00:00:00'}]"

I want find all the records that have "end" field within the array jsonb that is above specific timestamp

e.g I expect to get record 2 if I query "end" > "2019-02-19 00:00:00"..

Please advise!

1
postgresql

1 Answers

1
votes

You can try this code:

SELECT *
FROM test_table tt
WHERE exists(
          SELECT *
          FROM jsonb_array_elements(tt.settings) AS settings
          WHERE (settings ->> 'start') > '2019-02-19 00:00:00'
        );