Mysql Question posted in
The official MySQL documentation can be found here.

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

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.

Tags:

2

Answers


  1. 0 
    SET @j1 = '[{"Documents": []}]';
SET @j2 = '{"rc":{"DisplayName":"Registration Certificate","Extension":"png"}}';
SET @path = '$[0].Documents[0]';

SELECT JSON_PRETTY(
  JSON_MERGE_PATCH(
    JSON_REMOVE(
      JSON_ARRAY_INSERT(@j1, @path, JSON_OBJECT()),
      @path
    ),
    JSON_OBJECT(
      'Documents', JSON_ARRAY(
        JSON_EXTRACT(@j2, '$')
      )
    )
  )
) AS result;
    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search