Reputation: 924
How could I query a JSON field to search in all fields of the JSON?
Table
id | JSON
--------------------------
1 | {"name":"toto","age":25,"surname":"toto2"}
I'd like to query on every fields of the JSON, because I'm programming a global search function, and i need for example to return every row containing "toto" in its JSON.
I managed to do it in SqlServer 2016 with OPEN_JSON function.
Here is my code
SELECT DISTINCT *
FROM ObjectsTable
CROSS APPLY OPENJSON([JSON]) as tbValues
WHERE tbValues.value like '%toto%'
I need to do the same thing in Postgres.
Upvotes: 0
Views: 710
Reputation:
Essentially the same in Postgres, but cross apply
is cross join lateral
in the SQL standard and in Postgres
select o.id, t.*
from objectstable o
cross join lateral json_each_text(o.json) as t(k,val)
where t.val like '%toto%'
Upvotes: 5