JSON_ARRAY_INSERT function in MySQL is enclosing new JSON in double quotes

Below is a snippet of a MySQL Script

mysql> SET @j1 = '[{"Documents": []}]';
mysql> SET @j2 = '{"rc":{"DisplayName":"Registration Certificate","Extension":"png"}}';
mysql> SET @path = '$[0].Documents[0]';
mysql> SELECT REPLACE(JSON_ARRAY_INSERT(@j1, @path, @j2), '\\', '');

The output generated by above script is:

[{"Documents": ["{"rc":{"DisplayName":"Registration Certificate","Extension":"png"}}"]}]

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

If I do not use REPLACE function, the output generated is:

[{"Documents": ["{\"rc":{\"DisplayName\":\"Registration Certificate\",\"Extension\":\"png\"}}"]}]

Whereas, the output that I am expecting is:

[{"Documents": [{"rc":{"DisplayName":"Registration Certificate","Extension":"png"}}]}]

The difference is in the extra double quotes that surround @j2. Also, JSON_ARRAY_INSERT is adding backward slashes with all double quotes in @j2.

What is wrong with my script? Also can backward slashes be removed.

>Solution :

Documentation never implies the value itself is of JSON type. You need to cast first:

SELECT 
    JSON_ARRAY_INSERT(
        '[{"Documents": []}]', 
        '$[0].Documents[0]',
        '{"rc":{"DisplayName":"Registration Certificate","Extension":"png"}}'
    ) AS insert_string,
    JSON_ARRAY_INSERT(
        '[{"Documents": []}]', 
        '$[0].Documents[0]',
        CAST('{"rc":{"DisplayName":"Registration Certificate","Extension":"png"}}' AS JSON)
    ) AS insert_object  
;

Demo