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

Can you please be more specific about which version you are running? Query SELECT VERSION(); and report what it says. — Bill Karwin
– Bill Karwin, Commented Nov 18, 2023 at 21:32
I did read that nested JSON_ARRAYAGG wasn't supported Neither aggregate nor window functions can be nested. how to rewrite my query CTEs. Each CTE implements only one aggregation level, using source tables and previous CTEs as rowsource. — Akina
– Akina, Commented Nov 18, 2023 at 21:35
I edited to remove references to MySQL, because you are not using MySQL. You're using MariaDB. These are different databases, with different features. Especially with regards to JSON features, they are not compatible. We should all think of MariaDB as a wholly different product from MySQL, because even though MariaDB started as a fork of MySQL, these two products are being developed independently, with little regard to maintaining compatibility. — Bill Karwin
– Bill Karwin, Commented Nov 18, 2023 at 22:04

Gabriel · Accepted Answer · 2023-11-19 10:25:37Z

2

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

answered Nov 19, 2023 at 10:25

Gabriel

1231 silver badge9 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bill Karwin · Accepted Answer · 2023-11-18 22:02:35Z

0

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

answered Nov 18, 2023 at 22:02

Bill Karwin

568k87 gold badges711 silver badges872 bronze badges

3 Comments

Gabriel Over a year ago

Thanks a ton for taking the time to look into this and to make me discover dbfiddle.uk ! However the result of your query is not exactly what i was looking for. In your result, all 9 possibilities for container with id 3 are merged but i want them separated by choices. I also wrote my expected result in all json but i actually would like 2 row returned, one per container @BillKarwin

Gabriel Over a year ago

I juste discovered something weird in the data, make sure to use the version i just edited @BillKarwin

Bill Karwin Over a year ago

I'm happy to demonstrate the general idea, but I'm not going to chase down your specific use case. I'll leave it to you to apply the method to your data.

Collectives™ on Stack Overflow

MariaDB nested JSON_ARRAYAGG alternative

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related