0

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.

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.