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

Question

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

Sample input:

{
    "email": "d'[email protected]",
    "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!

Wierd error because i don't believe the JSON should pass the JSON_VALID function — Raymond Nijland
– Raymond Nijland, Commented Aug 25, 2018 at 0:57
There's a stray NULL in your query, but that should give another error. Sure you posted the right query? Because other than that, it works for me: rextester.com/DVZ80948 — sticky bit
– sticky bit, Commented Aug 25, 2018 at 1:06
Thank you for your reply guys. The query is what I'm using, however the sample data might be different. I'm querying more than 5 million records, and I get this error in between the SELECT query. Not sure, if it's just because of a corner use case or if the query is itself is incorrect. — Sahil Minocha
– Sahil Minocha, Commented Aug 25, 2018 at 1:35
The JSON here is in very clear violation of the spec. It's not clear whether you are aware of that. If JSON_VALID() is really returning true, that's a bug in mysql, but using user-defined varibles for assignment in SELECT is not at deterministic, so your query as written does not actually prove that @valid_source is being set to true before the IF() is evaluated, if at all. — Michael - sqlbot
– Michael - sqlbot, Commented Aug 25, 2018 at 18:40
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 using the operators instead of actual commands worked perfectly fine, it's weird since both are the same but never the less I'm happy that I could resolve it using a small fix. — Sahil Minocha
– Sahil Minocha, Commented Aug 26, 2018 at 21:37

Sahil Minocha · Accepted Answer · 2018-08-26 21:45:17Z

2

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,
}

answered Aug 26, 2018 at 21:45

Sahil Minocha

911 silver badge3 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Brad Davis · Accepted Answer · 2022-07-28 20:32:10Z

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.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related