MySQL JSON Functions

JSON (JavaScript Object Notation) documents can include data that is efficiently accessed by MySQL thanks to the native JSON data type that is supported by RFC 7159. When compared to placing JSON-format strings in a string column, the JSON data type offers the following benefits:

• JSON documents saved in JSON columns are automatically validated. Documents that are invalid cause an error.
• Improved storage format JSON documents are translated to an internal format that enables quick read access to document elements when they are stored in JSON columns. The value does not need to be processed from a text representation when the server needs to read a JSON value that was previously stored in this binary format. Because of the way the binary format is set up, the server can directly seek up subobjects or nested values using a key or array index without having to read all values before or after them in the document.

The next few sections provide basic information regarding the creation and manipulation of JSON values.

JSON_ARRAY() Function

The JSON_ARRAY() function in MySQL produces a JSON array with every parameter.

Using JSON_ARRAY()
The syntax for the JSON ARRAY() function in MySQL is as follows:

JSON_ARRAY(value1, value2, …)

Parameters

value1, value2, ... →Optional
Some values will be elements in a JSON array.

Return Value

There may be some conversions happening here:

• TRUE is converted to true.
• FALSE is converted to false.
• NULL is converted to null.
• A date, or time, or datetime value is converted to a string.

Example:

JSON_CONTAINS() Function

The JSON_CONTAINS() function in MySQL determines whether a JSON document includes another JSON document.

Using JSON_CONTAINS()
The syntax for the JSON_CONTAINS() function in MySQL is as follows:

• JSON_CONTAINS(target_json, candidate_json) 
• JSON_CONTAINS(target_json, candidate_json, path)

Parameters

1. target_json→Required
    A JSON document.
2. candidate_json→Required
   The included JSON document.
3. path→Optional
   The path expression.

Return Value

If the JSON document candidate_json is included in the JSON document target_json, the function will return 1, else it will return 0. If the path option is given, JSON_CONTAINS() determines whether candidate JSON is present in the section indicated by the route.

The JSON_CONTAINS() function will return NULL in the following cases:

• The specified path does not exist in the JSON document.
• Any parameter is NULL.

Example:

JSON_EXTRACT() Function

The JSON EXTRACT() function in MySQL extracts and returns the data that is indicated by the path expression in the JSON document.

Using JSON_EXTRACT()
The syntax for the JSON_EXTRACT() function in MySQL is as follows:

• JSON_EXTRACT(json, path, …)

Parameters

1. json→Required
   A JSON document.
2. path →Required
   You should specify at least one path expression.

Return Value

All values found in the JSON document that match the path expression are returned by the JSON_EXTRACT() function. Return the value if the path expression matches a value; if it matches more than one value, return an array of all the values.

The JSON_EXTRACT() function will return NULL in the following cases:

• If the specified path does not exist in the JSON document.
• If any parameter is NULL.

Example:

JSON_OBJECT() Function

The JSON_OBJECT() function in MySQL produces a JSON object with all the key-value pairs supplied by the parameters.

Using JSON_OBJECT()
The syntax for the JSON_OBJECT() function in MySQL is as follows:

• JSON_OBJECT(KEY, VALUE[, KEY2, VALUE2, …])

Parameters

1. key →Required
   The Key in the object.
2. value →Required
   The value of key in the object.

Return Value

The JSON_OBJECT() function returns a JSON object with all the key-value pairs after evaluating each key-value combination in the parameters.

JSON_OBJECT() changes non-string keys to strings because every key in the JSON object is a string. We should utilise a string type key to assure the program’s stability.

Example:

Conclusion:

In this article, we have gone through some functions of JSON that can be used in MySQL. We have discussed saving data as an array and object in a document. Checking if the JSON document includes another JSON document and extracting JSON data from document. I hope this article is useful to all