From: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
---|---|
To: | Tim Smith <randomdev4+postgres(at)gmail(dot)com> |
Cc: | Christophe Pettus <xof(at)thebuild(dot)com>, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>, pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: In need of some JSONB examples ? |
Date: | 2015-01-27 00:13:55 |
Message-ID: | CAHyXU0z3mvmnV-L0MHXPWcxk6ZNFGLxEZQpP0os=3PVTz1Raww@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Sun, Jan 25, 2015 at 6:50 AM, Tim Smith
<randomdev4+postgres(at)gmail(dot)com> wrote:
>> "Doesn't meet my particular use-case exactly" is not quite the same thing.
>
>
> I would have thought my outlined use-case was pretty basic and common ?
It is. If your objects are always laid out in about the same way, you
can use operator extraction for that:
postgres=# select
'[{"ID":"1","location_name":"Test"},{"ID":"2","location_name":"Examples"}]'::jsonb->1->'ID';
?column?
──────────
"2"
If you need to search in a more flexible way, then you need to look at
the jsquery extension; jsquery allows for arbitrary indexed
subdocument searching. see: https://github.com/akorotkov/jsquery
<compiling/installing>
postgres=# create extension jsquery;
CREATE EXTENSION
postgres=# select
'[{"ID":"1","location_name":"Test"},{"ID":"2","location_name":"Examples"}]'::jsonb
@@ '*.ID = "2"';
?column?
──────────
t
(1 row)
Time: 0.480 ms
postgres=# select
'[{"ID":"1","location_name":"Test"},{"ID":"2","location_name":"Examples"}]'::jsonb
@@ '*.ID = "3"';
?column?
──────────
f
(1 row)
postgres=# create table foo as select
'[{"ID":"1","location_name":"Test"},{"ID":"2","location_name":"Examples"}]'::jsonb
as v;
SELECT 1
postgres=# create index on foo using gin (v jsonb_value_path_ops);
CREATE INDEX
postgres=# set enable_seqscan to false;
SET
Time: 0.676 ms
postgres=# explain select * from foo where v @@ '*.ID = "3"';
QUERY PLAN
─────────────────────────────────────────────────────────────────────────
Bitmap Heap Scan on foo (cost=76.00..80.01 rows=1 width=32)
Recheck Cond: (v @@ '*."ID" = "3"'::jsquery)
-> Bitmap Index Scan on foo_v_idx (cost=0.00..76.00 rows=1 width=0)
Index Cond: (v @@ '*."ID" = "3"'::jsquery)
(4 rows)
merlin
From | Date | Subject | |
---|---|---|---|
Next Message | Maciek Sakrejda | 2015-01-27 02:24:37 | Re: Versioning Schema SQL ideas needed |
Previous Message | Andres Freund | 2015-01-26 23:42:18 | Re: Logical decoding output plug-in questions |