Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-22141

JSON_REMOVE returns NULL on valid arguments.

Details

    Description

      MariaDB [(none)]> SELECT JSON_REMOVE('{"A": { "B": 1 }}', '$.A.B.C');
      		 
      +---------------------------------------------+
      		 
      | JSON_REMOVE('{"A": { "B": 1 }}', '$.A.B.C') |
      		 
      +---------------------------------------------+
      		 
      | {"A": {"B": 1}}                             |
      		 
      +---------------------------------------------+
      		 
      1 row in set (0.00 sec)

      which is correct.

      MariaDB [(none)]> SELECT JSON_REMOVE('{"A": { "B": 1 }}', '$.A.B.C.D');
      		 
      +-----------------------------------------------+
      		 
      | JSON_REMOVE('{"A": { "B": 1 }}', '$.A.B.C.D') |
      		 
      +-----------------------------------------------+
      		 
      | NULL                                          |
      		 
      +-----------------------------------------------+
      		 
      1 row in set, 1 warning (0.00 sec)
      		 
       
      		 
      MariaDB [(none)]> show warnings;
      		 
      +---------+------+---------------------------------------------------------------------+
      		 
      | Level   | Code | Message                                                             |
      		 
      +---------+------+---------------------------------------------------------------------+
      		 
      | Warning | 4037 | Unexpected end of JSON text in argument 1 to function 'json_remove' |
      		 
      +---------+------+---------------------------------------------------------------------+
      		 
      1 row in set (0.00 sec)

      The result should be as in the first query, but we get NULL instead.

      user complained there on KB
      https://mariadb.com/kb/en/json_remove/#comment_4357

      Attachments

        Issue Links

          is duplicated by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-24542 JSON_REMOVE returns wrong result and ER_JSON_EOS after not finding element to remove

          • Major - Major loss of function.
          • Open

          Activity

            Ascending order - Click to sort in descending order
            jcontesse Jaime Contesse added a comment -

            Another example:

            This works great:

            SELECT JSON_REMOVE('[{"A": 1, "B": 2}, {"A": 1, "B": 2}, {"A": 1, "B": 2}, {"A": 1, "B": 2}, {"A": 1, "B": 2}]', '$[1].B')

            This returns NULL

            SELECT JSON_REMOVE('[{"A": 1, "B": 2}, {"A": 1, "B": 2}, {"A": 1, "B": 2}, {"A": 1, "B": 2}, {"A": 1, "B": 2}]', '$[*].B')

            jcontesse Jaime Contesse added a comment - Another example: This works great: SELECT JSON_REMOVE( '[{"A": 1, "B": 2}, {"A": 1, "B": 2}, {"A": 1, "B": 2}, {"A": 1, "B": 2}, {"A": 1, "B": 2}]' , '$[1].B' ) This returns NULL SELECT JSON_REMOVE( '[{"A": 1, "B": 2}, {"A": 1, "B": 2}, {"A": 1, "B": 2}, {"A": 1, "B": 2}, {"A": 1, "B": 2}]' , '$[*].B' )
            alice Alice Sherepa added a comment -

            jcontesse, KB says that with wildcards error is expected:

            MariaDB [test]> SELECT JSON_REMOVE('[{"A": 1, "B": 2}, {"A": 1, "B": 2}, {"A": 1, "B": 2}, {"A": 1, "B": 2}, {"A": 1, "B": 2}]', '$[*].B');
            		 
            +---------------------------------------------------------------------------------------------------------------------+
            		 
            | JSON_REMOVE('[{"A": 1, "B": 2}, {"A": 1, "B": 2}, {"A": 1, "B": 2}, {"A": 1, "B": 2}, {"A": 1, "B": 2}]', '$[*].B') |
            		 
            +---------------------------------------------------------------------------------------------------------------------+
            		 
            | NULL                                                                                                                |
            		 
            +---------------------------------------------------------------------------------------------------------------------+
            		 
            1 row in set, 1 warning (0,001 sec)
            		 
             
            		 
            Warning (Code 4044): Wildcards or range in JSON path not allowed in argument 2 to function 'json_remove'

            alice Alice Sherepa added a comment - jcontesse , KB says that with wildcards error is expected: MariaDB [test]> SELECT JSON_REMOVE('[{"A": 1, "B": 2}, {"A": 1, "B": 2}, {"A": 1, "B": 2}, {"A": 1, "B": 2}, {"A": 1, "B": 2}]', '$[*].B'); +---------------------------------------------------------------------------------------------------------------------+ | JSON_REMOVE('[{"A": 1, "B": 2}, {"A": 1, "B": 2}, {"A": 1, "B": 2}, {"A": 1, "B": 2}, {"A": 1, "B": 2}]', '$[*].B') | +---------------------------------------------------------------------------------------------------------------------+ | NULL | +---------------------------------------------------------------------------------------------------------------------+ 1 row in set, 1 warning (0,001 sec)   Warning (Code 4044): Wildcards or range in JSON path not allowed in argument 2 to function 'json_remove'
            rucha174 Rucha Deodhar added a comment -

            patch: https://github.com/MariaDB/server/commit/d904cfa828cfc559e1c7b66c848349bbc8b488ed

            rucha174 Rucha Deodhar added a comment - patch: https://github.com/MariaDB/server/commit/d904cfa828cfc559e1c7b66c848349bbc8b488ed
            holyfoot Alexey Botchkov added a comment -

            see the comment to the patch.

            holyfoot Alexey Botchkov added a comment - see the comment to the patch.

            People

              rucha174 Rucha Deodhar
              holyfoot Alexey Botchkov
              Votes:
              2 Vote for this issue
              Watchers:
              9 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.