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

Question

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!

eyal · Accepted Answer · 2019-02-06 08:46:42Z

1

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'
        );

answered Feb 6, 2019 at 8:46

eyal

426 bronze badges

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related