Converting between JSON and non-JSON values For comparison of JSON and non-JSON values, the non-JSON value is converted to JSON according to the rules in the following table, then the values compared as described previously. Converting between JSON and non-JSON values The following table provides a summary of the rules that MySQL follows when casting between JSON values and values of other types: Table 11.3 JSON Conversion Rules other type CAST(other type AS JSON) CAST(JSON AS other type) JSON No change No change utf8 character type ( utf8mb4 , utf8 ,  ascii ) The string is parsed into a JSON value. The JSON value is serialized into a  utf8mb4  string. Other character types Other character encodings are implicitly converted to  utf8mb4 and treated as described for utf8 character type. The JSON value is serialized into a  utf8mb4  string, then cast to the other character encoding. The result may not be meaningful. NULL Results in a  NULL  value of type JSON. Not applicable. Geometry types The geometry value is converted into a JSON document by calling ST_AsGeoJSON() . Illegal operation. Workaround: Pass the result of CAST( json_val  AS CHAR)  to ST_GeomFromGeoJSON() . All other types Results in a JSON document consisting of a single scalar value. Succeeds if the JSON document consists of a single scalar value of the target type and that scalar value can be cast to the target type. Otherwise, returns  NULL  and produces a warning. ORDER BY  and  GROUP BY  for JSON values works according to these principles: •Ordering of scalar JSON values uses the same rules as in the preceding discussion. •For ascending sorts, SQL  NULL  orders before all JSON values, including the JSON null literal; for descending sorts, SQL  NULL  orders after all JSON values, including the JSON null literal. •Sort keys for JSON values are bound by the value of the  max_sort_length  system variable, so keys that differ only after the first  max_sort_length  bytes compare as equal. •Sorting of nonscalar values is not currently supported and a warning occurs. ORDER BY CAST(JSON_EXTRACT(jdoc, ’$.id’) AS UNSIGNED) For sorting, it can be beneficial to cast a JSON scalar to some other native MySQL type. For example, if a column named  jdoc  contains JSON objects having a member consisting of an  id  key and a nonnegative value, use this expression to sort by  id  values: If there happens to be a generated column defined to use the same expression as in the  ORDER BY , the MySQL optimizer recognizes that and considers using the index for the query execution plan. See Section 8.3.11, “Optimizer Use of Generated Column Indexes” . 2083