Postgres check if value exists in a json field (without knowing key)

Question

I have a json column in postgresql, with a structure like so:

json_col:

{
"key1":["value1","value1.1"],
"key2":["value2"]
}

This would be one field in one row. I don't know the names of the keys. What I want to get is to get the row that contains the above json field without querying by a key but querying by value name (ex: value1 in this case).

To query for a key I use:

SELECT * FROM table where (json_col -> 'key1')::jsonb is not null;

This gets me what I want but via key1 which, in reality, I do not know the name of. Is there a way to directly query for json value?

(or part of a json value works too for me, ex: ...LIKE '%alue%')

Answer 1

In postgres 9.4 try this :

SELECT *
  FROM table
 CROSS JOIN LATERAL jsonb_each_text(json_col) AS c
 CROSS JOIN LATERAL unnest(c.value :: text[]) AS v(value)
 WHERE v.value LIKE '%value%'