Bug #78527 incomplete support and/or documentation of the JSON datatype
Submitted: 23 Sep 2015 7:54 Modified: 13 Oct 2015 10:01
Reporter: Roland Bouman Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.7.10, 8.0.0 OS:Any
Assigned to: Jon Stephens CPU Architecture:Any

[23 Sep 2015 7:54] Roland Bouman
Description:
According to https://dev.mysql.com/doc/refman/5.7/en/json.html, MySQL 5.7.8 "supports a native JSON data type".

I made a number of observations that may indicate either incomplete support or incomplete documentation.

- https://dev.mysql.com/doc/refman/5.7/en/data-type-defaults.html does not include any information about default values for JSON types.

If I try I get a confusing error message:

mysql> create table t(c json default 'null');
ERROR 1101 (42000): BLOB/TEXT column 'c' can't have a default value

- indexes / keys don't seem to be supported:

mysql> alter table t add constraint pk_t primary key (c);
ERROR 3152 (42000): JSON column 'c' cannot be used in key specification.
mysql> create index i on t(c);
ERROR 3152 (42000): JSON column 'c' cannot be used in key specification.

but such is not documented. This is important since other vendors that support a json type do offer some form of indexing.

- documentation on the JSON null value is almost absent, and it seems support for JSON null is lackluster. I can enter a null value:

insert into t values ('null');

...but I cannot formulate a query that retrieves them in a straightforward way. I have to resort to:

select * from t where c = JSON_EXTRACT('{"a": null}', '$.a');
 
