-- create a table with data
CREATE TABLE arrs AS SELECT format('{ "arr": [{ "a": [{ "b": %s, "c": true }] }] }', g.i)::jsonb AS data FROM generate_series(1,1000) g(i);
-- index, this one only supports "rooted" paths, but you can create one that allows searches not starting from the root too
CREATE INDEX idx ON arrs USING gin (data jsonb_path_ops);
-- search
postgres[22708][1]=# SELECT data->'arr' FROM arrs WHERE data @> '{"arr": [{"a":[{"b":5}]}]}';
┌────────────────────────────────┐
│ ?column? │
├────────────────────────────────┤
│ [{"a": [{"b": 5, "c": true}]}] │
└────────────────────────────────┘
(1 row)
-- show index usage
postgres[22708][1]=# EXPLAIN ANALYZE SELECT data->'arr' FROM arrs WHERE data @> '{"arr": [{"a":[{"b":5}]}]}';
┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
├─────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Bitmap Heap Scan on arrs (cost=20.01..24.02 rows=1 width=32) (actual time=0.131..0.132 rows=1 loops=1) │
│ Recheck Cond: (data @> '{"arr": [{"a": [{"b": 5}]}]}'::jsonb) │
│ Heap Blocks: exact=1 │
│ -> Bitmap Index Scan on idx (cost=0.00..20.01 rows=1 width=0) (actual time=0.107..0.108 rows=1 loops=1) │
│ Index Cond: (data @> '{"arr": [{"a": [{"b": 5}]}]}'::jsonb) │
│ Planning Time: 0.107 ms │
│ Execution Time: 0.186 ms │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(7 rows)
You can make an argument that the mongo's path description is easier to write, but "you can’t even get close to that with what Postgres currently supports" doesn't seem accurate.
I believe that only works if it’s the first item in the array right?
Having done more reading up though, it sounds like composite types are actually a better solution for PG, though then they come with their own set of caveats/limitations :\
Edit: formatting