JSON_EXTRACT not working when not all rows contain JSON

Question

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

Answer 1

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