Database.Guide – Page 382

JSON_EXTRACT() – Return Data from a JSON Document in MySQL

November 19, 2019July 27, 2018 by Ian

In MySQL, the JSON_EXTRACT() function returns data from a JSON document. The actual data returned is determined by the path you provide as an argument.

You provide the JSON document as the first argument, followed by the path of the data to return. You can also provide multiple paths to return data from multiple places in the document.

JSON_CONTAINS_PATH() Examples in MySQL

November 19, 2019July 26, 2018 by Ian

In MySQL, the JSON_CONTAINS_PATH() function tests whether or not a specified path is found in the given JSON document.

If the path is found, JSON_CONTAINS_PATH() returns 1, otherwise it returns 0.

JSON_CONTAINS() Examples in MySQL

November 19, 2019July 26, 2018 by Ian

In MySQL, the JSON_CONTAINS() function tests whether or not a specified value is found in the given JSON document or, optionally, at the specified path within the document.

If the value is found, JSON_CONTAINS() returns 1, otherwise it returns 0.

JSON_VALID() – Test for Valid JSON in MySQL

November 19, 2019July 25, 2018 by Ian

When using MySQL, you can use the JSON_VALID() function to test whether or not a string expression contains valid JSON.

If the expression contains valid JSON, JSON_VALID() returns 1, otherwise it returns 0.

JSON_UNQUOTE() – Remove Quotes from a JSON Document in MySQL

November 19, 2019July 25, 2018 by Ian

In MySQL, the JSON_UNQUOTE() function “unquotes” a JSON document and returns the result as a utf8mb4 string.

You provide the JSON document as an argument, and the function will do the rest.

JSON_SET() vs JSON_INSERT() vs JSON_REPLACE() in MySQL: What’s the Difference?

November 19, 2019July 24, 2018 by Ian

In MySQL, there are a number of functions to help you work with JSON documents. These include the JSON_SET(), JSON_INSERT(), and JSON_REPLACE() functions.

These three functions are related, yet they do differ slightly.

JSON_SET() – Insert or Update Values in a JSON Document in MySQL

February 14, 2020July 24, 2018 by Ian

In MySQL, the JSON_SET() function inserts or updates values in a JSON document and returns the result.

You provide the JSON document as the first argument, followed by the path to insert into, followed by the value to insert. You can provide multiple path/value pairs if you need to update multiple values.

JSON_REPLACE() – Replace Values in a JSON Document in MySQL

November 19, 2019July 23, 2018 by Ian

In MySQL, the JSON_REPLACE() function replaces values in a JSON document and returns the result.

You provide the JSON document as the first argument, followed by the path to replace data at, followed with the value to replace the existing value with.

You can replace data at multiple places in the document if required. To do this, simply provide multiple path/value pairs as required.

JSON_REMOVE() – Remove Data from a JSON Document in MySQL

November 19, 2019July 23, 2018 by Ian

In MySQL, the JSON_REMOVE() function removes data from a JSON document and returns the result.

You provide the JSON document as the first argument, followed by the path to remove data from. You can provide multiple paths if required.

JSON_MERGE_PATCH() vs JSON_MERGE_PRESERVE() in MySQL: What’s the Difference?

November 19, 2019July 22, 2018 by Ian

MySQL includes a number of functions for working with JSON documents. Among these are the JSON_MERGE_PATCH() and JSON_MERGE_PRESERVE() functions.

Both of these functions merge two or more JSON documents and return the result. However, there are certain cases where these functions will return a different result. You need to be aware of this before including them in any of your queries.