Thanks for the extra feedback Merlin. I'll look into it a bit more,
JSONB obviously needs a bit of experimentation in the lab to get my
query syntax right !
On 27 January 2015 at 00:13, Merlin Moncure <mmoncure@gmail.com> wrote:
> 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
