Problem records of querying VARCHAR type JSON data in MySQL

In database design, sometimes we store JSON data in VARCHAR or TEXT type fields. Although this method is flexible, special attention is required when querying. This article will explain in detail how to effectively query JSON data stored as VARCHAR type in MySQL.

1. Problem background

When JSON data is stored in a VARCHAR column, the common data formats are as follows:

[
  {"id":"1905555466980773889","hasPermission":true},
  {"id":"1905547884060835841","hasPermission":false}
]

We need to query whether this JSON array contains objects with a specific ID.

2. MySQL JSON function

MySQL version 5.7+ provides rich JSON handling functions, even if the data type is VARCHAR, we can still use these functions as long as the content is valid JSON:

2.1 Commonly used JSON functions

JSON_CONTAINS(target, candidate[, path]): Check whether the JSON document contains a specific value
JSON_EXTRACT(json_doc, path): Extract values from JSON documents
JSON_OBJECT(key, val[, key, val]...): Create a JSON object
JSON_ARRAY(val[, val]...): Create a JSON array
JSON_VALID(json_doc): Verify that the string is a valid JSON

3. Query example

3.1 Basic Query

Query records containing specific IDs in the JSON array:

SELECT * FROM sys_user
WHERE 
  app_ids IS NOT NULL
  AND app_ids != ''
  AND JSON_CONTAINS(app_ids, JSON_OBJECT('id', '1905555466980773889'));

3.2 Query multiple IDs

SELECT * FROM sys_user
WHERE 
  app_ids IS NOT NULL
  AND app_ids != ''
  AND (
    JSON_CONTAINS(app_ids, JSON_OBJECT('id', '1905555466980773889'))
    OR JSON_CONTAINS(app_ids, JSON_OBJECT('id', '1905547884060835841'))
  );

3.3 Using JSON_OVERLAPS (MySQL 8.0+)

SELECT * FROM sys_user
WHERE 
  app_ids IS NOT NULL
  AND app_ids != ''
  AND JSON_OVERLAPS(
    app_ids, 
    JSON_ARRAY(
      JSON_OBJECT('id', '1905555466980773889'),
      JSON_OBJECT('id', '1905547884060835841')
    )
  );

3.4 Query records for specific permissions

SELECT * FROM sys_user
WHERE 
  app_ids IS NOT NULL
  AND app_ids != ''
  AND JSON_CONTAINS(
    app_ids, 
    JSON_OBJECT('id', '1905555466980773889', 'hasPermission', true)
  );

4. Avoid common mistakes

4.1 Null value processing

The JSON_CONTAINS function will report an error when processing NULL or empty strings, so these cases need to be ruled out first:

-- Wrong way
SELECT * FROM sys_user WHERE JSON_CONTAINS(app_ids, JSON_OBJECT('id', '123'));
-- The correct way to do it
SELECT * FROM sys_user 
WHERE 
  app_ids IS NOT NULL 
  AND app_ids != '' 
  AND JSON_CONTAINS(app_ids, JSON_OBJECT('id', '123'));

4.2 JSON format matching

Make sure that the second parameter structure of JSON_CONTAINS matches the structure in the target JSON:

-- Wrong way (Directly transferred ID String)
SELECT * FROM sys_user WHERE JSON_CONTAINS(app_ids, '"1905555466980773889"');
-- The correct way to do it (Create an object that matches the array element)
SELECT * FROM sys_user WHERE JSON_CONTAINS(app_ids, JSON_OBJECT('id', '1905555466980773889'));

4.3 Ensure JSON validity

Add JSON_VALID Check to make sure the field content is valid JSON:

SELECT * FROM sys_user
WHERE 
  app_ids IS NOT NULL
  AND app_ids != ''
  AND JSON_VALID(app_ids) = 1
  AND JSON_CONTAINS(app_ids, JSON_OBJECT('id', '1905555466980773889'));

5. Application in MyBatis Plus

5.1 Basic Query

LambdaQueryWrapper<SysUser> queryWrapper = new LambdaQueryWrapper<>();
(SysUser::getAppIds)
            .ne(SysUser::getAppIds, "")
            .apply("JSON_CONTAINS(app_ids, JSON_OBJECT('id', {0}))", "1905555466980773889");
List<SysUser> userList = (queryWrapper);

5.2 Multi-condition query

QueryWrapper&lt;SysUser&gt; queryWrapper = new QueryWrapper&lt;&gt;();
("app_ids")
            .ne("app_ids", "")
            .apply("JSON_VALID(app_ids) = 1")
            .apply("JSON_CONTAINS(app_ids, JSON_OBJECT('id', {0}))", "1905555466980773889");
// If you also need to filter according to permissions("JSON_CONTAINS(app_ids, JSON_OBJECT('id', {0}, 'hasPermission', {1}))", 
                   "1905555466980773889", true);

5.3 Query multiple IDs

LambdaQueryWrapper<SysUser> queryWrapper = new LambdaQueryWrapper<>();
(SysUser::getAppIds)
            .ne(SysUser::getAppIds, "")
            .and(w -> ("JSON_CONTAINS(app_ids, JSON_OBJECT('id', {0}))", "1905555466980773889")
                      .or()
                      .apply("JSON_CONTAINS(app_ids, JSON_OBJECT('id', {0}))", "1905547884060835841"));
List<SysUser> userList = (queryWrapper);

6. Performance optimization suggestions

Consider using the JSON type: If your MySQL version is 5.7+, consider using the native JSON type instead of VARCHAR, which gives you better performance and functional support.

Add an index: Although it is impossible to create an index for JSON content directly, the generated column and function index can be used:

ALTER TABLE sys_user ADD COLUMN app_id_extracted JSON 
  GENERATED ALWAYS AS (JSON_EXTRACT(app_ids, '$[*].id')) VIRTUAL;
ALTER TABLE sys_user ADD INDEX idx_app_id_extracted (app_id_extracted);

Regular maintenance: For large tables, regular OPTIMIZE TABLE helps maintain performance.

7. Summary

When querying JSON data of type VARCHAR in MySQL, the key is:

Use the JSON_CONTAINS function and construct the correct JSON structure for matching
Handle NULL and empty strings
Verify JSON validity
Add native SQL conditions using the apply method in MyBatis Plus

Proper use of these techniques can effectively query and process JSON data stored in VARCHAR.

This is the end of this article about querying VARCHAR type JSON data in MySQL. For more related mysql querying VARCHAR type JSON data content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!