jsoncastingmariadbmariadb-10.5

How to cast value as JSON in MariaDB?


I want to extract a JSON with table name and columns inside each table.

Here is my SQL:

SELECT
    JSON_OBJECTAGG(table_name, columns)
FROM (
    SELECT
        table_name,
        JSON_OBJECTAGG(column_name, data_type) as columns
    FROM `COLUMNS`
    WHERE
        `TABLE_SCHEMA` = 'my_db'
    GROUP BY table_name
) table_columns

The problem is in JSON_OBJECTAGG(table_name, columns), columns became string. How to cast it as JSON?


Solution

  • Use JSON_EXTRACT(column_value_in_json, '$')

    SELECT
        JSON_OBJECTAGG(
            table_name,
            JSON_EXTRACT(
               columns,
               '$'
            )
        )
    FROM (
        SELECT
            table_name,
            JSON_OBJECTAGG(column_name, data_type) as columns
        FROM `COLUMNS`
        WHERE
                `TABLE_SCHEMA` = 'my_db'
        GROUP BY table_name
    ) table_columns