BUG #14354: Wrong interpretation of JSON 'null'

On 10/5/16, kouber@gmail.com <kouber@gmail.com> wrote:

The following bug has been logged on the website:

Bug reference: 14354
Logged by: Kouber Saparev
Email address: kouber@gmail.com
PostgreSQL version: 9.4.5
Operating system: Fedora
Description:

Trying to pass 'null' to jsonb_each_text() results in an ERROR, while at the
same time it is considered a valid JSON value. This behaviour seems quite
inconsistent - either such a value should be considered invalid in general,
either the function should treat it as a normal NULL instead.

db=# select 'null'::jsonb;
jsonb
-------
null
(1 row)

db=# select jsonb_each_text('null'::jsonb);
ERROR: cannot call jsonb_each_text on a non-object

db=# select jsonb_each_text(NULL);
jsonb_each_text
-----------------
(0 rows)

It is not a bug. It works as expected.

1. NULL::jsonb is not the same as 'null'::jsonb
PG's NULL (not jsonb's 'null'!) as input returns NULL output.

2. Argument for jsonb_each_text should be a jsonb with an
_json-object_ at top-level (see types of primitives by [1]https://www.postgresql.org/docs/9.6/static/datatype-json.html#JSON-TYPE-MAPPING-TABLE and [2]https://www.postgresql.org/docs/9.6/static/datatype-json.html#JSON-KEYS-ELEMENTS -- Best regards, Vitaly Burovoy),
e.g. '{"key1":"value1", "key2":"value2"}' to produce pairs (key,
value).

Your example has jsonb value with a null-value at top-level. The same
exception is raised if you send a json-string as an input:
db=# select jsonb_each_text('"str"'::jsonb);
ERROR: cannot call jsonb_each_text on a non-object

P.S.: what you're expecting from the "select
jsonb_each_text('null'::jsonb)" call?

[1]: https://www.postgresql.org/docs/9.6/static/datatype-json.html#JSON-TYPE-MAPPING-TABLE
[2]: https://www.postgresql.org/docs/9.6/static/datatype-json.html#JSON-KEYS-ELEMENTS -- Best regards, Vitaly Burovoy
--
Best regards,
Vitaly Burovoy

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

Okay, I also saw in the source code that it is looking for an _json-object_
(whatever that means) and is throwing that error otherwise. The thing is -
in my perception the string 'null' , being valid json(b), should also be
treated as valid _json-object_, isn't it? Otherwise we are ending up with
valid json's and valid json-object's, and then perhaps it is a
documentation issue to clarify the difference between the two?

I would expect from select "jsonb_each_text('null'::jsonb)" to return an
empty result set (just the same as an SQL NULL), as indeed this is the
meaning - 'null' is an empty, but still a valid json object.

I am using jsonb_each_text() in another stored procedure I wrote myself to
make json_diff(jsonb, jsonb), and it is failing for the entire multimillion
table because of a few rows that had this 'null' string value. So I had to
explicitly alter its invokation to jsonb_each_text(nullif($1,
'null')::jsonb), which is a work-around, but yet I felt its an
inconsistency in PostgreSQL itself.

2016-10-05 17:08 GMT+03:00 Vitaly Burovoy <vitaly.burovoy@gmail.com>:

Kouber Saparev <kouber@gmail.com> writes:

Okay, I also saw in the source code that it is looking for an _json-object_
(whatever that means) and is throwing that error otherwise. The thing is -
in my perception the string 'null' , being valid json(b), should also be
treated as valid _json-object_, isn't it?

No. A JSON "object" is something with field names and values, like a
Perl hash. JSON also has scalars and arrays; those are JSON values,
but not JSON objects. 'null' is a scalar, I think, although for some
purposes it might be better to view it as a fourth primitive kind of
JSON value.

jsonb_each_text() needs to work on a JSON object because otherwise its
return convention of returning a set of field names and values makes
no sense. What would you imagine jsonb_each_text('2'::jsonb) ought
to do? Similarly, there's not really any sensible interpretation
of jsonb_each_text('null'::jsonb).

... I felt its an
inconsistency in PostgreSQL itself.

The distinction between objects and other kinds of JSON values is
drawn in the JSON standard; we did not make it up. See
http://rfc7159.net/rfc7159

regards, tom lane

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

On 10/6/16, Kouber Saparev <kouber@gmail.com> wrote:

2016-10-05 17:08 GMT+03:00 Vitaly Burovoy <vitaly.burovoy@gmail.com>:

On 10/5/16, kouber@gmail.com <kouber@gmail.com> wrote:

The following bug has been logged on the website:

Bug reference: 14354
Logged by: Kouber Saparev
Email address: kouber@gmail.com
PostgreSQL version: 9.4.5
Operating system: Fedora
Description:

Trying to pass 'null' to jsonb_each_text() results in an ERROR, while
at the same time it is considered a valid JSON value. This behaviour
seems quite inconsistent - either such a value should be considered
invalid in general, either the function should treat it as a normal NULL
instead.

db=# select 'null'::jsonb;
jsonb
-------
null
(1 row)

db=# select jsonb_each_text('null'::jsonb);
ERROR: cannot call jsonb_each_text on a non-object

db=# select jsonb_each_text(NULL);
jsonb_each_text
-----------------
(0 rows)

It is not a bug. It works as expected.

1. NULL::jsonb is not the same as 'null'::jsonb
PG's NULL (not jsonb's 'null'!) as input returns NULL output.

