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

Question

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?

SELECT json_column FROM orders should show the JSON that is stored in the column json_column. Then you can check if the JSON is valid, and correct it (or delete it... ) — Luuk
– Luuk, Commented Jul 22, 2022 at 11:21

Joachim Isaksson · Accepted Answer · 2022-07-22 12:40:25Z

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related