On Thu, Feb 27, 2014 at 1:28 PM, Merlin Moncure <mmonc...@gmail.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers