Extract first value from a JSON dictionary in SQL Server

Question

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.

Answer 1

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