MySQL8 json payload does not accept json string as value for element

Question

I have a stored procedure (see stored_procedure_1 code below) that expects json passed as an argument and inserts that data into a table.

---

-- stored_procedure_1:

DELIMITER \\

DROP PROCEDURE IF EXISTS my_test_procedure \\

CREATE PROCEDURE my_test_procedure(my_payload JSON)

BEGIN

    INSERT INTO my_test_table (id, ud)
    SELECT jt.id,
           jt.ud
    FROM JSON_TABLE(my_payload, '$[*]' columns (
        id int path '$.id',
        ud json path '$.ud'
        )) AS jt;
END \\

DELIMITER ;

---

-- table DDL:

CREATE TABLE my_test_table
(
    id int,
    ud varchar(255),
    PRIMARY KEY (id)
);

Calling the procedure with the following call (see call_1 below) that has json string as value for one of the elements gives error (see error_1 response below).

-- call_1:

CALL my_test_procedure('[{"id":1,"ud":"{\"1\":5,\"2\":6,\"3\":7}"}]');

-- error_1: Error Code: 3140. Invalid JSON text: "Missing a comma or '}' after an object member." at position 17 in value for column '.my_payload'.

Call with that value as json (see call_2 below) works as expected.

-- call_2:

CALL my_test_procedure('[{"id":1,"ud":{\"1\":5,\"2\":6,\"3\":7}}]'); -- OR
CALL my_test_procedure('[{"id":1,"ud":{"1":5,"2":6,"3":7}}]');

Looking for suggestions for workarounds within MySQL code (consider the format of the passed payload cannot change).

Answer 1

The issue is with the escaping of " inside your string; when the string is interpreted by MySQL, the \" is interpreted as " (see the manual). For example:

SELECT '[{"id":1,"ud":"{\"1\":5,\"2\":6,\"3\":7}"}]'

Output:

[{"id":1,"ud":"{"1":5,"2":6,"3":7}"}]

which is not valid JSON. To be valid JSON when interpreted by MySQL, you need an escaped \ before the " (i.e. \\", which MySQL then interprets as \"):

SELECT '[{"id":1,"ud":"{\\"1\\":5,\\"2\\":6,\\"3\\":7}"}]'

Output:

[{"id":1,"ud":"{\"1\":5,\"2\":6,\"3\":7}"}]

This will then work with your procedure:

CALL my_test_procedure('[{"id":1,"ud":"{\\"1\\":5,\\"2\\":6,\\"3\\":7}"}]');
SELECT *
FROM my_test_table

Output:

id  ud
1   "{\"1\":5,\"2\":6,\"3\":7}"