2. Argument for jsonb_each_text should be a jsonb with an
_json-object_ at top-level (see types of primitives by [1] and [2]),
e.g. '{"key1":"value1", "key2":"value2"}' to produce pairs (key,
value).

Your example has jsonb value with a null-value at top-level. The same
exception is raised if you send a json-string as an input:
db=# select jsonb_each_text('"str"'::jsonb);
ERROR: cannot call jsonb_each_text on a non-object

P.S.: what you're expecting from the "select
jsonb_each_text('null'::jsonb)" call?

Okay, I also saw in the source code that it is looking for an _json-object_
(whatever that means)

It means mapping "key-value".

and is throwing that error otherwise. The thing is -
in my perception the string 'null' , being valid json(b),

Yes, it is. But JSON primitive 'null' is not a mapping "key-value".

should also be treated as valid _json-object_, isn't it?

No. Unfortunately, JavaSctipt (its 2 letters are in the acronym
"JSON") uses the word "object" instead of "mapping" or "dictionary"
that leads to misunderstanding.
The string 'null' is a valid JSON object in meaning it can be parsed
according to its rules, but it is not JSON-object in meaning of
"mapping".

Otherwise we are ending up with
valid json's and valid json-object's, and then perhaps it is a
documentation issue to clarify the difference between the two?

The table by [1]https://www.postgresql.org/docs/current/static/datatype-json.html#JSON-TYPE-MAPPING-TABLE mentions it.
The second note from the bottom in [2]https://www.postgresql.org/docs/current/static/functions-json.html pays your attention to it.

I would expect from select "jsonb_each_text('null'::jsonb)" to return an
empty result set (just the same as an SQL NULL), as indeed this is the
meaning - 'null' is an empty, but still a valid json object.

But your expectation is wrong since JSON value is not empty, it has
the single primitive of 'nulltype'.

I am using jsonb_each_text() in another stored procedure I wrote myself to
make json_diff(jsonb, jsonb), and it is failing for the entire multimillion
table because of a few rows that had this 'null' string value. So I had to
explicitly alter its invokation to jsonb_each_text(nullif($1,
'null')::jsonb), which is a work-around, but yet I felt its an
inconsistency in PostgreSQL itself.

It is confusing, but it is the same as if someone asks you to:
1) count letters in an unknown phrase,
2) count letters in the 'unknown phrase'.

In the first case your answer is "I don't know" -- it is SQL's NULL, thereas
in the second case your answer is "13 without a space".

The only difference between those cases are quotes which defines or
not "objects" (strings).

[1]: https://www.postgresql.org/docs/current/static/datatype-json.html#JSON-TYPE-MAPPING-TABLE
[2]: https://www.postgresql.org/docs/current/static/functions-json.html

--
Best regards,
Vitaly Burovoy

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

It looks like I do not know enough about the JSON type and I was treating
it always like an object with keys and values. You are absolutely correct,
now I see that I can really:

db=# select '2'::jsonb;
jsonb
-------
2
(1 row)

Which explains everything.

Cheers,

2016-10-06 15:47 GMT+03:00 Tom Lane <tgl@sss.pgh.pa.us>: