如何将值转换为JSON在MariaDB?

Question

我想提取一个JSON表名和列在每个表。

下面是我的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

问题是在JSON_OBJECTAGG(table_name, columns)中，列变成了字符串。如何将其转换为JSON?

Answer 1

使用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