5 ms·

With Mongo, assuming a document like: { "arr": [{ "a": [{ "b": 5, "c": true }] }] } You could query it with: { "arr.a.b": 5 } And you can create ind

• anarazel 8y ago
  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
  • BillinghamJ 8y ago
    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 :\
    • anarazel 8y ago
      > I believe that only works if it’s the first item in the array right? No, it works regardless of that.