How do you condense the values in a T-SQL pivot into a JSON?

user_17194313 0
Viewed 105

I have a table in SQL Server of the form:

date userId sessionStartTime SessionEndTime nClicks
2020-01-01 0 9:00:00 9:15:10 4
2020-01-01 0 9:33:00 10:05:05 2
2020-01-01 2 9:05:20 9:25:55 9
... ... ... ... ...

I'm looking to PIVOT it by userId and date and get something that consolidates the other columns as a JSON array. The output is envisioned to look like:

date userId data
2020-01-01 0 [{'sessionStartTime':9:00:00, 'SessionEndTime':9:15:10, 'nClicks':4}, {'sessionStartTime':9:33:00, 'SessionEndTime':10:05:05, 'nClicks':2}]
2020-01-01 2 [{'sessionStartTime':9:05:20, 'SessionEndTime':9:25:55, 'nClicks':9}]
... ... ...

Any ideas on how you could achieve this in SQL (T-SQL)?

azure-sql-database tsql sql-server sql json
2 Answers

You need to generate the JSON content for each distinct combination of [date] and [userId]:

Table:

SELECT *
INTO Data
FROM (VALUES
   ('2020-01-01', 0, '9:00:00', '9:15:10', 4),
   ('2020-01-01', 0, '9:33:00', '10:05:05', 2),
   ('2020-01-01', 2, '9:05:20', '9:25:55', 9)
) v ([date], [userId], [sessionStartTime], [SessionEndTime], [nClicks])

T-SQL:

SELECT DISTINCT [date], [userId], [data]
FROM Data d
OUTER APPLY (
   SELECT [sessionStartTime], [SessionEndTime], [nClicks]
   FROM Data 
   WHERE d.[date] = [date] AND d.[userId] = [userId]
   FOR JSON PATH
) j (data)

Result:

date userId data
2020-01-01 0 [{"sessionStartTime":"9:00:00","SessionEndTime":"9:15:10","nClicks":4},{"sessionStartTime":"9:33:00","SessionEndTime":"10:05:05","nClicks":2}]
2020-01-01 2 [{"sessionStartTime":"9:05:20","SessionEndTime":"9:25:55","nClicks":9}]
user_65780800

Unfortunately, SQL Server does not support JSON_AGG, which would have simplified things.

@Zhorov's answer requires a self-join, but this can actually be done with a single scan of the table and with normal aggregation, using STRING_AGG:

SELECT
  [date],
  [userId],
  STRING_AGG(N'[' + [data] + N']', N',')
FROM Data d
CROSS APPLY (
   SELECT [sessionStartTime], [SessionEndTime], [nClicks]
   FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
) j (data)
GROUP BY
  [date],
  [userId];

db<>fiddle

user_148689970
Related