Re: BUG #14354: Wrong interpretation of JSON 'null' - Mailing list 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>:

> 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?
>
> [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
>