mysql: Invalid JSON text in argument 1 to function json_extract: "Invalid value." at posit-CodePudding

I am trying to extract json from database, but I am guessing there is malformed json there, and some of them is not valid json, or not exist at all, so I try to do something like this:

SELECT JSON_EXTRACT(json_column, '$.payload.from') as was,
JSON_EXTRACT(json_column, '$.payload.to') as now
FROM orders
WHERE JSON_EXTRACT(json_column, '$.action') = 'change'
AND JSON_EXTRACT(json_column, '$.payload.from') IS NOT NULL
AND JSON_EXTRACT(json_column, '$.payload.to') IS NOT NULL
AND JSON_EXTRACT(json_column, '$.action') IS NOT NULL
AND JSON_VALID(json_column) = 1;

but I am still getting error: "Invalid JSON text in argument 1 to function json_extract: "Invalid value." at position 0."

any thoughts on how to skip this error for malformed JSON row?

CodePudding user response：

To start with, you should probably try to get a picture of what the invalid data is so that it's not the product of a bug in the logic inserting new data.

To see what's "broken", try;

SELECT json_column FROM order WHERE NOT JSON_VALID(json_column)

If you're satisfied that the invalid data is not actually of importance, you can use a CTE to only parse fields with valid values, that will make sure the validity is checked before the data is parsed;

WITH orders AS (
  SELECT json_column FROM orders WHERE JSON_VALID(json_column)
)
SELECT JSON_EXTRACT(json_column, '$.payload.from') as was,
       JSON_EXTRACT(json_column, '$.payload.to') as now
  FROM orders
 WHERE JSON_EXTRACT(json_column, '$.action') = 'change'
   AND JSON_EXTRACT(json_column, '$.payload.from') IS NOT NULL
   AND JSON_EXTRACT(json_column, '$.payload.to') IS NOT NULL
   AND JSON_EXTRACT(json_column, '$.action') IS NOT NULL

A very simple demo of the concept as db-fiddle