Re: jsonb and nested hstore

On Thu, Feb 27, 2014 at 1:28 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> 3) In it's current state jsonb is not very useful and we have to
> recognize that; it optimizes text json but OTOH covers, maybe 30-40%
> of what hstore offers. In particular, it's missing manipulation and
> GIST/GIN. The stuff it does offer however is how Andrew, Josh and
> others perceive the API will be used and I defer to them with the
> special exception of deserialization (the mirror of to_json) which is
> currently broken or near-useless in all three types. Andrew
> recognized that and has suggested a fix; even then to me it only
> matters to the extent that the API is clean and forward compatible.

It's missing manipulation (in the sense that the implicit cast
sometimes produces surprising results, in particular for operators
that return hstore), but it isn't really missing GiST/GIN support as
compared to hstore, AFAICT:

postgres=# select * from foo;
i
-------------------------------
{"foo": {"bar": "yellow"}}
{"foozzz": {"bar": "orange"}}
{"foozzz": {"bar": "orange"}}
(3 rows)

postgres=# select * from foo where i ? 'foo';
i
----------------------------
{"foo": {"bar": "yellow"}}
(1 row)

postgres=# explain analyze select * from foo where i ? 'foo';
QUERY PLAN
---------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on foo (cost=12.00..16.01 rows=1 width=32) (actual
time=0.051..0.051 rows=1 loops=1)
Recheck Cond: ((i)::hstore ? 'foo'::text)
Heap Blocks: exact=1
-> Bitmap Index Scan on hidxb (cost=0.00..12.00 rows=1 width=0)
(actual time=0.041..0.041 rows=1 loops=1)
Index Cond: ((i)::hstore ? 'foo'::text)
Planning time: 0.172 ms
Total runtime: 0.128 ms
(7 rows)

Now, it's confusing that it has to go through hstore, perhaps, but
that's hardly all that bad in and of itself. It may be a matter of
reconsidering how to make the two work together. Certainly, queries
like the following fail, because the parser thinks the rhs string is
an hstore literal, not a jsonb literal:

postgres=# select * from foo where i @> '{"foo":4}';
ERROR: 42601: bad hstore representation
LINE 1: select * from foo where i @> '{"foo":4}';
^
DETAIL: syntax error, unexpected STRING_P, expecting '}' or ',' at end of input
LOCATION: hstore_yyerror, hstore_scan.l:172

Other than that, I'm not sure in what sense you consider that jsonb is
"missing GIN/GiST". If you mean that it doesn't have some of the
capabilities that I believe are planned for the VODKA infrastructure
[1], which one might hope to have immediately available to index this
new nested structure, that is hardly a criticism of jsonb in
particular.

[1] http://www.pgcon.org/2014/schedule/events/696.en.html

--
Peter Geoghegan