MySQL JSON_CONTAINS_PATH and JSON_EXTRACT

Introduction

In recent years, the use of JSON in data storage and manipulation has become increasingly popular. MySQL, being one of the most widely used relational database management systems, has added several functions to handle JSON data efficiently. Two of these functions are JSON_CONTAINS_PATH and JSON_EXTRACT. In this article, we will explore these functions and understand their usage with code examples.

JSON_CONTAINS_PATH

JSON_CONTAINS_PATH is a MySQL function that allows you to check whether a JSON document contains a specific path. It returns 1 if the path exists in the document and 0 otherwise.

The syntax for JSON_CONTAINS_PATH is as follows:

JSON_CONTAINS_PATH(json_doc, one_or_all_paths, path[, path]...)
  • json_doc is the JSON document being searched.
  • one_or_all_paths is a string that specifies how the function should handle multiple paths. It can be either 'one' or 'all'.
  • path is the path to search for in the JSON document.

Let's consider an example to understand the usage of JSON_CONTAINS_PATH. Suppose we have a table named users with a column data that stores JSON data for each user. We want to find all the users who have a specific address.

CREATE TABLE users (
    id INT,
    data JSON
);

INSERT INTO users VALUES
(1, '{"name": "John", "address": {"city": "New York", "state": "NY"}}'),
(2, '{"name": "Alice", "address": {"city": "San Francisco", "state": "CA"}}'),
(3, '{"name": "Bob", "address": {"city": "Los Angeles", "state": "CA"}}');

To find the users with the address city as "San Francisco," we can use the following query:

SELECT id, JSON_EXTRACT(data, '$.name') AS name
FROM users
WHERE JSON_CONTAINS_PATH(data, 'one', '$.address.city');

The result of the above query will be:

| id | name  |
|----|-------|
| 2  | Alice |

Here, JSON_CONTAINS_PATH is used to check if the address.city path exists in the data column of the users table. Since only one path is specified, we use 'one' as the one_or_all_paths argument.

JSON_EXTRACT

JSON_EXTRACT is another useful function in MySQL that allows you to extract data from a JSON document. It returns the value associated with a specified path in the JSON document.

The syntax for JSON_EXTRACT is as follows:

JSON_EXTRACT(json_doc, path[, path]...)
  • json_doc is the JSON document from which data is extracted.
  • path is the path to the desired data in the JSON document.

Continuing with our previous example, suppose we want to extract the city and state from the address of each user in the users table.

SELECT JSON_EXTRACT(data, '$.address.city') AS city,
       JSON_EXTRACT(data, '$.address.state') AS state
FROM users;

The result of the above query will be:

| city           | state |
|----------------|-------|
| New York       | NY    |
| San Francisco  | CA    |
| Los Angeles    | CA    |

Here, JSON_EXTRACT is used to extract the values associated with the address.city and address.state paths in the data column of the users table.

Sequence Diagram

Let's now visualize the flow of the above examples using a sequence diagram. The following diagram illustrates the steps involved in executing the queries and obtaining the desired results.

sequenceDiagram
    participant Client
    participant MySQL

    Client->>MySQL: SELECT id, JSON_EXTRACT(data, '$.name') AS name FROM users WHERE JSON_CONTAINS_PATH(data, 'one', '$.address.city')
    MySQL-->>Client: Result (id: 2, name: Alice)

    Client->>MySQL: SELECT JSON_EXTRACT(data, '$.address.city') AS city, JSON_EXTRACT(data, '$.address.state') AS state FROM users
    MySQL-->>Client: Result (city: New York, state: NY)
    MySQL-->>Client: Result (city: San Francisco, state: CA)
    MySQL-->>Client: Result (city: Los Angeles, state: CA)

The sequence diagram shows the communication between the client and the MySQL server during the execution of the queries.

Conclusion

In this article, we explored the usage of two useful MySQL functions, JSON_CONTAINS_PATH and JSON_EXTRACT, for working with JSON data. We learned how to check whether a JSON document contains a specific path using JSON_CONTAINS_PATH and how to extract data from a JSON document using JSON_EXTRACT. These functions provide powerful tools for handling JSON data efficiently within MySQL.