# Facing issue in JSON generation **URL:** \ **Category:** MySQL & MariaDB\ **Created:** [January 15, 2021, 8:43am UTC](https://forums.percona.com/t/facing-issue-in-json-generation/8795 "2021-01-15T08:43:28Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![manoj.gupta.91](https://avatars.discourse-cdn.com/v4/letter/m/e95f7d/32.png) [@manoj.gupta.91](https://forums.percona.com/u/manoj.gupta.91)\ **Post date:** [January 15, 2021, 8:43am UTC](https://forums.percona.com/t/facing-issue-in-json-generation/8795/1 "2021-01-15T08:43:28Z")
Hi All, I’m facing an issue while generating JSON from MariaDB. ```auto CREATE TABLE `OCCUPANCY_DTLS` ( `PARKING_ID` INT(10) UNSIGNED NULL DEFAULT NULL, `PARKING_LOT_ID` INT(10) UNSIGNED NOT NULL, `VEHICLE_CLASS_ID` INT(10) UNSIGNED NOT NULL, `TOTAL_SLOTS_AVAILABLE` INT(10) UNSIGNED NOT NULL, `TOTAL_SLOTS_OCCUPIED` INT(10) UNSIGNED NOT NULL, `ONLINE_BOOKING_ENABLED` INT(10) UNSIGNED NOT NULL, `ONLINE_BOOKING_PERCENTAGE` INT(10) UNSIGNED NOT NULL, PRIMARY KEY (`PARKING_LOT_ID`, `VEHICLE_CLASS_ID`) USING BTREE ) COLLATE='utf8_general_ci' ENGINE=InnoDB ; INSERT INTO OCCUPANCY_DTLS VALUES( 1, 1, 1, 100, 20, 1, 10 ) ; INSERT INTO OCCUPANCY_DTLS VALUES( 1, 1, 2, 80, 10, 1, 10 ) ; INSERT INTO OCCUPANCY_DTLS VALUES( 1, 2, 1, 100, 15, 1, 20 ) ; ``` ```auto SELECT JSON_OBJECT ( 'ResHeader', JSON_OBJECT ( 'ResDate', DATE_FORMAT(SYSDATE(), '%d-%m-%Y %H:%i:%s'), 'ResID', 1, 'ResName', 'Occupancy', 'ResDesc', 'Parking Lot Availability and Occupancy Status' ), 'ResDetail', JSON_ARRAY( JSON_OBJECT ( 'ParkingLotID', P.PARKING_LOT_ID, 'ParkingLotOccupancySummary', JSON_ARRAYAGG( JSON_OBJECT ( 'VehicleClassID', P.VEHICLE_CLASS_ID, 'TotalSlots', P.TOTAL_SLOTS_AVAILABLE ) ORDER BY P.VEHICLE_CLASS_ID ) ) ) ) FROM occupancy_dtls P GROUP BY P.PARKING_LOT_ID ; ``` Required Output : ```auto { "ResHeader":{ "ResDate":"09-01-2021 12:38:20", "ResID":"12345", "ResName":"Occupancy", "ResDesc":"Parking Lot Availability and Occupancy Status" }, "ResDetail":[ { "ParkingLotID":"1", "ParkingLotOccupancySummary":[ { "VehicleClassID":"1", "TotalSlots":"15" }, { "VehicleClassID":"2", "TotalSlots":"15" } ] }, { "ParkingLotID":"2", "ParkingLotOccupancySummary":[ { "VehicleClassID":"1", "TotalSlots":"15" } ] } ] } ``` Thanks & Regards Manoj ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [January 15, 2021, 4:31pm UTC](https://forums.percona.com/t/facing-issue-in-json-generation/8795/2 "2021-01-15T16:31:44Z")
Hi Manoj, What is the issue? You did not explain your issue. ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [January 15, 2021, 4:39pm UTC](https://forums.percona.com/t/facing-issue-in-json-generation/8795/3 "2021-01-15T16:39:07Z")
When I copy/paste your SQL, I get this: `ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY P.VEHICLE_CLASS_ID` ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [January 15, 2021, 4:51pm UTC](https://forums.percona.com/t/facing-issue-in-json-generation/8795/4 "2021-01-15T16:51:56Z")
I would suggest that you try to first get the result you want as normal SQL, then add the JSON wrappers. ---
**Author:** ![manoj.gupta.91](https://avatars.discourse-cdn.com/v4/letter/m/e95f7d/32.png) [@manoj.gupta.91](https://forums.percona.com/u/manoj.gupta.91)\ **Post date:** [January 18, 2021, 8:03am UTC](https://forums.percona.com/t/facing-issue-in-json-generation/8795/5 "2021-01-18T08:03:22Z")
Apology for not explaining my issue properly. **Issues:** (1) I wrote a query for my desired output but it has two issues. One it outputs multiple /// forward slashes. Second it gives tag ("SlotOccupancyDetails": null) when there is no value i should not see this tag at all. (2) Is there any better way to write same query in MariaDB? ```auto WITH HEAD AS ( SELECT JSON_OBJECT ( 'ResDate', DATE_FORMAT(SYSDATE(), '%d-%m-%Y %H:%i:%s'), 'ResID', 1 ) AS HEADER ), SLOT_OCCUPANCY AS ( SELECT S.PARKING_LOT_ID, JSON_ARRAYAGG( JSON_OBJECT( 'SlotID', S.SLOT_ID, 'SlotNo', S.SLOT_NO, 'OccupancyStatus', S.SLOT_STATUS ) ) SLOT_INFO FROM parking_slots S GROUP BY S.PARKING_LOT_ID ), FINAL_DATA AS ( SELECT P.PARKING_LOT_ID, JSON_ARRAYAGG( JSON_OBJECT('VehicleClassID', P.VEHICLE_CLASS_ID, 'TotalSlotsAvailable', P.TOTAL_SLOTS_AVAILABLE, 'TotalSlotsOccupied', P.TOTAL_SLOTS_OCCUPIED, 'OnlineBookingEnabled', P.ONLINE_BOOKING_ENABLED, 'Online', P.ONLINE_BOOKING_PERCENTAGE, 'SlotOccupancyDetails', SO.SLOT_INFO ) ORDER BY P.VEHICLE_CLASS_ID ) OCCUPANCY_INFO FROM occupancy_dtls P LEFT OUTER JOIN SLOT_OCCUPANCY SO ON( P.PARKING_LOT_ID = SO.PARKING_LOT_ID) GROUP BY P.PARKING_LOT_ID ) SELECT JSON_OBJECT ( 'ResHeader', H.Header, 'ResDetail', JSON_ARRAYAGG( JSON_OBJECT ( 'ParkingLotID', D.PARKING_LOT_ID, 'ParkingLotOccupancySummary', D.OCCUPANCY_INFO ) ) ) FROM HEAD H, FINAL_DATA D ; ``` **Output :** ```auto { "ResHeader":"{\"ResDate\": \"18-01-2021 13:26:07\", \"ResID\": 1}", "ResDetail":[ { "ParkingLotID":1, "ParkingLotOccupancySummary":"[{\"VehicleClassID\": 1, \"TotalSlotsAvailable\": 100, \"TotalSlotsOccupied\": 20, \"OnlineBookingEnabled\": 1, \"Online\": 10, \"SlotOccupancyDetails\": \"[{\\\"SlotID\\\": 1, \\\"SlotNo\\\": \\\"A1\\\", \\\"OccupancyStatus\\\": \\\"1\\\"},{\\\"SlotID\\\": 2, \\\"SlotNo\\\": \\\"A2\\\", \\\"OccupancyStatus\\\": \\\"0\\\"},{\\\"SlotID\\\": 3, \\\"SlotNo\\\": \\\"A3\\\", \\\"OccupancyStatus\\\": \\\"1\\\"}]\"},{\"VehicleClassID\": 2, \"TotalSlotsAvailable\": 200, \"TotalSlotsOccupied\": 25, \"OnlineBookingEnabled\": 0, \"Online\": 0, \"SlotOccupancyDetails\": \"[{\\\"SlotID\\\": 1, \\\"SlotNo\\\": \\\"A1\\\", \\\"OccupancyStatus\\\": \\\"1\\\"},{\\\"SlotID\\\": 2, \\\"SlotNo\\\": \\\"A2\\\", \\\"OccupancyStatus\\\": \\\"0\\\"},{\\\"SlotID\\\": 3, \\\"SlotNo\\\": \\\"A3\\\", \\\"OccupancyStatus\\\": \\\"1\\\"}]\"}]" }, { "ParkingLotID":2, "ParkingLotOccupancySummary":"[{\"VehicleClassID\": 1, \"TotalSlotsAvailable\": 70, \"TotalSlotsOccupied\": 30, \"OnlineBookingEnabled\": 0, \"Online\": 0, \"SlotOccupancyDetails\": null}]" } ] } ``` Regards Manoj ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [January 18, 2021, 4:11pm UTC](https://forums.percona.com/t/facing-issue-in-json-generation/8795/6 "2021-01-18T16:11:00Z")
Check out the JSON\_UNQUOTE feature, or set the sql\_mode NO\_BACKSLASH\_ESCAPES. [https://dev.mysql.com/doc/refman/8.0/en/json-modification-functions.html#json-unquote-character-escape-sequences](https://dev.mysql.com/doc/refman/8.0/en/json-modification-functions.html#json-unquote-character-escape-sequences) > when there is no value i should not see this tag at all. You will see the tag because it is a column name and you are doing a join. Add a “WHERE IS NOT NULL” or change your JOIN to remove this column. Again, I recommend that you first attempt to write your query to return normal SQL results first, then attempt to add the JSON aspect. ---
**Author:** ![manoj.gupta.91](https://avatars.discourse-cdn.com/v4/letter/m/e95f7d/32.png) [@manoj.gupta.91](https://forums.percona.com/u/manoj.gupta.91)\ **Post date:** [January 19, 2021, 12:42pm UTC](https://forums.percona.com/t/facing-issue-in-json-generation/8795/7 "2021-01-19T12:42:42Z")
Thanks for your help and support. I’m comfortable with writing queries. But facing issues with MariaDB as I’m new to this database. I’m not able to use nested JSON\_ARRAYAGG which is perfectly fine with Oracle. Till now I’ve worked with Oracle and I don’t face any issue in that. Working with MongoDB is my first experience so facing and getting struck in such issues. Any help on better way to achieve this in MariaDB? Regards Manoj ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [January 19, 2021, 2:21pm UTC](https://forums.percona.com/t/facing-issue-in-json-generation/8795/8 "2021-01-19T14:21:08Z")
Because MariaDB and Oracle are different databases, there’s no guarantee that JSON\_ARRAYAGG will work the same. Each database is capable of implementing any function however they want. I know that MariaDB is different also from MySQL. Have you tried using Percona Server for MySQL, just to compare? If you can’t get it working in SQL, then you’ll have to parse the data in your app and construct the JSON via app code.