We’re using MySQL as the relational data store in most of our projects. MySQL is a solid foundation, even with complex data types, like JSON. Sometimes we’re integrating apps with external APIs. We typically store responses from external services when requesting from them or synchronizing data with them. These JSON responses go into a meta column for later reference.

During an app refactoring, we wanted to extract a property from the existing JSON payload of each database row. We used MySQL’s JSON search functions while preparing the SQL queries.

At some point, we noticed that not all rows contained valid JSON. This tutorial shows you how to handle invalid JSON data in MySQL.

MySQL Series Overview

Handling Invalid JSON Text in MySQL

We used the MySQL JSON_EXTRACT function to fetch a specific property from a column containing JSON values. Processing our SQL query resulted in the following error message:

Invalid JSON text in argument 1 to function json_extract: "Invalid value." at position 0  

Find All Rows With Invalid JSON

At first, we had to find all rows containing invalid JSON data. At that stage of development, we would be fine by skipping the rows containing invalid JSON. But that would push the process of fixing the data to a later point.

We used the MySQL JSON_VALID function to find all rows containing invalid JSON data in a given column. For example, the following sample table has invalid JSON in a row with id=3:

| id | json_column | |----|------------------------------------| | 1 | { "title": "Hello Future Studio" } | | 2 | NULL | | 3 | { "Invalid JSON" ] |

You can find the row by using JSON_VALID as a where condition:

SELECT *  
FROM your_table  
WHERE NOT JSON_VALID(json_column)  
-- matches row with ID 3

This query returned all rows with invalid JSON. From here it’s up to you and your data schema to find and find the JSON issues.

Find All Rows With Not-Null JSON Value

We also had rows with NULL values in the JSON column. That could happen because of a bug in the application or an issue before saving the JSON value to the database. We also wanted to find all rows with NULL values to re-fetch them again.

| id | json_column                        | 
|----|------------------------------------|
| 1  | { "title": "Hello Future Studio" } |
| 2  | NULL                               |
| 3  | { "Invalid JSON" ]                 |

We re-processed the given rows and moved on to extracting the JSON values.

Extracting JSON Values When Present

You can use MySQL JSON functions in the SELECT part of a statement and also as a filter in a WHERE condition. We were interested in a title column of the stored JSON blob. We wanted to extract the title for each row when the JSON blob contained valid values. The JSON_EXTRACT function helps you do that with a specific notation.

Here’s the sample query fetching the title for each row. We’re combining the JSON_VALID and JSON_EXTRACT columns to only fetch rows with valid JSON data and where the title is present at the root level of the JSON structure:

SELECT  
    id, 
    JSON_EXTRACT(json_column, '$.title') as title
FROM your_table  
WHERE  
    JSON_VALID(json_column)
    AND JSON_EXTRACT(json_column, '$.title') IS NOT NULL

That’s it!

---

Mentioned Resources

• MySQL docs for JSON search functions
• MySQL docs for the JSON_VALID function
• MySQL docs for the JSON_EXTRACT function