By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE restaurants (res jsonb);
CREATE TABLE foo(name text); -- DON'T make this char(10)
INSERT INTO restaurants VALUES
('{
"id": 2,
"name": "trail",
"payload": {
"details": [
{
"name": "ohmama",
"cuisine": "mexican"
},
{
"name": "foody",
"cuisine": "italian"
}
],
"location": "NY"
}
}');
CREATE TABLE
CREATE TABLE
INSERT 0 1
-- Your query, untangled:
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%';
| feature_name | detail_name |
|---|---|
| trail | ohmama |
SELECT 1