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%')

See How do I search for a specific string in a JSON Postgres data type column? — klin
– klin, Commented Feb 24, 2022 at 9:28

Edouard · Accepted Answer · 2022-02-24 10:07:50Z

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

answered Feb 24, 2022 at 10:07

Edouard

7,1291 gold badge11 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related