Hacker News new | past | comments | ask | show | jobs | submit login

That works. A bit different syntax, but it works:

  -- 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.

Edit: formatting




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 :\


> I believe that only works if it’s the first item in the array right?

No, it works regardless of that.




Consider applying for YC's Spring batch! Applications are open till Feb 11.

Guidelines | FAQ | Lists | API | Security | Legal | Apply to YC | Contact

Search: