mysql 8.0 process json with escape mark '\'

Question

SET @v = '{"a":" \\u3b7e - c:\\user\\abc - \\"ET\\" "}';

SET @j = CAST(@v AS JSON);

failed Error Code: 3141. Invalid JSON text in argument 1 to function cast_as_json: "Incorrect hex digit after \u escape in string." at position 18. because cast_as_json will try to decode '\user' which is not a escape character.

if it like this c:\\\\user\\\\abc, everything fine.

Tried SET @v = REPLACE(@v, '\\', '\\\\');, it will break other string escape.

Tried SET @v = CONVERT(@v USING ASCII);, No matter what charset, no different

Mysql rule '\\' -> '\', '\u3b7e' -> '㭾'. Json rule " -> " in string

I don't know how to solve this.

You can't fix it up after the fact. The JSON has to be given correctly in the input data. Otherwise, there's no way to know which \\u is supposed to be literal versus starting a Unicode escape sequence. — Barmar
– Barmar, Commented Jan 16 at 20:26
Mysql rule ... '\u3b7e' -> '㭾'. ??? dbfiddle.uk/4jaPs9EU — Akina
– Akina, Commented Jan 17 at 4:32

wa56 · Accepted Answer · 2025-01-25 03:00:22Z

0

Finally, I found the root cause is: In JSON, a Unicode surrogate pair must consist of two valid sequences: High surrogate: \uD800-\uDBFF Low surrogate: \uDC00-\uDFFF MySQL interprets JSON strictly and will throw an error if: The surrogate pair is incomplete or malformed. The string contains improperly escaped sequences.

and I just replaced \ -> \\, which will make '\"', separately '\\' and '"', create error.

Finally I replace all possible unicode surrogate to 0000. so far no more fail. Hope helps other people.

SET @jsn = REGEXP_REPLACE(@jsn, '\\\\u[dD][0-9a-fA-F]{3}', '\\\\uD000');

answered Jan 25 at 3:00

wa56

3413 silver badges13 bronze badges

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

Collectives™ on Stack Overflow

mysql 8.0 process json with escape mark '\'

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