PostgreSQL – Return Rows with Specific Value in JSONB Dictionary

postgresql

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.

Best Answer

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>>

Related Question