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:

user culture (fr-fr)
two-letter user culture (fr)
english (en)
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.

@abolfazlsadeghi there is sample data, see the first code block, and the expected behaviour is well explained. — Thom A
– Thom A ♦, Commented May 10, 2023 at 13:11
There is no syntax for retrieving "any" property from a JSON object with JSON_VALUE or JSON_QUERY. If performance is a concern arguably none of this processing should be done in SQL Server anyway, but in any case you should test rather than guessing -- SELECT TOP(1) [value] FROM OPENJSON(...) should scale just fine because the JSON is parsed incrementally (which you can verify by adding some unparsable rubbish at the end and seeing it won't cause an error). — Jeroen Mostert
– Jeroen Mostert, Commented May 10, 2023 at 13:11
Maybe you could look into properly normalized design, where each language / translation is a single row — siggemannen
– siggemannen, Commented May 10, 2023 at 14:16

SelVazi · Accepted Answer · 2023-05-10 14:16:26Z

2

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

edited May 10, 2023 at 14:16

answered May 10, 2023 at 13:24

SelVazi

16.2k2 gold badges19 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

keen4000 Over a year ago

Turns out this is the only solution for my problem. Thanks.

keen4000 Over a year ago

Unfortunately I can't upvote, because even though I am using SO since 10 years, I still don't have enough reputation points

Thom A Over a year ago

Reputation is earned from contributions, not length of membership, @keen4000 . YOu might have been a member for almost 10 years, but you've not contributed anything in those 10 years, so you've not earned any reputation.

Collectives™ on Stack Overflow

Extract first value from a JSON dictionary in SQL Server

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related