Return all rows with a certain value in jsonb dictionary in Postgres

Question

Let's say I've got a table like this where name is just a string and dicts is jsonb.

id | name | dicts 
----+------+-----------------------------------------------------------
 1 | "bob"| {"food": "pasta", "exercise": "walking"}
 2 | "joe"| {"vehicle": "car"}
 3 |"john"| {"hobbies": "walking"}

And I want to return all rows that contain "walking" as a value within the key:value pairs in the dicts column. So in this case, I'd want to return the "bob" and "john" rows since "walking" appears as values in both rows. The number of key:value pairs in each jsonb dict can vary and the keys can also vary.

There's this command

select jsonb_object_keys(column) from table;

that at least gets me all the possible keys represented in a particular column, but that's the only lead I've got so far.

Answer 1

You can use jsonb_each_text (or json_each_text) function which expands outer most json object into key - value record. Here is the example query;

SELECT * FROM 
(
 SELECT
 t.id,
 (jsonb_each_text(t.dicts)).*
 FROM
 my_table t
) denormalized_values
WHERE
 value = 'walking'

And the result of the query above will be;

| id | key | value |
|----|----------|---------|
| 1 | exercise | walking |
| 3 | hobbies | walking |

<<fiddle>>