0

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.

keen4000
  • 3
  • 2
  • can you show example data and expected data? – abolfazl sadeghi May 10 '23 at 13:10
  • @abolfazlsadeghi there is sample data, see the first code block, and the expected *behaviour* is well explained. – Thom A May 10 '23 at 13:11
  • 3
    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 May 10 '23 at 13:11
  • Maybe you could look into properly normalized design, where each language / translation is a single row – siggemannen May 10 '23 at 14:16

1 Answers1

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

SelVazi
  • 10,028
  • 2
  • 13
  • 29
  • Turns out this is the only solution for my problem. Thanks. – keen4000 May 11 '23 at 06:58
  • 1
    Unfortunately I can't upvote, because even though I am using SO since 10 years, I still don't have enough reputation points – keen4000 May 11 '23 at 07:00
  • 1
    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. – Thom A May 11 '23 at 12:50