MySQL JSON_CONTAINS and JSON_EXTRACT: A Comprehensive Guide

![MySQL Logo](

Introduction

In recent years, JSON (JavaScript Object Notation) has become increasingly popular as a format for storing and exchanging data. MySQL, one of the most widely used relational databases, has introduced several functions to handle JSON data. Two of these functions are JSON_CONTAINS and JSON_EXTRACT. In this article, we will dive deep into these functions, exploring their syntax, use cases, and how to use them effectively in your MySQL queries.

JSON_CONTAINS

The JSON_CONTAINS function is used to check whether a specified JSON value exists within a JSON document. It returns 1 if the value is found, and 0 otherwise.

The syntax of the JSON_CONTAINS function is as follows:

JSON_CONTAINS(json_doc, json_value[, path])
  • json_doc: The JSON document to search within.
  • json_value: The JSON value to search for.
  • path (optional): The path to search within the JSON document.

Let's illustrate the usage of JSON_CONTAINS with an example. Consider a table called employees with a JSON column named data, which stores employee information. We want to find all the employees whose data includes the skill "Java". Here's how we can achieve this using JSON_CONTAINS:

SELECT * FROM employees
WHERE JSON_CONTAINS(data, '"Java"', '$.skills');

In the above query, we specify the JSON document column data, the JSON value "Java", and the path to search within the JSON document $.skills. If the value "Java" exists within the "skills" array, the row will be returned.

JSON_EXTRACT

The JSON_EXTRACT function is used to extract a specific value from a JSON document. It returns the extracted value as a string or a JSON document.

The syntax of the JSON_EXTRACT function is as follows:

JSON_EXTRACT(json_doc, path[, path]...)
  • json_doc: The JSON document from which to extract the value.
  • path: The path to the value within the JSON document.

Let's understand the usage of JSON_EXTRACT with an example. Consider the same employees table as before. This time, we want to extract all the employee names from the JSON column data. Here's how we can achieve this using JSON_EXTRACT:

SELECT JSON_EXTRACT(data, '$.name') AS employee_name
FROM employees;

In the above query, we specify the JSON document column data and the path to the "name" value within the JSON document $.name. The JSON_EXTRACT function returns the extracted employee names as a result set.

Use Cases

Now that we understand the basics of JSON_CONTAINS and JSON_EXTRACT, let's explore some common use cases where these functions can be useful.

Filtering JSON Data

One of the main use cases of JSON_CONTAINS is filtering JSON data based on specific criteria. Suppose we have a table products with a JSON column attributes, which stores product attributes like color, size, and weight. We want to find all the products that have the color "red" and size "large". Here's how we can achieve this using JSON_CONTAINS:

SELECT *
FROM products
WHERE JSON_CONTAINS(attributes, '"red"', '$.color')
AND JSON_CONTAINS(attributes, '["large"]', '$.size');

In the above query, we use JSON_CONTAINS to check if the color is "red" and the size is "large" within the JSON document attributes. Only the products that match both criteria will be returned.

Extracting Nested Values

JSON_EXTRACT is commonly used to extract values from nested JSON structures. Consider a table orders with a JSON column items, which stores order details. Each order can have multiple items, and each item has a name, quantity, and price. We want to extract the total price for each order. Here's how we can achieve this using JSON_EXTRACT:

SELECT JSON_EXTRACT(items, '$[*].price') AS prices,
       JSON_EXTRACT(items, '$[*].quantity') AS quantities,
       JSON_EXTRACT(items, '$[*].price * $[*].quantity') AS total_price
FROM orders;

In the above query, we use JSON_EXTRACT to extract the prices and quantities of all items. Then, we use these extracted values to calculate the total price per order by multiplying the prices and quantities together.

Conclusion

In this article, we explored the JSON_CONTAINS and JSON_EXTRACT functions in MySQL. We learned about their syntax, use cases, and how to use them effectively in our queries. These functions provide powerful capabilities for handling JSON data within MySQL, allowing us to filter, extract, and manipulate JSON values with ease. By leveraging these functions, we can make the most out of JSON data in our relational database applications.

Remember, the efficient use of JSON_CONTAINS and JSON_EXTRACT can greatly