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?
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