* Robert Haas (robertmhaas@gmail.com) wrote:
> On Thu, Jan 29, 2015 at 5:30 PM, Andrew Dunstan <andrew@dunslane.net> wrote:
> > jsonb stores string values as postgres text values, with the unicode escapes
> > resolved, just as it also resolves numbers and booleans into their native
> > representation. If you want the input perfectly preserved, use json, not
> > jsonb. I think that's made pretty clear in the docs.
> >
> > so text->jsonb->text is not and has never been expected to be a noop.
>
> If you can't store text in a jsonb object and get it back out again,
> it doesn't seem like a very useful data type.
Hrm. That sentence strikes me as slightly confused.
You *can* store text in a jsonb object and get it back out again,
however, that's not the same as taking a JSON structure which is
represented as text, converting it to jsonb, and then converting it back
into a text representation.
> Where exactly do you think this is documented?
http://www.postgresql.org/docs/9.4/static/datatype-json.html
"Because the json type stores an exact copy of the input text, it will
preserve semantically-insignificant white space between tokens, as well
as the order of keys within JSON objects. Also, if a JSON object within
the value contains the same key more than once, all the key/value pairs
are kept. (The processing functions consider the last value as the
operative one.) By contrast, jsonb does not preserve white space, does
not preserve the order of object keys, and does not keep duplicate
object keys. If duplicate keys are specified in the input, only the last
value is kept."
There is further discussion about how the JSON primitive types are
mapped on to native PostgreSQL types, which both constrains what values
are valid and may impact the formatting of values, eg:
"One semantically-insignificant detail worth noting is that in jsonb,
numbers will be printed according to the behavior of the underlying
numeric type. In practice this means that numbers entered with E
notation will be printed without it, for example:"
That, overall, isn't really the issue at hand though, from what I
gather. The problem is that strings can be represented in JSON which
can't be represented in our 'text' datatype and therefeore in our jsonb
type (since jsonb converts it to a 'text' datatype underneath). There
were attempts to make it work anyway, but that ended up causing other
issues. In the end, I tend to agree with Tom on this one and, while it
really stinks to break things for folks who have just started using 9.4,
we're at least early enough on that we hopefully won't break it for
*too* many people.
Thanks,
Stephen