Re: jsonb and nested hstore

On 02/25/2014 09:45 AM, Bruce Momjian wrote:
>> It hurts our adoption substantially to confuse developers. We need to
>> recommend one type over the other, hence "Use jsonb unless you need X".
>> Merlin is pushing the type of multivariable comparison where *I*
>> wouldn't be able to make sense of which one I should pick, let alone
>> some web developer who's just trying to get a site built. That sort of
>> thing *really* doesn't help our users.
>
> I agree it would be nice to have something simple, like "Use JSON if you
> wish to just store/retrieve entire JSON structures, and JSONB if you
> wish to do any kind of lookup or manipulation of JSON values on the
> server".

(to clarify below: "json" refers to the current varlena datatype; JSON
refers to JSON serialized data).

I don't think that's decisive enough, which is why I wrote the doc the
way I did. The problem is that most users would prefer that we tell
them which one to use, which is why I want to structure the doc as "Use
jsonb unless you need one of these things", or more specifically:

In general, most applications will find it advantageous to store
JSON data
as <type>jsonb</type>, as jsonb is more efficient when using JSON
manipulation functions, and will
support future advanced json index, operator and search features. The
<type>json</type> will primarily be useful for applications which
need to
preserve exact formatting of the input JSON, or users with existing
<type>json</type> columns which they do not want to convert to
<type>jsonb</type>.

Part of my reason for wanting to recommend jsonb over json is in the
context of the third storage option for JSON, namely TEXT. The only
things which distinguish json from TEXT for JSON storage are validation
and a set of json manipulation functions. jsonb works with the
manipulation functions better/faster, causing the old json type to start
looking like more of a DOMAIN over TEXT than a real type comparatively.
In other words, if you ask the question "Why would I want to use json
instead of either jsonb or TEXT", the answer becomes quite narrow.

Possibly I should expand the little chart and add a column for TEXT?
It's a viable option for storing JSON data, especially if you store a
lot of broken JSON or fragments.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com