Invalid documents produce an error. JSON documents stored in JSON columns are converted to an internal format that permits quick read access to document elements. When the server later must read a JSON value stored in this binary format, the value need not be parsed from a text representation. The binary format is structured to enable the server to look up subobjects or nested values directly by key or array index without reading all values before or after them in the document. The space required to store a JSON document is roughly the same as for or ; see , for more information. It is important to keep in mind that the size of any JSON document stored in a JSON column is limited to the value of the system variable. When the server is manipulating a JSON value internally in memory, it can be larger than this; the limit applies when the server stores it. A JSON column cannot have a default value. Along with the JSON data type, a set of SQL functions is available to enable operations on JSON values, such as creation, manipulation, and searching. The following discussion shows examples of these operations. For details about individual functions, see. A set of spatial functions for operating on GeoJSON values is also available. JSON columns, like columns of other binary types, are not indexed directly; instead, you can create an index on a generated column that extracts a scalar value from the JSON column. See , for a detailed example. The MySQL optimizer also looks for compatible indexes on virtual columns that match JSON expressions. MySQL NDB Cluster 7. A maximum of 3 JSON columns per table is supported. The next few sections provide basic information regarding the creation and manipulation of JSON values. Keys in JSON objects must be strings. The next several paragraphs describe how MySQL handles JSON values provided as input. In MySQL, JSON values are written as strings. MySQL parses any string used in a context that requires a JSON value, and produces an error if it is not valid as JSON. Strings in other character sets are converted to utf8mb4 as necessary. For strings in the ascii or utf8 character sets, no conversion is needed because ascii and utf8 are subsets of utf8mb4. As an alternative to writing JSON values using literal strings, functions exist for composing JSON values from component elements. Instead, the result from is converted to a string when assigned to the variable. One way to insert this as a JSON object into the facts table is to use the MySQL function. If this mode is set, a single backslash instead of double backslashes can be used to insert the JSON object literal, and the backslashes are preserved. Normalization, Merging, and Autowrapping of JSON Values When a string is parsed and found to be a valid JSON document, it is also normalized: Members with keys that duplicate a key found earlier in the document are discarded even if the values differ. This is a known issue in MySQL 5. Bug 86866, Bug 26369555 MySQL also discards extra whitespace between keys, values, or elements in the original JSON document. To make lookups more efficient, it also sorts the keys of a JSON object. You should be aware that the result of this ordering is subject to change and not guaranteed to be consistent across releases. MySQL functions that produce JSON values see always return normalized values. Merging JSON Values In contexts that combine multiple arrays, the arrays are merged into a single array by concatenating arrays named later to the end of the first array. Path expressions are useful with functions that extract parts of or modify a JSON document, to specify where within that document to operate. The key name must be specified within double quotation marks if the name without quotes is not legal within path expressions for example, if it contains a space. Array positions are integers beginning with zero. As mentioned previously, path components that name keys must be quoted if the unquoted key name is not legal in path expressions. See , for more information. Some functions take an existing JSON document, modify it in some way, and return the resulting modified document. Path expressions indicate where in the document to make changes. The functions differ in how they handle existing and nonexisting values within the document. The document produced by evaluating one pair becomes the new value against which the next pair is evaluated. Comparison of JSON values takes place at two levels. The first level of comparison is based on the JSON types of the compared values. If the types differ, the comparison result is determined solely by which type has higher precedence. If the two values have the same JSON type, a second level of comparison occurs using type-specific rules. The following list shows the precedences of JSON types, from highest precedence to the lowest. The type names are those returned by the function. Types shown together on a line have the same precedence. Any value having a JSON type listed earlier in the list compares greater than any value having a JSON type listed later in the list. If the first N bytes of the two values are identical, the shorter value is ordered before the longer value. OPAQUE values are values that are not classified as one of the other types. If two values originally come from the MySQL DATETIME and TIMESTAMP types, respectively, they are equal if they represent the same point in time. If the arrays are not equal, their order is determined by the elements in the first position where there is a difference. The array with the smaller value in that position is ordered first. If all values of the shorter array are equal to the corresponding values in the longer array, the shorter array is ordered first. If the first N bytes of the two strings are identical, the shorter string is considered smaller than the longer string. For a general discussion of these types of numbers, see. That is, exact-value numbers are converted to approximate-value numbers. To provide the most consistent behavior across all rows, MySQL converts approximate-value numbers to exact-value numbers. The resulting ordering is consistent and does not lose precision for the exact-value numbers. For example, given the scalars 9223372036854775805, 9223372036854775806, 9223372036854775807 and 9. 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. 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. Geometry types The geometry value is converted into a JSON document by calling. Workaround: Pass the result of to. 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. For sorting, it can be beneficial to cast a JSON scalar to some other native MySQL type. Aggregation of JSON Values For aggregation of JSON values, SQL NULL values are ignored as for other data types. Non- NULL values are converted to a numeric type and aggregated, except for , , and. The conversion to number should produce a meaningful result for JSON values that are numeric scalars, although depending on the values truncation and loss of precision may occur. Conversion to number of other JSON values may not produce a meaningful result.
