SQL Server中提取JSON字典首个值？含多语言回退需求

场景说明 #

我在SQL Server中将多语言翻译存储为JSON字典，通过JSON_VALUE提取对应值（示例用变量，实际为nvarchar(max)类型的表列）：

DECLARE @json AS NVARCHAR(200) = '{"en":"green","de":"grün","fr":"vert"}'
SELECT JSON_VALUE(@json, '$.fr') -- 返回 'vert'

需要实现优先级回退机制：

• 用户完整文化代码（如fr-fr）
• 用户双字母文化代码（如fr）
• 英文（en）
• 最终回退：返回JSON字典中的任意首个值

前三级回退已通过COALESCE实现：

SELECT COALESCE(
    JSON_VALUE(@json, '$.fr-fr'),
    JSON_VALUE(@json, '$.fr'),
    JSON_VALUE(@json, '$.en')
) -- 返回 'vert'

核心问题 #

如何高效提取JSON字典的首个值作为最终回退？尝试过$[0]无效，OPENJSON可行但担心性能（需用于表排序场景）。

高效解决方案 #

方法1：结合OPENJSON与TOP 1（性能可控） #

OPENJSON取TOP 1的开销极低，完全适配表排序场景，可直接嵌入COALESCE或封装为复用函数：

直接嵌入写法

DECLARE @json AS NVARCHAR(200) = '{"en":"green","de":"grün","fr":"vert"}'
DECLARE @culture NVARCHAR(10) = 'fr-fr'

SELECT COALESCE(
    JSON_VALUE(@json, '$."' + @culture + '"'),
    JSON_VALUE(@json, '$."' + LEFT(@culture, 2) + '"'),
    JSON_VALUE(@json, '$.en'),
    (SELECT TOP 1 value FROM OPENJSON(@json))
) AS TranslatedValue

封装为内联表值函数（复用性更强）

内联表值函数（ITVF）比标量UDF性能更优，可被查询优化器直接展开：

CREATE FUNCTION dbo.GetFirstJsonValue(@json NVARCHAR(MAX))
RETURNS TABLE
AS
RETURN
(
    SELECT TOP 1 value FROM OPENJSON(@json)
)

调用示例：

SELECT COALESCE(
    JSON_VALUE(@json, '$.fr-fr'),
    JSON_VALUE(@json, '$.fr'),
    JSON_VALUE(@json, '$.en'),
    (SELECT value FROM dbo.GetFirstJsonValue(@json))
) AS TranslatedValue

方法2：纯字符串截取（极端性能优化） #

如果JSON格式严格规范（键值对均用双引号包裹，无嵌套、转义字符），可通过字符串截取跳过JSON解析：

DECLARE @json AS NVARCHAR(200) = '{"en":"green","de":"grün","fr":"vert"}'

SELECT COALESCE(
    JSON_VALUE(@json, '$.fr-fr'),
    JSON_VALUE(@json, '$.fr'),
    JSON_VALUE(@json, '$.en'),
    SUBSTRING(
        @json,
        CHARINDEX('":"', @json) + 2,
        CHARINDEX('"', @json, CHARINDEX('":"', @json) + 2) - (CHARINDEX('":"', @json) + 2)
    )
) AS TranslatedValue

注意：该方法仅适用于格式简单的JSON字典，复杂场景优先使用OPENJSON方案。

性能说明 #

• OPENJSON取TOP 1的开销极小，SQL Server会自动优化，不会成为大表排序场景的性能瓶颈。
• 内联表值函数能避免标量UDF逐行调用的性能损耗，适配批量查询场景。

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