Collectives™ on Stack Overflow
Find centralized, trusted content and collaborate around the technologies you use most.
Learn more about Collectives
Teams
Q&A for work
Connect and share knowledge within a single location that is structured and easy to search.
Learn more about Teams
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);
–
–
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.
–
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
