CODEWITHSUNDEEP
sqljsonpostgresql
Vincent Ducroquet
Vincent Ducroquet

Reputation: 924

Search in all JSON fields with PostgreSQL

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

Answers (1)

user330315
user330315

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

Related Questions