Search in all JSON fields with PostgreSQL

Question

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.

score 5 · Accepted Answer · 2017-06-09 17:23:51Z

5

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

Collectives™ on Stack Overflow

Search in all JSON fields with PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related