MariaDb vs MySQL JSON_ARRAYAGG JSON_OBJECT Without Escaping-CodePudding

I have a select query, which works just fine in my localhost MySQL database environment. It should return json object.

When I run the same query on my hosted public server with MariaDB 10.5.15 installed, the returned json includes several backslashes, escaping characters.

Here is the code:

        SELECT 
        json_object(
            'id', C1.Category1ID,
            'text', C1.Category1Name,
            'nodes', JSON_ARRAYAGG(
                JSON_OBJECT(
                    'id', C2.Category2ID, 
                    'text', C2.Category2Name, 
                    'nodes', C2.nodes, 
                    'class', 'nav-level-2', 
                    'href', 'admin-categories-2.php'
                )
            ),
            'class', 'nav-level-1',
            'href', 'admin-categories-1.php'
        ) AS JSON
    FROM categories_1 C1

        LEFT JOIN (
        
            SELECT 
                C2.Category1ID, 
                C2.Category2ID, 
                C2.Category2Name, 
                JSON_ARRAYAGG(
                    JSON_OBJECT(
                        'id', C3.Category3ID, 
                        'text', C3.Category3Name, 
                        'class', 'nav-level-3',
                        'href', 'admin-categories-3.php'
                    )
                ) as nodes
            FROM categories_2 C2
                LEFT JOIN categories_3 C3 ON C3.Category2ID = C2.Category2ID AND C3.Category3Status = 1 
            WHERE C2.Category2Status = 1 
            GROUP BY C2.Category2ID
            ORDER BY C2.Category2Order, C3.Category3Order  
            
        ) C2 ON C2.Category1ID = C1.Category1ID
        
    WHERE C1.Category1Status = 1 
    GROUP BY C1.Category1ID 
    ORDER BY C1.Category1Order 
    ;

My question is how to write this query correctly for MariaDB.

I am attaching result from MySQL (img1) and MariaDB (img2).

I am attaching create and insert statements for db here:

    CREATE TABLE `categories_1` (
  `Category1ID` int(11) NOT NULL AUTO_INCREMENT,
  `Category1Name` varchar(45) DEFAULT NULL,
  `Category1Name_FR` varchar(45) DEFAULT NULL,
  `Category1Photo` varchar(45) DEFAULT NULL,
  `Category1Order` int(3) DEFAULT NULL,
  `Category1Status` int(1) DEFAULT 1,
  PRIMARY KEY (`Category1ID`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COMMENT='Top level category'

CREATE TABLE `categories_2` (
  `Category2ID` int(11) NOT NULL AUTO_INCREMENT,
  `Category2Name` varchar(45) DEFAULT NULL,
  `Category2Name_FR` varchar(45) DEFAULT NULL,
  `Category2Order` int(3) DEFAULT NULL,
  `Category2Status` int(1) DEFAULT 1,
  `Category1ID` int(11) DEFAULT NULL COMMENT 'To which parent level category it fits',
  PRIMARY KEY (`Category2ID`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8 COMMENT='Mid level category'

CREATE TABLE `categories_3` (
  `Category3ID` int(11) NOT NULL AUTO_INCREMENT,
  `Category3Name` varchar(45) DEFAULT NULL,
  `Category3Name_FR` varchar(45) DEFAULT NULL,
  `Category3Order` int(3) DEFAULT NULL,
  `Category3Status` int(1) DEFAULT 1,
  `Category2ID` int(11) DEFAULT NULL COMMENT 'To which parent level category it fits',
  PRIMARY KEY (`Category3ID`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8 COMMENT='Bottom level category'


INSERT INTO `categories_1` VALUES (1,'Meat','Meat-fr','meat.jpg',1,1),(2,'Fish & Sea Food',NULL,'fish.jpg',2,1),(3,'Fruit & Vegetables',NULL,'fruit-veg.jpg',3,1),(4,'Test L1',NULL,'categories-default.jpg',4,0);
INSERT INTO `categories_2` VALUES (1,'Beef','Feef-fr',1,1,1),(2,'Lamb',NULL,2,1,1),(3,'Pork',NULL,3,1,1),(4,'Veal',NULL,4,1,1),(5,'Poultry-Fowl',NULL,6,1,1),(6,'Sausages and Bacon',NULL,5,1,1),(7,'Salmon',NULL,8,1,2),(8,'Flat Fish',NULL,9,1,2),(9,'Common Fish',NULL,10,1,2),(10,'Squid family',NULL,11,1,2),(11,'Shellfish',NULL,12,1,2),(12,'Tuna',NULL,13,1,2),(13,'Other Fish',NULL,14,1,2),(14,'TEST L2',NULL,7,0,1);
INSERT INTO `categories_3` VALUES (1,'Specialist Beef','Specialist Beef-fr',1,1,1),(2,'Wagyu',NULL,2,1,1),(3,'Japanese Wagyu',NULL,3,1,1),(4,'Other Beef',NULL,4,1,1),(5,'All Lamb',NULL,6,1,2),(6,'All Pork',NULL,5,1,3),(7,'All Veal',NULL,7,1,4),(8,'All Poultry-Fowl',NULL,11,1,5),(9,'Pork Sausages',NULL,8,1,6),(10,'Other meats',NULL,9,1,6),(11,'Bacon',NULL,10,1,6),(12,'All Salmon',NULL,12,1,7),(13,'All Flat Fish',NULL,13,1,8),(14,'All Common Fish',NULL,14,1,9),(15,'All Squid family',NULL,15,1,10),(16,'All Shellfish',NULL,16,1,11),(17,'All Tuna',NULL,17,1,12),(18,'All Other Fish',NULL,18,1,13),(19,'TEST L3',NULL,999,0,14);

CodePudding user response：

MariaDB have no JSON datatype (JSON keyword is an alias for LONGTEXT keyword only), it may treate string type value as JSON only.

You use construction JSON_ARRAYAGG( JSON_OBJECT( .... In MariaDB the value produced by JSON_OBJECT is string ! It is one solid string, not complex value of JSON datatype. Hence during JSON_ARRAYAGG this solid string value which contains the chars needed in quoting is processed, and all doublequote chars are quoted.

See FIDDLE, especially last and pre-last code blocks. In pre-last block pay special attention to the doubequote chars which wraps the whole value (not inner doublequotes which are quoted by the slashes).

I do not see the way to fix this in MariaDB. There is no method to tell the function that the value provided as an argument is not string but JSON - there is no such datatype.

Wait until MariaDB implements JSON datatype (if) and upgrade.