C# SQL Server to JSON with key groups-CodePudding

I've this table with is basically translations:

Key        CultureId     Txt
$HELLO     en-GB         Hello
$HELLO     pt-BR         Olá
$WELCOME   en-GB         Welcome
$WELCOME   pt-BR         Olá

And a select like:

Select Key, CultureId, Txt 
From Xlations 
Order by Key

This is an endpoint rest api, so I'd like a result like

{
   "$HELLO":{
      "en-GB":"Hello",
      "pt-BR":"Olá"
   },
   "$WELCOME":{
      "en-GB":"Bem Vindo",
      "pt-BR":"Welcome"
   }
}

So, keys with no arrays, totally in objects where the field key will be the parent of the assigned translations.

I know how to do it by creating few iterations on my code, but I was wondering if there is some shorthand for that because I don't want to keep my code huge and complex with iterates and nested iterates. Not sure if such things are possible, but: Anywone know some easy and simple way ?

CodePudding user response：

JSON output is usually generated using the FOR JSON clause. In your case, the required JSON output has variable key names, so FOR JSON is probably not an option. But, if the SQL Server version is 2017 or higher, you may try to generate the JSON manually, using string concatenation and aggregation. Also, as @Charlieface commented, escape the generated text with STRING_ESCAPE().

Test table:

SELECT *
INTO Xlations
FROM (VALUES
   (N'$HELLO',   N'en-GB', N'Hello'),
   (N'$HELLO',   N'pt-BR', N'Olá'),
   (N'$WELCOME', N'en-GB', N'Welcome'),
   (N'$WELCOME', N'pt-BR', N'Bem Vindo')
) v ([Key], CultureId, Txt)

Statement:

SELECT CONCAT(
   N'{',
   STRING_AGG(CONCAT(N'"', STRING_ESCAPE([Key], 'json'), N'":', [Value]), N','),
   N'}'
) AS Json
FROM (
   SELECT DISTINCT x.[Key], a.[Value]
   FROM Xlations x
   OUTER APPLY (
      SELECT CONCAT(
         N'{',
         STRING_AGG(CONCAT(N'"', STRING_ESCAPE(CultureId, 'json'), N'":"', STRING_ESCAPE(Txt, 'json'), N'"'), N','),
         N'}'
      ) AS [Value]
      FROM Xlations
      WHERE [Key] = x.[Key]
   ) a
) t

Result:

{
   "$HELLO":{"en-GB":"Hello","pt-BR":"Olá"},
   "$WELCOME":{"en-GB":"Welcome","pt-BR":"Bem Vindo"}
}

CodePudding user response：

You can not use your sql functions to do this and you have to do it manually.

SELECT CONCAT('{',string_agg(jsoncol,','),'}') Json
FROM
   (SELECT '1' AS col, CONCAT('"',[key],'"',':{'   string_agg(jsoncol,',') ,'}') AS jsoncol
    FROM 
       (SELECT [key],CONCAT('"',CultureId,'":"',txt ,'"') AS jsoncol FROM tb) t
    GROUP BY [key]) t
GROUP BY col

demo in dbfiddle<>uk

CodePudding user response：

The answer given by @Zhorov is good, but you can improve it by only querying the table once, aggregating then aggregating again.

This should be more performant than a correlated subquery.

SELECT CONCAT(
   N'{',
   STRING_AGG(CONCAT(N'"', STRING_ESCAPE([Key], 'json'), N'":', [Value]), N','),
   N'}'
) AS Json
FROM (
   SELECT x.[Key], CONCAT(
         N'{',
         STRING_AGG(CONCAT(N'"', STRING_ESCAPE(CultureId, 'json'), N'":"', STRING_ESCAPE(Txt, 'json'), N'"'), N','),
         N'}'
      ) AS [Value]
   FROM Xlations x
   GROUP BY x.[Key]
) t

db<>fiddle