MySQL 8.0 Reference Manual Including MySQL NDB Cluster 8.0

12.17.1 JSON Function Reference

Table 12.21 JSON Functions

Name Description
JSON_ARRAY() Create JSON array
JSON_ARRAY_APPEND() Append data to JSON document
JSON_ARRAY_INSERT() Insert into JSON array
-> Return value from JSON column after evaluating path; equivalent to JSON_EXTRACT().
JSON_CONTAINS() Whether JSON document contains specific object at path
JSON_CONTAINS_PATH() Whether JSON document contains any data at path
JSON_DEPTH() Maximum depth of JSON document
JSON_EXTRACT() Return data from JSON document
->> Return value from JSON column after evaluating path and unquoting the result; equivalent to JSON_UNQUOTE(JSON_EXTRACT()).
JSON_INSERT() Insert data into JSON document
JSON_KEYS() Array of keys from JSON document
JSON_LENGTH() Number of elements in JSON document
JSON_MERGE() (deprecated 8.0.3) Merge JSON documents, preserving duplicate keys. Deprecated synonym for JSON_MERGE_PRESERVE()
JSON_MERGE_PATCH() Merge JSON documents, replacing values of duplicate keys
JSON_MERGE_PRESERVE() Merge JSON documents, preserving duplicate keys
JSON_OBJECT() Create JSON object
JSON_OVERLAPS() Compares two JSON documents, returns TRUE (1) if these have any key-value pairs or array elements in common, otherwise FALSE (0)
JSON_PRETTY() Print a JSON document in human-readable format
JSON_QUOTE() Quote JSON document
JSON_REMOVE() Remove data from JSON document
JSON_REPLACE() Replace values in JSON document
JSON_SCHEMA_VALID() Validate JSON document against JSON schema; returns TRUE/1 if document validates against schema, or FALSE/0 if it does not
JSON_SCHEMA_VALIDATION_REPORT() Validate JSON document against JSON schema; returns report in JSON format on outcome on validation including success or failure and reasons for failure
JSON_SEARCH() Path to value within JSON document
JSON_SET() Insert data into JSON document
JSON_STORAGE_FREE() Freed space within binary representation of JSON column value following partial update
JSON_STORAGE_SIZE() Space used for storage of binary representation of a JSON document
JSON_TABLE() Return data from a JSON expression as a relational table
JSON_TYPE() Type of JSON value
JSON_UNQUOTE() Unquote JSON value
JSON_VALID() Whether JSON value is valid
MEMBER OF() Returns true (1) if first operand matches any element of JSON array passed as second operand, otherwise returns false (0)

MySQL supports two aggregate JSON functions JSON_ARRAYAGG() and JSON_OBJECTAGG(). See Section 12.20, “Aggregate (GROUP BY) Functions”, for descriptions of these.

MySQL also supports pretty-printing of JSON values in an easy-to-read format, using the JSON_PRETTY() function. You can see how much storage space a given JSON value takes up, and how much space remains for additional storage, using JSON_STORAGE_SIZE() and JSON_STORAGE_FREE(), respectively. For complete descriptions of these functions, see Section 12.17.8, “JSON Utility Functions”.