BUG #14178: output of jsonb_object and json_object doesn't match textually

On Tue, Jun 7, 2016 at 10:42 AM, <ap@zip.com.au> wrote:

I think json_object should provide similar output. Consistency is nice
aesthetically and as being in line with the principle of least surprise.
Its lack may lead to possible unpleasant results if anyone tries to deal
with a JSON structure as TEXT (for whatever reason).

That's a debatable point, and as any output is legal json it does not
actually hurt one way or another. However, as json_object has been
introduced at the same time as jsonb, I'd tend as well to think that
consistency is a good idea if possible, see the patch attached.
--
Michael

On Tue, Jun 7, 2016 at 2:40 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:

On Tue, Jun 7, 2016 at 10:42 AM, <ap@zip.com.au> wrote:

I think json_object should provide similar output. Consistency is nice
aesthetically and as being in line with the principle of least surprise.
Its lack may lead to possible unpleasant results if anyone tries to deal
with a JSON structure as TEXT (for whatever reason).

That's a debatable point, and as any output is legal json it does not
actually hurt one way or another. However, as json_object has been
introduced at the same time as jsonb, I'd tend as well to think that
consistency is a good idea if possible, see the patch attached.

(It's a shame that json_build_object and json_object_agg also don't
agree on where to put whitespace...)

--
Thomas Munro
http://www.enterprisedb.com

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

On Tue, Jun 7, 2016 at 3:48 PM, Thomas Munro
<thomas.munro@enterprisedb.com> wrote:

On Tue, Jun 7, 2016 at 2:40 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:

On Tue, Jun 7, 2016 at 10:42 AM, <ap@zip.com.au> wrote:

I think json_object should provide similar output. Consistency is nice
aesthetically and as being in line with the principle of least surprise.
Its lack may lead to possible unpleasant results if anyone tries to deal
with a JSON structure as TEXT (for whatever reason).

That's a debatable point, and as any output is legal json it does not
actually hurt one way or another. However, as json_object has been
introduced at the same time as jsonb, I'd tend as well to think that
consistency is a good idea if possible, see the patch attached.

(It's a shame that json_build_object and json_object_agg also don't
agree on where to put whitespace...)

Right, missed that, as well as json_object_two_arg.
--
Michael

On Tue, Jun 7, 2016 at 6:54 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:

On Tue, Jun 7, 2016 at 3:48 PM, Thomas Munro
<thomas.munro@enterprisedb.com> wrote:

On Tue, Jun 7, 2016 at 2:40 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:

On Tue, Jun 7, 2016 at 10:42 AM, <ap@zip.com.au> wrote:

I think json_object should provide similar output. Consistency is nice
aesthetically and as being in line with the principle of least surprise.
Its lack may lead to possible unpleasant results if anyone tries to deal
with a JSON structure as TEXT (for whatever reason).

That's a debatable point, and as any output is legal json it does not
actually hurt one way or another. However, as json_object has been
introduced at the same time as jsonb, I'd tend as well to think that
consistency is a good idea if possible, see the patch attached.

(It's a shame that json_build_object and json_object_agg also don't
agree on where to put whitespace...)

Right, missed that, as well as json_object_two_arg.

One more difference is that json_object_agg uses "{ " and " }" while
the other functions use "{" and "}".

--
Thomas Munro
http://www.enterprisedb.com

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

On Tue, Jun 7, 2016 at 4:55 PM, Thomas Munro
<thomas.munro@enterprisedb.com> wrote:

On Tue, Jun 7, 2016 at 6:54 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:

On Tue, Jun 7, 2016 at 3:48 PM, Thomas Munro
<thomas.munro@enterprisedb.com> wrote:

On Tue, Jun 7, 2016 at 2:40 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:

On Tue, Jun 7, 2016 at 10:42 AM, <ap@zip.com.au> wrote:

I think json_object should provide similar output. Consistency is nice
aesthetically and as being in line with the principle of least surprise.
Its lack may lead to possible unpleasant results if anyone tries to deal
with a JSON structure as TEXT (for whatever reason).

That's a debatable point, and as any output is legal json it does not
actually hurt one way or another. However, as json_object has been
introduced at the same time as jsonb, I'd tend as well to think that
consistency is a good idea if possible, see the patch attached.

(It's a shame that json_build_object and json_object_agg also don't
agree on where to put whitespace...)

Right, missed that, as well as json_object_two_arg.

One more difference is that json_object_agg uses "{ " and " }" while
the other functions use "{" and "}".

OK, that would make the entries generated a bit more compact.. Are
there other opinions on the matter?
--
Michael

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

On Tue, Jun 7, 2016 at 8:58 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:

OK, that would make the entries generated a bit more compact.. Are
there other opinions on the matter?

I have noticed as well that array elements separated by a comma use a
space after the comma with jsonb, and not with json. Still, doing
improvements in this area would be useful if a user is interested in
doing direct comparison of jsonb with json after casting them into
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...
--
Michael

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

On Wed, Jun 08, 2016 at 08:32:41PM +1000, AP wrote:

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.

(docs snipped)

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.

Well I got bored and did it anyway. I checked all the functions in the doc page
and modified accordingly. My patch is, obviously, a superset of Michael's. :)

Interesting tidbit wrt this bit in the docs for JSON:

"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."
- https://www.postgresql.org/docs/current/static/datatype-json.html

json_strip_nulls doesn't appear to adhere to that:

moo=# select json_strip_nulls('{ "a":"b", "c":["a",2,-5,null], "d":{"e":"f", "true": false, "false": true, "val": null}}');
json_strip_nulls
-----------------------------------------------------------------------
{"a":"b","c":["a",2,-5,null],"d":{"e":"f","true":false,"false":true}}
(1 row)

Not sure if this should be fixed in-code or a note be made in the docs
of the exceptions.

Anyhow, patch attached.

Andrew