MariaDB nested JSON_ARRAYAGG alternative

Question

I did read that nested JSON_ARRAYAGG wasn't supported but I can't find how to rewrite my query

Data :

CREATE TABLE IF NOT EXISTS `table1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(99) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `table1` (`id`, `name`) VALUES
    (2, 'Blablifdsfsdfsdfsd'),
    (3, 'Fdsfsdfds');

CREATE TABLE IF NOT EXISTS `table2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `container` int(10) unsigned NOT NULL,
  `sort` int(3) unsigned NOT NULL,
  `name` varchar(99) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `table2` (`id`, `container`, `sort`, `name`) VALUES
    (3, 2, 0, 'Fsdfds'),
    (4, 3, 0, 'Fsdfsdfds'),
    (5, 3, 1, 'Fdsfsdfsd');

CREATE TABLE IF NOT EXISTS `table3` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `container` int(11) unsigned NOT NULL,
  `choice` int(11) unsigned NOT NULL,
  `sort` int(3) unsigned NOT NULL,
  `type` varchar(20) NOT NULL DEFAULT '',
  `refId` int(11) unsigned NOT NULL,
  `extraCharge` float NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `table3` (`id`, `container`, `choice`, `sort`, `type`, `refId`, `extraCharge`) VALUES
    (1, 2, 3, 0, 'category', 39, 0),
    (2, 2, 3, 1, 'category', 40, 0),
    (3, 2, 3, 2, 'category', 19, 0),
    (4, 2, 3, 3, 'category', 23, 0),
    (8, 3, 4, 0, 'category', 39, 0),
    (9, 3, 4, 1, 'category', 17, 0),
    (10, 3, 4, 2, 'category', 40, 0),
    (11, 3, 4, 3, 'category', 36, 0),
    (12, 3, 5, 0, 'item', 22, 0),
    (13, 3, 5, 1, 'category', 40, 0),
    (14, 3, 5, 2, 'category', 36, 0),
    (15, 3, 5, 3, 'category', 41, 0),
    (16, 3, 5, 4, 'category', 26, 0);

My Query :

SELECT 
    table1.name,
    JSON_ARRAYAGG(JSON_OBJECT(
        "name", table2.name,
        "possibilities",JSON_ARRAYAGG(JSON_OBJECT(
            "id", table3.refId,
            "extraCharge", table3.extraCharge) 
            ORDER BY table3.sort))
         ORDER BY table2.sort) AS choices
FROM table1
LEFT JOIN table2 ON table2.container = table1.id
LEFT JOIN table3 ON table3.choice = table2.id
GROUP BY table1.id

Expected Result :

[
    {
        name: "Blablifdsfsdfsdfsd", choices: [
            {
                name: "Fsdfds", possibilities: [
                    { id: 39, extraCharge: 0 },
                    { id: 39, extraCharge: 0 },
                    { id: 19, extraCharge: 0 },
                    { id: 23, extraCharge: 0 },
                ]
            }
        ]
    },
    {
        name: "Fdsfsdfds", choices: [
            {
                name: "Fsdfsdfds", possibilities: [
                    { id: 39, extraCharge: 0 },
                    { id: 17, extraCharge: 0 },
                    { id: 40, extraCharge: 0 },
                    { id: 36, extraCharge: 0 },
                ]
            },
            {
                name: "Fdsfsdfsd", possibilities: [
                    { id: 22, extraCharge: 0 },
                    { id: 40, extraCharge: 0 },
                    { id: 36, extraCharge: 0 },
                    { id: 41, extraCharge: 0 },
                    { id: 26, extraCharge: 0 },
                ]
            }
        ]
    }
]

Could you help me rewrite this to make it work? :)

Am I missing something?

Fiddle

Sorry but it looks like I cannot provide a fiddle because DB version looks too old to support sorting in JSON_ARRAYAGG expression

Answer 1

Got it. For future readers :

No need to overcomplicate things.

Just add a select before the second JSON_ARRAYAGG, a proper WHERE clause and remove the useless join

SELECT 
table1.name,
JSON_ARRAYAGG(JSON_OBJECT(
    "name", table2.name,
    "possibilities",(SELECT JSON_ARRAYAGG(JSON_OBJECT(
                      "id", table3.refId,
                      "extraCharge", table3.extraCharge)
                     ORDER BY table3.sort)
                     FROM table3
                     WHERE table3.choice = table2.id)
                     )
     ORDER BY table2.sort) AS choices
FROM table1
LEFT JOIN table2 ON table2.container = table1.id
GROUP BY table1.id

Fiddle

Answer 2

I believe in MariaDB, JSON_ARRAYAGG() supports ORDER BY since it was introduced in 10.5. The real problem with your question is that you're trying to do an aggregate inside an aggregate. This is not allowed for any kind of aggregate function (e.g. you cannot do SUM(COUNT()) in any version of SQL database).

So you have to do one aggregation in a subquery, then the outer aggregation in the outer query.

This works in MariaDB 10.5 and later:

SELECT 
    JSON_ARRAYAGG(
        JSON_OBJECT(
            "name", name,
            "choices", choices
        )
        ORDER BY name) AS choices
FROM (
    SELECT t1name as name, 
        JSON_ARRAYAGG(
            JSON_OBJECT(
                "name", name,
                "possibilities", possibilities
            )
            ORDER BY name
        ) AS choices
    FROM (
        SELECT table1.name AS t1name, table2.name AS name,
            JSON_ARRAYAGG(
                JSON_OBJECT(
                    "id", table3.id,
                    "extraCharge", table3.extraCharge
                )
                ORDER BY table3.sort
            ) AS possibilities
        FROM table1
        LEFT JOIN table2 ON table2.container = table1.id
        LEFT JOIN table3 ON table3.choice = table2.id
        GROUP BY table1.id
    ) AS t
  ) AS t;

You do need an extra nested subquery for each level of nesting you want in your result JSON.

Demo Dbfiddle for MariaDB 10.5: https://dbfiddle.uk/VpFTIses