- documentation on ordering and comparison (https://dev.mysql.com/doc/refman/5.7/en/json.html) has this confusing remark:

"For ascending (descending) sorts, SQL NULL orders before (after) all JSON values, including the JSON null literal."

Is it before or after? And where does the JSON null value sort at?

How to repeat:
see description

Suggested fix:
better support for json (null value, default values, indexes)
better docuentation.
[23 Sep 2015 8:27] MySQL Verification Team
Hello Roland,

Thank you for the report.
Verified as described with 5.7.10/5.8.0 builds.

Thanks,
Umesh
[23 Sep 2015 8:28] MySQL Verification Team
// 5.7.10/5.8.0

mysql> create table t(c json default 'null');
ERROR 1101 (42000): BLOB/TEXT column 'c' can't have a default value
mysql> show errors;
+-------+------+-------------------------------------------------+
| Level | Code | Message                                         |
+-------+------+-------------------------------------------------+
| Error | 1101 | BLOB/TEXT column 'c' can't have a default value |
+-------+------+-------------------------------------------------+
1 row in set (0.00 sec)

mysql>  alter table t add constraint pk_t primary key (c);
ERROR 3152 (42000): JSON column 'c' cannot be used in key specification.
mysql> create index i on t(c);
ERROR 3152 (42000): JSON column 'c' cannot be used in key specification.
mysql> insert into t values ('null');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t;
+------+
| c    |
+------+
| null |
+------+
1 row in set (0.00 sec)

mysql> insert into t values (null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t;
+------+
| c    |
+------+
| null |
| NULL |
+------+
2 rows in set (0.00 sec)

mysql> select * from t where c = JSON_EXTRACT('{"a": null}', '$.a');
+------+
| c    |
+------+
| null |
+------+
1 row in set (0.00 sec)

mysql>
[23 Sep 2015 10:07] Knut Anders Hatlen
Hi Roland,

Thanks for the feedback.

> - https://dev.mysql.com/doc/refman/5.7/en/data-type-defaults.html does not include any information about default values for JSON types.
>
> If I try I get a confusing error message:
>
> mysql> create table t(c json default 'null');
> ERROR 1101 (42000): BLOB/TEXT column 'c' can't have a default value

Agreed. The manual should list this limitation, and the error message should be improved.

> - indexes / keys don't seem to be supported:
> [ ... ]
> but such is not documented. This is important since other vendors that support a json type do offer some form of indexing.

Currently JSON values can only be indexed by declaring a generated column that extracts the information that should be indexed, and creating an index on the generated column. It would be good if the JSON data type section of the manual spelled this out.

> - documentation on the JSON null value is almost absent, and it seems support for JSON null is lackluster. I can enter a null value:
>
> insert into t values ('null');
>
> ...but I cannot formulate a query that retrieves them in a straightforward way. I have to resort to:
>
> select * from t where c = JSON_EXTRACT('{"a": null}', '$.a');

Yes, in the lack of special SQL syntax for the JSON null literal, one has to use workarounds like the one you found. Might be less cryptic to say CAST('null' AS JSON). It's probably more efficient to use the JSON_TYPE function to look for the JSON null literal, though:

  WHERE JSON_TYPE(c) = 'NULL'

> - documentation on ordering and comparison (https://dev.mysql.com/doc/refman/5.7/en/json.html) has this confusing remark:
>
> "For ascending (descending) sorts, SQL NULL orders before (after) all JSON values, including the JSON null literal."
>
> Is it before or after?

This sentence attempts to say that in ascending sorts (ORDER BY json_val ASC), SQL NULL orders before all JSON values, and in descending sorts (ORDER BY json_val DESC) it orders after all JSON values. Could be made clearer, I agree.

> And where does the JSON null value sort at?

The JSON null literal sorts as smaller than any other JSON value. This is explained in the "Comparison and Ordering of JSON Values" section, https://dev.mysql.com/doc/refman/5.7/en/json.html#json-comparison . Look for the paragraph that starts with "The following list shows the precedences of JSON types".
[23 Sep 2015 12:22] Roland Bouman
@Umesh, thanks for verifying! 
@Knut, thanks for the clarification, much appreciated :)

re default:

> Agreed. The manual should list this limitation, and the error message should be improved.

Ok. So should a separate bug be filed that focuses on only that problem? If os, will you or will I?

re indexes:

> Currently JSON values can only be indexed by declaring a generated column that extracts the information that should be indexed, and creating an index on the generated column.

Thanks! I have to admit I didn't keep track of these improvements, and I didn't think of that yet. Although it would be nice to have a more seamless integration of json type and indexes, I can certainly agree that this offers enough possibilities for any practical purposes. Thanks for pointing it out! 
 
> It would be good if the JSON data type section of the manual spelled this out.

Agreed! I would expect to see a "Indexing JSON columns" section with the other documentation about the JSON type. In the indexing doc there could be a short para that only explains the limitation of creating a index directly on a json doc, and includes a link to the relevant section on indexing in the JSON chapter. 

(But I'm sure the docs team will know how to manage)

> Might be less cryptic to say CAST('null' AS JSON). 

Agreed, thanks!

> It's probably more efficient to use the JSON_TYPE function to look for the JSON null literal, though: WHERE JSON_TYPE(c) = 'NULL'

Also good, thanks!

I have another suggestion. When I first glanced through the description of JSON_OBJECT, I expected it to be something just like JSON.parse as described in Ecmascript. (i.e., parse a JSON string and turn it into a an actual (javascript) object). 

So I expected that JSON_OBJECT would be able to accept a single argument, and turn that into its JSON representation. Now, it turns out that it does not - it either accepts no arguments at all, or pairs of arguments that end up as key/values in the newly created object.

My suggestion would be to extend the implementation of JSON_OBJECT so that in case of a single argument, it works exactly like the single argument version of JSON.parse as described by ecmascript (see: https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/JSON/pars...)

Example usage:

JSON_OBJECT(NULL) -> null (JSON null)
JSON_OBJECT('{"key": "value"}') -> {"key": "value"}
JSON_OBJECT("String") -> "String" (JSON string value)
JSON_OBJECT(5) -> 5 (JSON number)

It would not bite the curent implementation, since the single argument implementation is not used (or in fact an error currently).

Alternatively, a new function JSON_PARSE() could be created, that only accepts a single argument, and which works exactly like ECMAscripts single argument version of JSON.parse.

In addition, this would make it very convenient for application developers to store any valid JSON - not just documents. Thoughts? 

Re ordering:

> This sentence attempts to say that in ascending sorts (ORDER BY json_val ASC), SQL NULL orders before all JSON values, and in descending sorts (ORDER BY json_val DESC) it orders after all JSON values. Could be made clearer, I agree.

Ok. Should a separate bug be filed for a doc improvement request?

> This is explained in the "Comparison and Ordering of JSON Values" section, https://dev.mysql.com/doc/refman/5.7/en/json.html#json-comparison . Look for the paragraph that starts with "The following list shows the precedences of JSON types".

Sorry, missed that. Thanks.
[23 Sep 2015 14:37] Knut Anders Hatlen
I think we can keep this as a single bug report for now. What we have currently is: Improve one specific error message, and clarify various sections of the manual. If some new behaviour is requested, that should probably be put in a separate bug report for easier tracking.

Regarding the suggested extensions to JSON_OBJECT:

> My suggestion would be to extend the implementation of JSON_OBJECT so that in case of a single argument, it works exactly like the single argument version of JSON.parse as described by ecmascript (see: https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/JSON/pars...)
>
> Example usage:
>
> JSON_OBJECT(NULL) -> null (JSON null)

All of the examples in the above URL take string arguments, so I think this one meant to say:

JSON_OBJECT('null') -> null (JSON null)

> JSON_OBJECT('{"key": "value"}') -> {"key": "value"}
> JSON_OBJECT("String") -> "String" (JSON string value)

And here:

JSON_OBJECT('"String"') -> "String" (JSON string value)

> JSON_OBJECT(5) -> 5 (JSON number)

With those modifications, the single-argument JSON_OBJECT(value) (or JSON_PARSE(value)) would behave the same way as CAST(value AS JSON). So I don't think it would provide any new functionality. See also the table "JSON Conversion Rules" in https://dev.mysql.com/doc/refman/5.7/en/json.html, which explains how CASTs to and from JSON work. Does that address the use case you had in mind?
[23 Sep 2015 15:17] Roland Bouman
Hi @Knut, 

ok - I played a bit with CAST(x as JSON) and I'm now convinced you're right - no extra functionality required.

That said, I feel the manual should probably dedicate a section or so on CAST(X as JSON) too - It should probably have an entry in "Functions that create JSON values" because that's where I'd be looking (https://dev.mysql.com/doc/refman/5.7/en/json-creation-functions.html)

I suppose it would be fine if that entry refers to the conversion table in https://dev.mysql.com/doc/refman/5.7/en/json.html and adds a few examples to illustrate.

Thanks again!
[24 Sep 2015 14:45] Paul DuBois
re: "For ascending (descending) sorts, SQL NULL orders before (after) all JSON values, including the JSON null literal."

This means for ascending, it's before, for descending, it's after. The nonparenthesized words go together, as do the parenthesized words.
[24 Sep 2015 15:07] Roland Bouman
Paul, I think Knut was more successful in decoding the riddle than I was. Would it hurt to make 2 sentences out of it, or else maybe add an example?
[6 Oct 2015 18:11] Jon Stephens
Documented fix in the MySQL 5.7.10 and 5.8.0 as follows:

    The error message returned when trying to define a BLOB, TEXT,
    JSON, or GEOMETRY column with a default value (Error 1101,
    ER_BLOB_CANT_HAVE_DEFAULT) referred to BLOB and TEXT columns
    only. The same error applies to any of these four types when
    trying to use DEFAULT with it in a column definition; the
    corresponding error message now makes this clear by referring to
    JSON and GEOMETRY columns as well.

Closed.
[6 Oct 2015 18:16] Jon Stephens
Now that the server portion of this bug has been closed, I'm reopening it as a Docs bug and assigning to myself to handle the remainder.

As a start, I've noted in the 5.7 Manual that JSON and GEOMETRY columns don't support DEFAULT (under "Data Type Default Values" and "CREATE TABLE Syntax").
[7 Oct 2015 16:47] Jon Stephens
Hi Roland!

Update: I've added some more admonitions regarding JSON not having a default and not supporting indexes, as well as a fairly detailed example of using a generated column to provide an indirect index for such a column--the example should appear shortly at 

https://dev.mysql.com/doc/refman/5.7/en/create-table.html#create-table-secondary-indexes-v...

More work on the docs issues are in progress. Thanks for all the suggestions.

j.
[7 Oct 2015 16:53] Roland Bouman
Hey John!

How's life? Miss you man :)

Thanks for the update - much appreciated.
[13 Oct 2015 10:01] Jon Stephens
Hi Roland! Long time no see... Someone should do something about that.

I reviewed this again and added some pointers between different sections covering the topics you brought up as well as another brief example. I think everything you've brought up in this bug report has been addressed, so I'm going to go ahead and close it.

Thanks!!

jon.
[18 Jun 2016 21:28] Omer Barnir
Posted by developer:
 
Reported version value updated to reflect release name change from 5.8 to 8.0