Error DeserializeJSON() MySQL json_object

Question

I am getting back a JSON string from a MySQL 5.7 query in ColdFusion 9.0.1. Here is my query:

SELECT  (
        SELECT  GROUP_CONCAT(
                  JSON_OBJECT(
                    'nrtype', nrt.nrtype,
                    'number', nr.number
                    )
                )
        ) AS nrJSON
FROM    ...

The returned data looks like this:

{"nrtype": "Phone 1", "number": "12345678"},{"nrtype": "E-Mail 1", "number": "[email protected]"}

But as soon as I try to use DeserializeJSON() on it I am getting the following error:

JSON parsing failure at character 44:',' in {"nrtype": "Phone 1", "number": "12345678"},{"nrtype": "E-Mail 1", "number": "[email protected]"}

I am a little confused. What I want to get is a structure created by the DeserializeJSON() function.

What can I do?

Answer 1

That is not valid JSON as the parser is describing. If you wrap that JSON within square brackets '[' and ']' it would be valid (or at least parsable). They will make it an array of structures. Not sure how to make MySQL return the data within those brackets?

I guess you could add the brackets using ColdFusion but I would prefer to have the source do it correctly.

jsonhack = '[' & queryname.nrJSON & ']';
datarecord = DeserializeJSON(jsonhack);
writeDump(datarecord);

I created an example with your data that you can see here - trycf.com gist

From the comments

The solution indeed was [to add the following to the SQL statement]:

CONTACT('[', 
    GROUP_CONCAT(
        JSON_OBJECT(...)
    ),
']')

Answer 2

If you have columns with some already containing JSON format String, try this : https://stackoverflow.com/a/45278722/2282880

Portion of code with JSON_MERGE() :

...
CONCAT(
    '{"elements": [',
    GROUP_CONCAT(
        JSON_MERGE(
            JSON_OBJECT(
                'type',  T2.`type`,
                'data',  T2.`data`
            ),
            CONCAT('{"info": ',  T2.`info`, '}')
        )
    ),
    ']}'
) AS `elements`,
...