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

When using GROUP BY with JSON_ARRAYAGG with joint table, the square brackets are not included

    XMLWordPrintable

Details

    Description

      When using without the GROUP BY, the square brackets are there, but when I add GROUP BY to the query the square brackets disappear.

      create table owner(
          id int primary key,
          name varchar(50)
      );
       
      create table test_table(
          id int primary key,
          type varchar(50),
          size int,
          color varchar(50),
          owner_id int,
          FOREIGN KEY(owner_id) REFERENCES owner(id)
      );
       
      SELECT name, owner.id, JSON_ARRAYAGG(JSON_OBJECT('id',tt.id,'type',tt.type,'color',tt.color)) as materials from owner LEFT JOIN test_table tt on owner.id = tt.owner_id GROUP BY owner.id;
      

      Expected result:

      Name Id Materials
      David 1 [{"id": 2, "type": "metal", "color": "blue"},{"id": 1, "type": "paper", "color": "red"}]
      Joe 2 [{"id": 3, "type": "wood", "color": "green"}]
      John 3 [{"id": 4, "type": "plastic", "color": "yellow"}]

      Actual result:

      Name Id Materials
      David 1 {"id": 2, "type": "metal", "color": "blue"}

      ,

      {"id": 1, "type": "paper", "color": "red"}
      Joe 2 {"id": 3, "type": "wood", "color": "green"}
      John 3 {"id": 4, "type": "plastic", "color": "yellow"}

      Attachments

        Issue Links

          Activity

            People

              holyfoot Alexey Botchkov
              davidszoke Dávid Szöke
              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.