从SQL Server中的JSON字典中提取第一个值。

huangapple go评论58阅读模式
英文:

Extract first value from a JSON dictionary in SQL Server

问题

Sure, here's the translated content:

我在JSON字典中存储了各种翻译。我使用JSON_VALUE提取值(在此示例中,我使用一个变量而不是表列。在现实生活中,它是一个nvarchar(max)表列):

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

现在我正在为用户文化不存在于字典中的情况实施后备机制。我想尝试不同的文化,使用COALESCE

1) 用户文化 (fr-fr)
2) 两个字母的用户文化 (fr)
3) 英语 (en)
4) 作为最后的选项,我想返回字典中的任何一个翻译 (FirstOrDefault)

树中的三种不同(已知)文化的后备机制很容易(选项1-3):

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

我的问题是:是否有一种方法可以提取JSON字典的任何(第一个)键值对,然后返回其值(选项4)?例如,如果只有德国(de)的翻译,用户文化是法语(fr),他们仍然应该获得德国翻译。总比没有好。

我尝试使用'$[0]'访问它,但显然不起作用。

使用OPENJSON确实可以访问,但我想这会降低性能。我需要它用于按字母顺序对表进行排序。
英文:

I have various translations stored in a JSON dictionary. I extract the values with JSON_VALUE (for that example I use a variable instead of a column. In real life it is a nvarchar(max) table column):

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

Now I am implementing a fallback mechanism for the case the user's culture does not exist in the dictionary. I want to try different cultures, with a coaloesce:

  1. user culture (fr-fr)
  2. two-letter user culture (fr)
  3. english (en)
  4. as a last option I want to return just any translation in that dictionary (FirstOrDefault).

Fallback of tree different (known) cultures is easy (Options 1-3):

SELECT COALESCE(JSON_VALUE(@json, '$."fr-fr"'), JSON_VALUE(@json, '$."fr"'), JSON_VALUE(@json, '$."en"')) -- returns 'vert'

My question: Is there a way to extract just any (the first) key-value pair of a JSON dictionary and then return the value of it (Option 4)? For example if there is only a german (de) translation and the user culture is french (fr), they should still get the german translation. Better than nothing.

I tried accessing it with '$[0]' but that obviously did not work.

Access with OPENJSON does work indeed, but I guess there will be a loss in performance with that. I need it for sorting tables alphabetically.

答案1

得分: 2

这可以使用 OPENJSON 来从 JSON 生成行来实现。

您可以使用条件排序 ORDER BY CASE 来指定生成行的顺序,然后使用 TOP(1) 获取第一个行:

SELECT TOP(1) [key], [value]
FROM OpenJson(@json)
ORDER BY CASE WHEN [key] = 'fr-fr' THEN 1
              WHEN [key] = 'fr' THEN 2
              WHEN [key] = 'en' THEN 3
              ELSE 4 END;

在此演示

英文:

This can be done using OPENJSON to generate rows from json .

You can specify the order of the generated rows using the conditional order ORDER BY CASE then get the first one using TOP(1) :

SELECT TOP(1) [key] , [value] 
FROM OpenJson(@json)
ORDER BY case when [key] = 'fr-fr' then 1
              when [key] = 'fr' then 2
              when [key] = 'en' then 3
              else 4 end;

Demo here

huangapple
  • 本文由 发表于 2023年5月10日 21:03:35
  • 转载请务必保留本文链接:https://go.coder-hub.com/76218758.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定