My JSON data looks like this:
[{ "id": 1, "payload": { "location": "NY", "details": [{ "name": "cafe", "cuisine": "mexican" }, { "name": "foody", "cuisine": "italian" } ] } }, { "id": 2, "payload": { "location": "NY", "details": [{ "name": "mbar", "cuisine": "mexican" }, { "name": "fdy", "cuisine": "italian" } ] } }]
given a text “foo” I want to return all the tuples that have this substring. But I cannot figure out how to write the query for the same.
I followed this related answer but cannot figure out how to do LIKE
.
This is what I have working right now:
SELECT r.res->>'name' AS feature_name, d.details::text FROM restaurants r , LATERAL (SELECT ARRAY ( SELECT * FROM json_populate_recordset(null::foo, r.res#>'{payload, details}') ) ) AS d(details) WHERE d.details @> '{cafe}';
Instead of passing the whole text of cafe
I want to pass ca
and get the results that match that text.
Advertisement
Answer
Your solution can be simplified some more:
SELECT r.res->>'name' AS feature_name, d.name AS detail_name FROM restaurants r , jsonb_populate_recordset(null::foo, r.res #> '{payload, details}') d WHERE d.name LIKE '%oh%';
Or simpler, yet, with jsonb_array_elements()
since you don’t actually need the row type (foo
) at all in this example:
SELECT r.res->>'name' AS feature_name, d->>'name' AS detail_name FROM restaurants r , jsonb_array_elements(r.res #> '{payload, details}') d WHERE d->>'name' LIKE '%oh%';
db<>fiddle here
But that’s not what you asked exactly:
I want to return all the tuples that have this substring.
You are returning all JSON array elements (0-n per base table row), where one particular key ('{payload,details,*,name}'
) matches (case-sensitively).
And your original question had a nested JSON array on top of this. You removed the outer array for this solution – I did the same.
Depending on your actual requirements the new text search capability of Postgres 10 might be useful.