Question 108774 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

SQL Server JSON group

I’m trying to transform my table to JSON

My table:

DROP TABLE IF EXISTS #tmp
CREATE TABLE #tmp(AnalyticGoodsT CHAR(6), date SMALLDATETIME)

INSERT INTO #tmp(AnalyticGoodsT, date)
VALUES ('000001', '20230424'), ('000002', '20230424'), ('000003', '20230424'), ('000004', '20230424')

Required JSON format(i’m trying to group rows by date):

[
  {
    "Date": "24.04.2023",
    "bk": [
      "000001",
      "000002",
      "000003",
      "000004"
    ]
  }
]

The only way to do this that I found is this

SELECT REPLACE(REPLACE(x, '{"id":',''), '"}','"') FROM 
    (SELECT TOP 10 
        Date=CONVERT(varchar,date,104), 
        (SELECT DISTINCT id=AnalyticGoodsT FROM #tmp a2 
        WHERE a1.date=a2.date
        FOR JSON path) AS bk 
    FROM #tmp a1 
    GROUP BY date 
    FOR JSON PATH) a(x)

But it seems to me that this method is completely wrong.

Tags:

3

Answers


  1. 0

    You can only simplify your statement. I don’t think, that you can build a dynamic JSON array of scalar values, so you need a text-based approach:

    • Build the JSON array using a combination of STRING_AGG(), CONCAT() and STRING_ESCAPE() (to escapes possible special characters).
    • Wrap the generated array with JSON_QUERY() to prevent the escaping of the generated JSON array. As is explained in the documentation, … JSON_QUERY returns a valid JSON fragment. As a result, FOR JSON doesn’t escape special characters in the JSON_QUERY return value.
    • Return the expected JSON with FOR JSON AUTO.

    T-SQL:

    SELECT 
   CONVERT(varchar(10), [date], 104) AS [Date], 
   JSON_QUERY(
      CONCAT('[', STRING_AGG('"' + STRING_ESCAPE(AnalyticGoodsT, 'json') + '"', ','), ']')
   ) AS [bk]
FROM #tmp
GROUP BY [date]
FOR JSON AUTO
    Login or Signup to reply.
  2. 0

    You can do it using JSON_QUERY and STRING_AGG to contruct the bk array and FOR JSON PATH as follows :

    SELECT *
FROM (
  SELECT date,
         JSON_QUERY('["' + STRING_AGG( AnalyticGoodsT, '","') + '"]') as bk
          
  FROM #tmp
  group by date
) AS s
FOR JSON PATH

    Demo here

    Login or Signup to reply.
  3. 0

    Unfortunately, SQL Server does not have JSON_AGG. You need to use a combination of STRING_AGG JSON_QUERY and STRING_ESCAPE

    SELECT
  Date = CONVERT(nvarchar(10), t.date, 104),
  bk = JSON_QUERY(N'[' + STRING_AGG(N'"' + STRING_ESCAPE(t.AnalyticGoodsT, 'json') + N'"', N',') + N']')
FROM #tmp t
GROUP BY
  t.date
FOR JSON PATH;

    db<>fiddle

    STRING_ESCAPE is necessary to escape any invalid JSON characters, and JSON_QUERY is necessary to prevent double-escaping.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search