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

VGhlIGZvbGxvd2luZyBidWcgaGFzIGJlZW4gbG9nZ2VkIG9uIHRoZSB3ZWJz
aXRlOgoKQnVnIHJlZmVyZW5jZTogICAgICAxNDE3OApMb2dnZWQgYnk6ICAg
ICAgICAgIEFuZHJldyBQCkVtYWlsIGFkZHJlc3M6ICAgICAgYXBAemlwLmNv
bS5hdQpQb3N0Z3JlU1FMIHZlcnNpb246IDkuNS4xCk9wZXJhdGluZyBzeXN0
ZW06ICAgTGludXgsIERlYmlhbiwgSmVzc2llCkRlc2NyaXB0aW9uOiAgICAg
ICAgCgpIaSwNCg0KV2hlbiBwcmVzZW50aW5nIEpTT04gb3V0cHV0IGFzIFRF
WFQsIGpzb25fb2JqZWN0IHNlZW1zIHRvIGJlIHRoZSBvZGQgbXVwcGV0Cm91
dDoNCg0KZmllbGQ9IyBzZWxlY3QganNvbl9vYmplY3QoQVJSQVlbJ21vbycs
ICd3b29mJ10sIEFSUkFZWydjb3cnLAonZG9nJ10pOjp0ZXh0Ow0KICAgICAg
ICAgICBqc29uX29iamVjdCAgICAgICAgICAgDQotLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0NCiB7Im1vbyIgOiAiY293IiwgIndvb2YiIDog
ImRvZyJ9DQooMSByb3cpDQoNClRpbWU6IDAuNDI4IG1zDQpmaWVsZD0jIHNl
bGVjdCBqc29uYl9vYmplY3QoQVJSQVlbJ21vbycsICd3b29mJ10sIEFSUkFZ
Wydjb3cnLAonZG9nJ10pOjp0ZXh0Ow0KICAgICAgICAganNvbmJfb2JqZWN0
ICAgICAgICAgIA0KLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLQ0K
IHsibW9vIjogImNvdyIsICJ3b29mIjogImRvZyJ9DQooMSByb3cpDQoNClRp
bWU6IDAuMzA2IG1zDQoNCkkgdHJpZWQgYSBmZXcgZnVuY3Rpb25zIGFuZCBh
bGwgYXBwZWFyIHRvIGJlIGxlYXZpbmcgbm8gc3BhY2UgYmVmb3JlIHRoZQpj
b2xvbi4gRXZlbiB0b19qc29uOg0KDQpmaWVsZD0jIHNlbGVjdCB0b19qc29u
KCd7Im1vbyI6ICAgICAgICAiY293In0nOjpqc29uYik7DQogICAgdG9fanNv
biAgICAgDQotLS0tLS0tLS0tLS0tLS0tDQogeyJtb28iOiAiY293In0NCigx
IHJvdykNCg0KVGltZTogMC40MDAgbXMNCg0KQW5kIGNhc3Q6DQoNCmZpZWxk
PSMgc2VsZWN0IGNhc3QoJ3sibW9vIjogICAgICAgICJjb3cifSc6Ompzb25i
IGFzIGpzb24pOw0KICAgICAganNvbiAgICAgIA0KLS0tLS0tLS0tLS0tLS0t
LQ0KIHsibW9vIjogImNvdyJ9DQooMSByb3cpDQoNClRpbWU6IDAuMzk4IG1z
DQoNCkkgdGhpbmsganNvbl9vYmplY3Qgc2hvdWxkIHByb3ZpZGUgc2ltaWxh
ciBvdXRwdXQuIENvbnNpc3RlbmN5IGlzIG5pY2UgDQphZXN0aGV0aWNhbGx5
IGFuZCBhcyBiZWluZyBpbiBsaW5lIHdpdGggdGhlIHByaW5jaXBsZSBvZiBs
ZWFzdCBzdXJwcmlzZS4NCkl0cyBsYWNrIG1heSBsZWFkIHRvIHBvc3NpYmxl
IHVucGxlYXNhbnQgcmVzdWx0cyBpZiBhbnlvbmUgdHJpZXMgdG8gZGVhbA0K
d2l0aCBhIEpTT04gc3RydWN0dXJlIGFzIFRFWFQgKGZvciB3aGF0ZXZlciBy
ZWFzb24pLg0KDQpBbmRyZXcNCgoK

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

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

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

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

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

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