How to cast value as JSON in MariaDB?

Question

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?

The inner query has already aggregated on `table_name`, so I don't see what you are trying to achieve with the outer query. This is the [dbfiddle](https://dbfiddle.uk/?rdbms=mariadb_10.5&fiddle=36a6665504db18a2bd7a149c101ffe23) I came up with (altering `TABLE_SCHEMA` criteria because of dbfiddle limitation). Is this what you wanted? — danblack, Jul 26 '21 at 00:49

score 4 · Accepted Answer · answered Jul 30 '21 at 09:45

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

How to cast value as JSON in MariaDB?

1 Answers1