Re: In need of some JSONB examples ?

On Sun, Jan 25, 2015 at 6:50 AM, Tim Smith
<randomdev4+postgres@gmail.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