how to replace double back slash character to single one from MySQL JSON_OBJECT function results for column including JSON string

You can use the MySQL function REPLACE() to replace double backslashes with single backslashes in a JSON object. Here’s an example:

1

SELECT REPLACE(JSON_OBJECT(column_name), '\\\\\\\\', '\\\\') FROM table_name;

This will return the column values with double backslashes replaced with single backslashes.

If you want to insert a JSON object literal with single backslashes instead of double backslashes, you can set the NO_BACKSLASH_ESCAPES SQL mode⁴. When this mode is set, a single backslash instead of double backslashes can be used to insert the JSON object literal, and the backslashes are preserved⁴. If you use the JSON_OBJECT() function when performing the insert and this mode is set, you must alternate single and double quotes⁴.

I hope this helps! Let me know if you have any other questions.

ソース: Bing との会話 2023/3/28(1) MySQL :: MySQL 8.0 Reference Manual :: 11.5 The JSON …. https://dev.mysql.com/doc/refman/8.0/en/json.html アクセス日時 2023/3/28. (2) How to replace backslash("\\\\") from JSON document?. https://stackoverflow.com/questions/59369828/how-to-replace-backslash-from-json-document アクセス日時 2023/3/28. (3) Single backslash in json being replaced with double backslash. https://powerusers.microsoft.com/t5/Building-Flows/Single-backslash-in-json-being-replaced-with-double-backslash/td-p/732934 アクセス日時 2023/3/28. (4) Replacing double backslash to single backslash in json string. https://social.msdn.microsoft.com/Forums/windows/en-US/c7f5e9f4-8b34-4236-9c62-6f55dbbd5df7/replacing-double-backslash-to-single-backslash-in-json-string?forum=winappswithhtml5 アクセス日時 2023/3/28. (5) How do I remove backslashes from records in MySQL. https://dba.stackexchange.com/questions/2261/how-do-i-remove-backslashes-from-records-in-mysql アクセス日時 2023/3/28. (6) mysql: replace \ (backslash) in strings - Stack Overflow. https://stackoverflow.com/questions/10059450/mysql-replace-backslash-in-strings アクセス日時 2023/3/28. (7) json - Replace single backslash escape with double backslash …. https://stackoverflow.com/questions/36625333/replace-single-backslash-escape-with-double-backslash-in-javascript アクセス日時 2023/3/28. (8) Replace single backslash “\” with double backslashes. https://stackoverflow.com/questions/16144090/replace-single-backslash-with-double-backslashes アクセス日時 2023/3/28.

例: Auroraから S3に　JSON出力

1
2
3
4
5
6
7
8
9

    return "\n".join(
        [
            "SET sql_mode = 'NO_BACKSLASH_ESCAPES';",
            f"SELECT JSON_OBJECT({obj_list})",
            f"FROM {table} INTO OUTFILE S3 '{bucket_prefix}'",
            f"MANIFEST ON OVERWRITE {opt_overwrite};",
            "",
        ]
    )