Generate JSON output of multiple columns using TO_JSON

{question}

How can I obtain the JSON output for multiple columns using the TO_JSON function?

{question}

{answer}

The TO_JSON function converts a table column, an entire table, a scalar value, or a single row into a JSON object.

If we want to convert multiple columns into JSON, we cannot do it the regular way. We receive the following error indicating the second column, as shown below:

ERROR 1064 ER_PARSE_ERROR: 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 ', to_json_test.Name) AS Results FROM to_json_test' at line 1

Solution:

To utilize multiple columns in TO_JSON, we can use either CTE or subselect methods, as demonstrated below:

CTE method:

WITH Sub as (SELECT IDPLAN, NAME FROM to_json_test)
SELECT TO_JSON(Sub.*) AS Results
FROM Sub;

Subselect:

SELECT TO_JSON(Sub.*) AS Results
FROM (SELECT IDPLAN, NAME FROM to_json_test) Sub;

{answer}