On Wed, Jun 08, 2016 at 11:59:10AM +0900, Michael Paquier wrote:
> text to save some parsing cost. Still, an area where things are not
> under controll with json is the key ordering within the same nest
> level, which is on the contrary pre-defined in jsonb by the unicity of
> the key names, and that's not the case of json. Take this example:
>
> =# select row_to_json(r)::json from ( select relkind, oid::regclass as
> name from pg_class where relname = 'pg_class') r;
> row_to_json
> ------------------------------------
> {"relkind":"r", "name":"pg_class"}
> (1 row)
> =# select row_to_json(r)::jsonb from (select relkind, oid::regclass as
> name from pg_class where relname = 'pg_class') r;
> row_to_json
> --------------------------------------
> {"name": "pg_class", "relkind": "r"}
> (1 row)
> This kills any hope of comparison.
>
> So after thinking more on the matter, I'd rather give up on such
> patches and recommend doing ::jsonb::text to be able to do equality
> comparisons. If you want to get output similar to what jsonb is doing,
> a simple cast with ::jsonb looks rather the saner way to go. This
> induces more parsing cost, but that's the cost to pay...
I think two things are being somewhat conflated:
1. the look of our JSON structures - we should be consistent here
2. the ordering of our JSON structures internally - a related (for the above
use case issue) but, ultimately, seperate issue.
1 is easy to solve. JSON is JSON is JSON whether it's in a JSON data-type or
JSONB and so no matter which it should look the same.
The types JSON and JSONB, to my knowledge, are different in terms of
internal (wrt postgres) representation and what they promise:
"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."
- https://www.postgresql.org/docs/current/static/datatype-json.html
As you can see, the key ordering is already covered. If you care, stick to JSON.
If you don't, fly JSONB airlines.
I think it's still worth doing #1 above and I'm willing to do the patch
myself (it didn't occur to me how simple it was and I've been kicking myself
at losing the opportunity to contribute :( :) ) if need be and if it'll fly.
Andrew