Re: BUG #14354: Wrong interpretation of JSON 'null' - Mailing list pgsql-bugs
From | Vitaly Burovoy |
---|---|
Subject | Re: BUG #14354: Wrong interpretation of JSON 'null' |
Date | |
Msg-id | CAKOSWNnVZoRyQHTchbY7Xev1VvvptYXpxJq7TRWwNMiRe7ufSA@mail.gmail.com Whole thread Raw |
In response to | Re: BUG #14354: Wrong interpretation of JSON 'null' (Kouber Saparev <kouber@gmail.com>) |
List | 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] mentions it. The second note from the bottom in [2] 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
pgsql-bugs by date: