如何扩展SQL查询，从数据库JSON列提取指定键并新增列展示

原查询用于筛选数据库中NVARCHAR(MAX)类型的JSON列，排除指定模式和表名。以下修改后的查询将新增一列，展示JSON列中包含的指定键的中文名称（对应英文键映射：firstname→名、midilename→中间名、fullname→全名、email→邮箱、phone→电话、dateofbirth→出生日期）：

DECLARE @sql NVARCHAR(MAX) = '';

SELECT @sql += '
SELECT 
    ''' + s.name + '.' + t.name + ''' AS Tablename,
    ''' + c.name + ''' AS ColumnName,
    STRING_AGG(k.ChineseKey, '', '') AS 包含的键
FROM (
    VALUES
        (''firstname'', ''名''),
        (''midilename'', ''中间名''),
        (''fullname'', ''全名''),
        (''email'', ''邮箱''),
        (''phone'', ''电话''),
        (''dateofbirth'', ''出生日期'')
    ) k(EnglishKey, ChineseKey)
WHERE EXISTS (
    SELECT 1 
    FROM ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + '
    WHERE JSON_VALUE(' + QUOTENAME(c.name) + ', ''$.' + k.EnglishKey + ''') IS NOT NULL
)
GROUP BY ''' + s.name + '.' + t.name + ''', ''' + c.name + '''
'
FROM 
    sys.tables t
JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
JOIN 
    sys.columns c ON t.object_id = c.object_id
JOIN 
    sys.types tp ON c.user_type_id = tp.user_type_id
WHERE 
    tp.name = 'nvarchar' 
    AND c.max_length = -1
    AND s.name NOT LIKE 'bireports' 
    AND t.name NOT LIKE '%_bk%' 
    AND t.name NOT LIKE '%_mcr_%' 
    AND t.name NOT LIKE '%_mmp_%';

-- 去除开头多余字符并添加排序
SET @sql = STUFF(@sql, 1, 2, '') + ' ORDER BY Tablename, ColumnName';

EXEC sys.sp_executesql @sql;

说明 #

1. 该查询通过动态SQL遍历所有符合条件的表和列，对每个JSON列检查指定键是否存在
2. 使用STRING_AGG将存在的键的中文名称拼接成逗号分隔的字符串
3. 仅返回包含至少一个指定键的列记录（若需展示所有列，包括无指定键的，可调整逻辑改为左连接并处理空值）

示例输出 #

内容的提问来源于stack exchange，提问作者SYED