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

JSON_ARRAYAGG and JSON_OBJECTAGG treat JSON arguments as text

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.5
    • Fix Version/s: 10.5.4
    • Component/s: JSON
    • Labels:
      None

      Description

      I'm looking at how JSON_ARRAYAGG and JSON_OBJECTAGG work at processing JSON
      documents.

      I'm trying these examples:

      create table t30 (col1 json);
      insert into t30 values('{"color":"red", "size":1}' );
      insert into t30 values('{"color":"blue", "size":2}' );
      

      MariaDB [test]> select JSON_ARRAYAGG(col1) from t30;
      +------------------------------------------------------------------------+
      | JSON_ARRAYAGG(col1)                                                    |
      +------------------------------------------------------------------------+
      | ["{\"color\":\"red\", \"size\":1}","{\"color\":\"blue\", \"size\":2}"] |
      +------------------------------------------------------------------------+
      

      while on MySQL-8, one gets:

      select JSON_ARRAYAGG(col1) from t30;
      +-------------------------------------------------------------+
      | JSON_ARRAYAGG(col1)                                         |
      +-------------------------------------------------------------+
      | [{"size": 1, "color": "red"}, {"size": 2, "color": "blue"}] |
      +-------------------------------------------------------------+
      

      The same with JSON_OBJECTAGG:

      create table t31 (name varchar(100), contents json);
      insert into t31 values('obj1', '{"color":"blue", "size":2}' );
      insert into t31 values('obj2', '{"color":"red", "size":1}' );
      

      MariaDB [test]> select JSON_OBJECTAGG(name, contents) from t31;
      +---------------------------------------------------------------------------------------+
      | JSON_OBJECTAGG(name, contents)                                                        |
      +---------------------------------------------------------------------------------------+
      | {"obj1":"{\"color\":\"blue\", \"size\":2}", "obj2":"{\"color\":\"red\", \"size\":1}"} |
      +---------------------------------------------------------------------------------------+
      

      While on MySQL-8 it is:

      select JSON_OBJECTAGG(name, contents) from t31;
      +-----------------------------------------------------------------------------+
      | JSON_OBJECTAGG(name, contents)                                              |
      +-----------------------------------------------------------------------------+
      | {"obj1": {"size": 2, "color": "blue"}, "obj2": {"size": 1, "color": "red"}} |
      +-----------------------------------------------------------------------------+
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              holyfoot Alexey Botchkov
              Reporter:
              psergey Sergei Petrunia
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: