如何用SQL从存储JSON的文本字段中提取多个GUID

嘿，隔了20年捡SQL确实得慢慢找感觉，我懂这种生疏感！从非结构化JSON文本里揪出所有GUID，核心思路是匹配标准GUID格式加上数据库对JSON的解析能力，不同数据库的写法略有不同，我给你列几个主流场景的解法：

先明确：GUID的标准格式正则 #

所有GUID都符合这个模式：[0-9a-fA-F]{8}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{12}，我们用这个来筛选。

情况1：SQL Server（2016+）

如果你的JSON结构固定（比如GUID都在dashlets.data.dataSourceId里），直接定位路径提取效率最高：

-- 针对固定路径提取（推荐，效率更高）
SELECT DISTINCT value AS guid
FROM 你的表名
CROSS APPLY OPENJSON(你的JSON字段名, '$.dashlets[*].data.dataSourceId');

如果JSON结构不固定，需要遍历所有节点找GUID，用递归解析：

-- 遍历所有JSON节点提取GUID（SQL Server 2022+支持REGEXP_LIKE）
WITH recursive_json AS (
    SELECT CAST(value AS NVARCHAR(MAX)) AS str_value
    FROM 你的表名
    CROSS APPLY OPENJSON(你的JSON字段名)
    UNION ALL
    SELECT CAST(j.value AS NVARCHAR(MAX))
    FROM recursive_json
    CROSS APPLY OPENJSON(str_value) j
    WHERE ISJSON(str_value) = 1
)
SELECT DISTINCT str_value AS guid
FROM recursive_json
WHERE REGEXP_LIKE(str_value, '^[0-9a-fA-F]{8}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{12}$');

情况2：MySQL（8.0+）

同样，固定路径优先：

-- 固定路径提取
SELECT DISTINCT json_unquote(json_extract(你的JSON字段名, '$.dashlets[*].data.dataSourceId')) AS guid
FROM 你的表名;

全节点遍历找GUID：

-- 遍历所有节点提取
SELECT DISTINCT json_unquote(json_extract(j.value, '$')) AS guid
FROM 你的表名,
     JSON_TABLE(
         你的JSON字段名,
         '$.**' COLUMNS (value JSON PATH '$')
     ) j
WHERE json_unquote(json_extract(j.value, '$')) REGEXP '^[0-9a-fA-F]{8}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{12}$';

情况3：PostgreSQL

固定路径提取：

-- 固定路径提取
SELECT DISTINCT (jsonb_path_query(你的JSON字段名::jsonb, '$.dashlets[*].data.dataSourceId')).text AS guid
FROM 你的表名;

全节点遍历：

-- 遍历所有节点提取
WITH RECURSIVE extract_strings AS (
    SELECT jsonb_each_text(你的JSON字段名::jsonb) AS kv
    FROM 你的表名
    UNION ALL
    SELECT jsonb_each_text((kv).value::jsonb)
    FROM extract_strings
    WHERE (kv).value ~ '^\{.*\}$' OR (kv).value ~ '^\[.*\]$'
)
SELECT DISTINCT (kv).value AS guid
FROM extract_strings
WHERE (kv).value ~ '^[0-9a-fA-F]{8}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{12}$';

小提示 #

如果你的JSON里GUID格式有变种（比如大写/小写混合），调整正则的大小写匹配规则就行。另外固定路径的写法比全遍历快很多，能用上就优先用这个！

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