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

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:


DROP PROCEDURE IF EXISTS my_test_procedure \\

CREATE PROCEDURE my_test_procedure(my_payload JSON)


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


-- 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).


  • 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}"}]'



    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}"}]'



    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


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