MySQL 5.7 JSON_EXTRACT does not work with quoted strings within the object: [ERROR] "Missing a closing quotation mark in string"

user_10271744 0
Viewed 4587

I am not able to extract quoted strings using JSON_EXTRACT function in MySQL 5.7.

Sample input:

{
    "email": "d'souza@email.com",
    "body": "I may have "random quotes '(single)/(double)" " in my source data"
}

Tried using,

SELECT 
@valid_source := JSON_VALID(jsonString), NULL
IF(@valid_source, JSON_UNQUOTE(JSON_EXTRACT(jsonString, "$.email")), NULL)

I get an error stating: Invalid JSON text in argument 1 to function json_extract: "Missing a closing quotation mark in string." at position xxx

Any help will be appreciated, thanks!

mysql-5.7 quotes mysql sql json
2 Answers

Here is the fix that worked for me: I used the operator "-->" instead of JSON_UNQUOTE(JSON_EXTRACT("jsonString")) and it did not throw me any error for any kind of quotes in my input string. Please note that the above Sample JSON was only one of the use cases that I was expecting in my input. I have around 4 million records, with all different combinations of characters and quotes since it contains the email bodies and using the operators instead of actual commands worked perfectly fine, it's weird since both are essentially the same but never the less I'm happy that I could resolve it using a small fix.

{
@valid_json := JSON_VALID(inputString),
IF(@valid_json, inputString ->> '$.email', NULL) AS EMAIL,
}
user_102717440

You are getting this error because the contents in the field, 'inputString' are invalid JSONs for some record. I think it's a mistake to blindly handle this error without persisting any error message for downstream / future users.

This approach may be acceptable (although I wouldn't accept this from a data engineer). A better solution would be to put a more informative error in the IF statement rather than a null, perhaps something like

CONCAT_WS(" ", 'INCORRECTLY FORMED JSON :', inputString), so that your overall function becomes

IF(valid_json(inputString), input_string ->> '$.email', CONCAT_WS(" ", 'INCORRECTLY FORMED JSON :', inputString)) as EMAIL.

That way any downstream users will be able to identify any underlying data quality issues that may be affecting the validity of conclusions. Along those lines, it may be worth having a completely separate field like

JSON_VALID(inputString) AS INPUT_JSON_VALID that you can use for easier downstream filtering.

user_18734030
Related