Different output from mariadb query and using postman

Issue

Hello I have a query to return a JSON value from the database to backend. When I query it directly from the database (phpmyadmin) it works fine and the query return correct value. But when I access it using postman, it returns different value (it returns null)

This the result from the query from phpmyadmin

{
"name": "Desi Ratnaningsih", 
"day": "Monday", 
"schedule": 
    [
        {"start": "10:00:00", "end": "13:00:00"},
        {"start": "17:00:00", "end": "20:00:00"}
    ]
}

This is the result from postman

{
    "name": "Desi Ratnaningsih",
    "day": "Monday",
    "schedule": null
}

This is my query

SELECT DISTINCT JSON_OBJECT( 
'name', d.name, 
'day', s.day, 
'schedule', JSON_EXTRACT(( 
           SELECT CONCAT('[', GROUP_CONCAT( JSON_OBJECT( 'start', h.start, 'end', h.end )) ,']') 
           FROM hours h JOIN schedules s ON h.id = s.working_hour 
           JOIN doctors d ON s.doctor_id = d.doctor_id WHERE d.doctor_id =1 AND s.day="Monday"),'$') ) AS data 
FROM doctors d JOIN schedules s ON d.doctor_id=s.doctor_id 
JOIN hours h on s.working_hour = h.id 
WHERE d.doctor_id = 1 AND s.day="Monday";

Solution

Is this simpler/correct?

SELECT JSON_OBJECT( 
'name', d.name, 
'day', s.day, 
'schedule', JSON_ARRAYAGG( JSON_OBJECT( 'start', h.start, 'end', h.end )) 
) AS data 
FROM doctors d
JOIN schedules s ON d.doctor_id=s.doctor_id 
JOIN hours h on s.working_hour = h.id 
WHERE d.doctor_id = 1 AND s.day="Monday"
GROUP BY d.docker_id, s.day;

This required MariaDB-10.5.

For earlier versions the GROUP_CONCAT approach would be:

SELECT JSON_OBJECT( 
'name', d.name, 
'day', s.day, 
'schedule', CONCAT('[', GROUP_CONCAT( JSON_OBJECT( 'start', h.start, 'end', h.end ) ) ,']') 
) AS data 
FROM doctors d
JOIN schedules s ON d.doctor_id=s.doctor_id 
JOIN hours h on s.working_hour = h.id 
WHERE d.doctor_id = 1 AND s.day="Monday"
GROUP BY d.docker_id, s.day;

Answered By – danblack

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0