MySQL JSON Functions & Operators

Below is a list of the JSON functions and operators available in MySQL.

NameDescription
->Inline path operator that returns a value from a JSON column after evaluating the path. Equivalent to JSON_EXTRACT().
->>Same as the -> operator except that it unquotes the value.
JSON_ARRAY()Creates a JSON array.
JSON_ARRAYAGG()Aggregates a result set as a single JSON array.
JSON_ARRAY_APPEND()Appends data to a JSON document.
JSON_ARRAY_INSERT()Inserts a new value into a JSON array.
JSON_CONTAINS()Tests whether or not a specified value is found in the given JSON document or, optionally, at the specified path within the document.
JSON_CONTAINS_PATH()Tests whether or not a specified path is found in the given JSON document.
JSON_DEPTH()Returns the maximum depth of a JSON document.
JSON_EXTRACT()Returns data from a JSON document. The actual data returned is determined by the path you provide as an argument.
JSON_INSERT()Inserts a value into a JSON document and returns the result.
JSON_KEYS()Returns keys from the top level value of a JSON object.
JSON_LENGTH()Returns the length of a JSON document.
JSON_MERGE()Merges JSON documents, preserving duplicate keys. Deprecated synonym for JSON_MERGE_PRESERVE().
JSON_MERGE_PATCH()Performs an RFC 7396 compliant merge of two or more JSON documents, without preserving members having duplicate keys.
JSON_MERGE_PRESERVE()Merges JSON documents, while preserving duplicate keys. Synonym for (the deprecated) JSON_MERGE().
JSON_OBJECT()Creates a JSON object from a list of key/value pairs.
JSON_OBJECTAGG()Aggregates a result set as a single JSON array whose elements consist of the rows. 
JSON_OVERLAPS()Compares two JSON documents, returns true (1) if these have any key-value pairs or array elements in common, otherwise it returns false (0).
JSON_PRETTY()Prints a JSON document in human-readable format.
JSON_QUOTE()Quotes a string as a JSON value by wrapping it with double quote characters and escaping interior quote and other characters, then returning the result as a utf8mb4 string. Typically used to produce a valid JSON string literal for inclusion within a JSON document.
JSON_REMOVE()Removes data from a JSON document.
JSON_REPLACE()Replaces values in JSON document.
JSON_SCHEMA_VALID()Validates a JSON document against a JSON schema and returns TRUE/1 if the document is valid, FALSE/0 if it is not.
JSON_SCHEMA_VALIDATION_REPORT()Validates a JSON document against a JSON schema and returns a report in JSON format on outcome of the validation including success or failure and reasons for failure.
JSON_SEARCH()Returns the path to a given string in a JSON document.
JSON_SET()Inserts or updates values in a JSON document and returns the result.
JSON_STORAGE_FREE()Shows how much storage space was freed up after a JSON Document was updated.
JSON_STORAGE_SIZE()Returns the number of bytes used to store the binary representation of a JSON document.
JSON_TABLE()Converts JSON data into a relational form.
JSON_TYPE()Returns the type of a JSON value.
JSON_UNQUOTE()Removes quotes from a JSON value.
JSON_VALID()Tests whether a value is valid JSON.
JSON_VALUE()Extracts a value from JSON document at location pointed to by the path provided.
MEMBER OF()Tests whether or not a value is a member of a given JSON array.
Returns true (1) if the array contains the value, and false (0) if it doesn’t.