Correctly return column as JSON Array in MySQL after using CONCAT, GROUP_CONCAT and JSON_OBJECT

Question

I'm using MySQL in a Node.JS API, so I need to get data from the database as JSON objects/arrays.

I'm trying to get a JSON Array nested within the result JSON as one of the values, so this is my current query:

    SELECT
        l.id AS id, l.description AS description, l.parent AS parent,

        (
             SELECT CONCAT(
                 '[',
                 GROUP_CONCAT(
                     JSON_OBJECT(
                     'id', a.id, 'description', a.description,
                     'ip', a.ip, 'lastmovementdetected', a.lastmovementdetected
                     )
                 ),
                 ']'
                 )
              FROM airconditioners AS a WHERE location = l.id
         ) AS airconditioners        

    FROM locations as l`

However, this is the query result (actual output is an array of these JSON objects):

{
        "id": 1,
        "description": "Meu quarto",
        "parent": 0,
        "airconditioners": "[{\"id\": 1, \"ip\": \"192.168.137.96\", \"description\": \"Ar-condicionado\", \"lastmovementdetected\": null},{\"id\": 2, \"ip\": \"192.168.0.1\", \"description\": \"Ar-condicionado\", \"lastmovementdetected\": null},{\"id\": 3, \"ip\": \"192.168.0.1\", \"description\": \"Ar-condicionado\", \"lastmovementdetected\": null}]"
}

SQL is returning the JSON Array as a String and it's also escaping the double quotes from within the JSON.

This is the expected return:

"id": 1,
"description": "Meu quarto",
"parent": 0,
"airconditioners": [
    {
        "id":1,
        "ip":"192.168.137.96",
        "description":"Ar-condicionado",
        "lastmovementdetected":null
    },
    {
        "id":2,
        "ip":"192.168.0.1",
        "description":"Ar-condicionado",
        "lastmovementdetected":null
    },
    {
        "id":3,
        "ip":"192.168.0.1",
        "description":"Ar-condicionado",
        "lastmovementdetected":null
    }
]

Can this be done using a SQL query only? Or I'll have to treat the result before sending the response on the API?

I've tried surrounding the column with a CAST((SELECT...) AS JSON) AS airconditioners and also putting JSON_UNQUOTE() in many places, with no success whatsoever.

EDIT

I couldn't get to a conclusion whether MySQL is compatible with what I want or not. But, for instance, I'm using the following Javascript code to work around it:

Object.keys(result).forEach(key => {
    let airconditioners = result[key].airconditioners;

    if(airconditioners == null) {
    // If the airconditioner field is null, then we replace it with an empty array
        result[key].airconditioners = [];
    } else {
        result[key].airconditioners = JSON.parse(airconditioners);
    }
});

youngchill · Accepted Answer · 2020-12-16 05:40:34Z

1

use JSON_EXTRACT then get result as you expect

SELECT
        l.id AS id, l.description AS description, l.parent AS parent,

        (
             SELECT JSON_EXTRACT( IFNULL(
                CONCAT(
                 '[',
                 GROUP_CONCAT(
                     JSON_OBJECT(
                     'id', a.id, 'description', a.description,
                     'ip', a.ip, 'lastmovementdetected', a.lastmovementdetected
                     )
                 ),
                 ']'
                 )
             ,'[]'),'$')
              FROM airconditioners AS a WHERE location = l.id
         ) AS airconditioners        

    FROM locations as l`

answered Dec 16, 2020 at 5:40

youngchill

114 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MeVimalkumar Over a year ago

this one is a good workaround, but the only catch here is it still returns as a string

Collectives™ on Stack Overflow

Correctly return column as JSON Array in MySQL after using CONCAT, GROUP_CONCAT and JSON_OBJECT

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related