To speed up the search process, let’s set an index for array data within a JSON-formatted column

This article introduces how to set an index for array data within JSON-formatted columns storing JSON data.

Firstly, setting an index on commonly queried columns is an effective way to speed up search processes.

However, storing JSON-formatted data in a database column and wanting to search specific array data within that JSON doesn’t typically speed up search processes even if you set an index, due to the usual specifications of indexing.

In such cases, when aiming to expedite the search process for array data within JSON-formatted columns, utilizing Multi-Valued Indexes would be beneficial.

By using Multi-Valued Indexes, it’s possible to set an index on array data stored in JSON format.

Let’s say the following data is stored in a JSON formatted ‘json_data_column’ column. As an assumption, let’s consider that the ‘json_data_column’ column contains JSON data in the same format. Furthermore, while it would be better to normalize the string data held in ‘skills->languages’ and ‘skills->frameworks’ with IDs, for now, let’s ignore that aspect.

{
    "id": 1,
    "skills": {
        "languages": [
            "PHP",
            "JavaScript",
            "Java"
        ],
        "frameworks": [
            "Laravel",
            "Vue.js",
            "Spring"
        ]
    }
}

If you want to improve the search speed for the array data under the ‘skills->languages’ key within the ‘json_data_column’ column, you can set an index as follows.

ALTER TABLE json_data_column ADD INDEX members_skills_languages_index( (CAST(skills->’$.languages’ AS UNSIGNED ARRAY)) );

This enables faster search processing for the data under ‘json_data_column.skills->languages’ key. However, it’s important to note that, similar to a regular index, there are search operations where the index won’t take effect. While ‘exact match search’ and ‘prefix match search’ benefit from the index, operations like ‘partial match search’ or ‘suffix match search,’ such as the LIKE search, won’t benefit from the index and will result in a full scan.

Let’s set up the appropriate index, implement the suitable search process, and ensure it results in quick hits.