JSON_EXTRACT not working when not all rows contain JSON

user_4296895 0
Viewed 12835

I am trying to run JSON EXTRACT but get the following error:

Data truncation: Invalid JSON text in argument 1 to function json_extract: "The document is empty." at position 0.

select id, JSON_EXTRACT(content, "$.pathway_id") from reports
mysql
1 Answers

You can prevent this error by using JSON_VALID to make sure the field contains JSON as follows:

select id, CASE WHEN JSON_VALID(content) THEN JSON_EXTRACT(content, "$.pathway_id") ELSE null END from reports
user_42968950
Related