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

JSON_NORMALIZE handling of duplicate keys

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.7, 10.8, 10.9
    • 10.11
    • JSON
    • None

    Description

      There is a bug in the processing of JSON_NORMALIZE() due to the fact that the sort algorithm isn't stable (it doesn't guarantee that duplicates in the input remain in input order). For instance:

      SET @j = '{"x": 1, "y": 2, "z": 3, "b": 0, "c": 5, "b":3, "a": 4, "b": 1, "a": 3, "a": 2, "a": 1, "b": 2}';
      SELECT @k := JSON_NORMALIZE(@j);
      SELECT JSON_EXTRACT(@j, '$.b'), JSON_EXTRACT(@k, '$.b');
      

      Results in:

      +---------------------------------------------------------------------------------------------------------------------------+
      | @k                                                                                                                        |
      +---------------------------------------------------------------------------------------------------------------------------+
      | {"a":4.0E0,"a":1.0E0,"a":2.0E0,"a":3.0E0,"b":1.0E0,"b":2.0E0,"b":3.0E0,"b":0.0E0,"c":5.0E0,"x":1.0E0,"y":2.0E0,"z":3.0E0} |
      +---------------------------------------------------------------------------------------------------------------------------+
      +-------------------------+-------------------------+
      | JSON_EXTRACT(@j, '$.b') | JSON_EXTRACT(@k, '$.b') |
      +-------------------------+-------------------------+
      | 0                       | 1.0E0                   |
      +-------------------------+-------------------------+
      

      Given that duplicate key values are inaccessible via path expressions, I expected JSON_NORMALIZE() to remove duplicate keys so that

      {"a": 1, "a": 2}

      and

      {"a": 1}

      would produce the same JSON_NORMALIZE() output (and thus also be JSON_EQUAL()). If this isn't desirable for some reason, the sort must at least be stable so that the extracted values don't jump around.

      Attachments

        Issue Links

          Activity

            People

              cvicentiu Vicențiu Ciorbaru
              WayneD Wayne Davison (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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