How to query JSON in MySQL with JSON_CONTAINS

You can query a JSON document in MySQL to find content within it with:

• The JSON_CONTAINS function that will return if a JSON document is contained within another JSON document. The function returns 1 if the document is contained, 0 elsewhere.
• The JSON_CONTAINS function that will return if a JSON document path is contained within another JSON document. The function returns 1 if the document is contained, 0 elsewhere.

NOTE: more info is available in the MySQL JSON functions documentation page

👉 Want a FREE MySQL database?👈
🦀 Check Aiven’s FREE plans! 🦀

⚡️ Want to optimize your SQL query with AI? ⚡️
Check Aiven SQL Query Optimizer!

⚡️ Want a Fully MySQL optimized Database? ⚡️
🐧 Check Aiven AI database optimizer! Powered by EverSQL 🐧

The dataset

The dataset is the following:

{
    "id": 778,
    "shop": "Luigis Pizza",
    "name": "Edward Olson",
    "phoneNumbers":
        ["(935)503-3765x4154","(935)12345"],
    "address": "Unit 9398 Box 2056 DPO AP 24022",
    "image": null,
    "pizzas": [
        {
            "pizzaName": "Salami",
            "additionalToppings": ["🥓", "🌶️"]
        },
        {
            "pizzaName": "Margherita",
            "additionalToppings": ["🍌", "🌶️", "🍍"]
        }
    ]
}

[
    {
        "pizzaName": "Salami",
        "additionalToppings": ["🥓", "🌶️"]
    },
    {
        "pizzaName": "Margherita",
        "additionalToppings": ["🍌", "🌶️", "🍍"]
    }
]

create table test(id serial primary key, json_data json);

insert into test(json_data) values (
'{
    "id": 778,
    "shop": "Luigis Pizza",
    "name": "Edward Olson",
    "phoneNumbers":
        ["(935)503-3765x4154","(935)12345"],
    "address": "Unit 9398 Box 2056 DPO AP 24022",
    "image": null,
    "pizzas": [
        {
            "pizzaName": "Salami",
            "additionalToppings": ["🥓", "🌶️"]
        },
        {
            "pizzaName": "Margherita",
            "additionalToppings": ["🍌", "🌶️", "🍍"]
        }
    ]
}');

Query JSON in MySQL with JSON_CONTAINS

To query a JSON document you can use the JSON_CONTAINS function. To find if the data in contains a pizzaName equal to Margherita you can:

select 
    JSON_CONTAINS(json_data,'{"pizzaName":"Margherita"}', '$.pizzas') result from test;

Where

• json_data is the JSON column
• {"pizzaName":"Margherita"} is the JSON document to be found
• the last parameter $.pizzas defines the search path: the JSON_CONTAINS will only check for the presence of {"pizzaName":"Margherita"} within the pizzas item in the JSON document.

Result

+--------+
| result |
+--------+
|      1 |
+--------+

The result shows that {"pizzaName":"Margherita"} is present. If we search for {"pizzaName":"Hawaii"} with:

select 
    JSON_CONTAINS(json_data,'{"pizzaName":"Hawaii"}', '$.pizzas') result from test;

We’ll get the 0

+--------+
| result |
+--------+
|      0 |
+--------+

Query JSON in MySQL with JSON_CONTAINS_PATH

The above function works well if we can pinpoint the exact document and value we want to search. If, on the other side, we want to look for the existance of one or more paths in the document, we can use the JSON_CONTAINS_PATH function. As example, if we need to search for the presence of shop or additionalToppings we can write:

select 
    JSON_CONTAINS_PATH(json_data,'one','$.shop','$.pizzas.additionalToppings') result from test;

• json_data is the JSON column
• one dictates if we are looking to match only one path or all the paths (in this case we are ok to match at least one of the paths)
• $.shop looks for the shop item within the JSON document
• $.pizzas.additionalToppings looks for the additionalToppings item within pizzas in the JSON document

Result

+--------+
| result |
+--------+
|      1 |
+--------+

If we try with a different set, to check if we have both (check the second parameter to all) the shop and the drinks items with:

select 
    JSON_CONTAINS_PATH(json_data,'all','$.shop','$.drinks') result from test;

We get the expected 0 since the drinks item is not present in the doc

+--------+
| result |
+--------+
|      0 |
+--------+

Review all the JSON MySQL use-cases listed in the main page