Facing issue in JSON generation

MySQL & MariaDB
1

Hi All,

I’m facing an issue while generating JSON from MariaDB.

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 ) ;
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 :

{
   "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

2

Hi Manoj,
What is the issue? You did not explain your issue.

1 Like
3

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

1 Like
4

I would suggest that you try to first get the result you want as normal SQL, then add the JSON wrappers.

1 Like
5

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?

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 :

{
   "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

1 Like
6

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

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.

1 Like
7

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

1 Like
8

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.

1 Like