Filter Postgres jsonb field on values, return only matching key-value pairs

0
votes

I have some data structured roughly like this:

id  | data

1   | {"a": 4, "b": 5, "c": 19}
2   | {"a": 6, "b": 7}
3   | {"a": 8, "d": 3}
4   | {"a": 3, "b": 1}

I would like to be able to filter this data based on the values of the keys in the json. As you can see, the keys can vary between records. For example, I would like to get all key-value pairs per row that have a value greater than 4. In the case of the data above, that should give:

id  | data
1   | {"b": 5, "c": 19}
2   | {"a": 6, "b": 7}
3   | {"a": 8}
4   | {} (for this row, it's fine if it's just left out, or returned as empty object)

I've been looking at answers like the one provided here but I don't want to provide a specific field name. Also, I'd like to get both the key and the value if the condition holds. This is my first time working with Postgres jsonb, so I'm probably missing a function.

1
postgresqljsonb

1 Answers

2
votes

You will need to use jsonb_each() together with a sub-query:

select d.id, t.new_data
from the_table d 
  cross join lateral (
     select jsonb_object_agg(key, value) as new_data
     from jsonb_each(d.data) as x(key, value)
     where value::int > 4
  ) as t