Thread: BUG #14354: Wrong interpretation of JSON 'null'
VGhlIGZvbGxvd2luZyBidWcgaGFzIGJlZW4gbG9nZ2VkIG9uIHRoZSB3ZWJz aXRlOgoKQnVnIHJlZmVyZW5jZTogICAgICAxNDM1NApMb2dnZWQgYnk6ICAg ICAgICAgIEtvdWJlciBTYXBhcmV2CkVtYWlsIGFkZHJlc3M6ICAgICAga291 YmVyQGdtYWlsLmNvbQpQb3N0Z3JlU1FMIHZlcnNpb246IDkuNC41Ck9wZXJh dGluZyBzeXN0ZW06ICAgRmVkb3JhCkRlc2NyaXB0aW9uOiAgICAgICAgCgpU cnlpbmcgdG8gcGFzcyAnbnVsbCcgdG8ganNvbmJfZWFjaF90ZXh0KCkgcmVz dWx0cyBpbiBhbiBFUlJPUiwgd2hpbGUgYXQgdGhlCnNhbWUgdGltZSBpdCBp cyBjb25zaWRlcmVkIGEgdmFsaWQgSlNPTiB2YWx1ZS4gVGhpcyBiZWhhdmlv dXIgc2VlbXMgcXVpdGUKaW5jb25zaXN0ZW50IC0gZWl0aGVyIHN1Y2ggYSB2 YWx1ZSBzaG91bGQgYmUgY29uc2lkZXJlZCBpbnZhbGlkIGluIGdlbmVyYWws CmVpdGhlciB0aGUgZnVuY3Rpb24gc2hvdWxkIHRyZWF0IGl0IGFzIGEgbm9y bWFsIE5VTEwgaW5zdGVhZC4NCg0KZGI9IyBzZWxlY3QgJ251bGwnOjpqc29u YjsNCiBqc29uYg0KLS0tLS0tLQ0KIG51bGwNCigxIHJvdykNCg0KZGI9IyBz ZWxlY3QganNvbmJfZWFjaF90ZXh0KCdudWxsJzo6anNvbmIpOw0KRVJST1I6 ICBjYW5ub3QgY2FsbCBqc29uYl9lYWNoX3RleHQgb24gYSBub24tb2JqZWN0 DQoNCmRiPSMgc2VsZWN0IGpzb25iX2VhY2hfdGV4dChOVUxMKTsNCiBqc29u Yl9lYWNoX3RleHQNCi0tLS0tLS0tLS0tLS0tLS0tDQooMCByb3dzKQoK
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
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 >
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
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
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>: > 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 >