MySQL provides us with a bunch of functions and operators that allow us to search JSON documents for various things, such as specific values, paths, keys, etc.

Here are seven functions and three operators that search JSON documents in MySQL.

The JSON_CONTAINS() Function

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.

Example:

SET @target = '{"a": 1, "b": 2, "c": {"d": 3}}';
SET @candidate = '{"c": {"d": 3}}';
SELECT JSON_CONTAINS(@target, @candidate);

Result:

1

In this case, the candidate (second argument) was found in the target document (first argument) and so 1 was returned.

We can also specify a path within the document by adding a third argument.

The JSON_CONTAINS_PATH() Function

The JSON_CONTAINS_PATH() function returns 0 or 1 to indicate whether a JSON document contains data at a given path or paths.

Example:

SET @doc = '{"a": 1, "b": 2, "c": {"d": 3}}';
SELECT JSON_CONTAINS_PATH(@doc, 'one', '$.a');

Result:

1

In this case the path exists in the JSON document and we get a return value of 1.

The JSON_EXTRACT() Function

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

Example:

SELECT JSON_EXTRACT('{"Name": "Bart", "Age": 10}', '$.Name');

Result:

"Bart"

Here, I returned the value that’s stored at the Name key.

It’s possible to specify multiple paths by separating them with commas.

The -> Operator

The MySQL -> operator is an alias for the two-argument syntax of JSON_EXTRACT().

Suppose we have the following table called Pets:

SELECT * 
FROM Pets;

Result:

+-------+-----------------------------------------------+
| PetId | PetName                                       |
+-------+-----------------------------------------------+
|     1 | {"name": "Wag", "type": "Dog", "weight": 20}  |
|     2 | {"name": "Bark", "type": "Dog", "weight": 10} |
|     3 | {"name": "Meow", "type": "Cat", "weight": 7}  |
+-------+-----------------------------------------------+

We can see that the PetName column contains JSON.

We can use the -> operator to extract data from one of the fields within those JSON documents:

SELECT PetName -> '$.name'
FROM Pets;

Result:

+---------------------+
| PetName -> '$.name' |
+---------------------+
| "Wag"               |
| "Bark"              |
| "Meow"              |
+---------------------+

The ->> Operator

The MySQL ->> operator is very similar to the -> operator, except that it removes any quotes from string values that are extracted.

Let’s run the same query from the previous example, except that we’ll replace the -> operator with the ->> operator:

SELECT PetName ->> '$.name'
FROM Pets;

Result:

+----------------------+
| PetName ->> '$.name' |
+----------------------+
| Wag                  |
| Bark                 |
| Meow                 |
+----------------------+

We get the same data, but this time the double quotes have been removed.

The JSON_KEYS() Function

The JSON_KEYS() function returns keys from the top level value of a JSON object. The keys are returned as a JSON array, or, if a path argument is given, the top-level keys from the selected path.

Example:

SELECT JSON_KEYS('{"Name": "Bart", "Age": 10}');

Result:

["Age", "Name"]

So, rather than return the actual values (like in most of the other functions and operators), this function returns the key names themselves.

The JSON_OVERLAPS() Function

The JSON_OVERLAPS() function tests whether or not two JSON documents have any key-value pairs or array elements in common.

Example:

SELECT JSON_OVERLAPS( '[ "Cat", "Dog", "Horse" ]', '[ "Cat", "Fish" ]' );

Result:

1

In this case, there was an overlap (because Cat is present in both JSON documents) and so 1 was returned.

The JSON_SEARCH() Function

The JSON_SEARCH() function returns the path to a given string in a JSON document.

Example:

SELECT JSON_SEARCH('{"Name": "Bart", "Age": 10}', 'one', 'Bart');

Result:

"$.Name"

Here, I wanted to know the path that the value Bart belongs to. The JSON_SEARCH() function told me that it resides at $.Name.

The JSON_VALUE() Function

The JSON_VALUE() function extracts a value from a JSON document at the specified path.

Example:

SELECT JSON_VALUE( '{ "name" : "Wag", "type" : "Dog" }', '$.type' );

Result:

Dog

Notice that this function removes the double quotes from the string value (as opposed to the JSON_EXTRACT() function, which leaves the double quotes in).

However, the JSON_VALUE() function allows us to specify a return type, and if we specify a return type of JSON, then we’ll get the double quotes.

The MEMBER OF() Operator

The MEMBER OF() operator tests whether or not a value is a member of a given JSON array. It returns 1 if the value is a member, and 0 if it’s not.

Example:

SELECT 'Cat' MEMBER OF( '[ "Cat", "Dog", "Bird" ]' );

Result:

1

In this case, the value is a member of the array, and so 1 is returned.

The MEMBER OF() operator was added in MySQL 8.0.17.