SQL Server: is it possible to use JSON_VALUE within GROUP_BY?

Question

I tried to use JSON_VALUE within GROUP_BY clause and got this error:

JSON text is not properly formatted. Unexpected character 'b' is found at position 0.

When I use it only in SELECT, I get the expected result.

SELECT 
    COUNT(*),
    Date,
    JSON_VALUE(msg, '$.a')
FROM
    requests
GROUP BY 
    Date, JSON_VALUE(msg, '$.a')

Answer 1

It seems, that there is an invalid JSON, stored in the msg column. But you may try to change your statement.

Table:

CREATE TABLE requests (
   [Date] date,
   [Msg] nvarchar(1000)
)
INSERT INTO requests ([Date], [Msg])
VALUES
   ('20201020', N'{"m":"GET","a":"/Login.aspx"}'),
   ('20201020', N'{"m":"GET","a":"/Login.aspx"}'),
   ('20201020', N'{"m":"GET","a":"/Login.aspx"}'),
   ('20201021', N'{"m":"GET","a":"/Login.aspx"}'),
   ('20201021', N'{b:"GET","a":"/Login.aspx"} ')

Statement:

SELECT 
   COUNT(*) AS [Count], 
   [Date], 
   CASE WHEN ISJSON([Msg]) = 1 THEN JSON_VALUE([Msg], '$.a') END AS Msg
FROM requests
GROUP BY [Date], CASE WHEN ISJSON([Msg]) = 1 THEN JSON_VALUE([Msg], '$.a') END

Result:

Count   Date        Msg
1       2020-10-21  
3       2020-10-20  /Login.aspx
1       2020-10-21  /Login.aspx

As an additional note, if you want to get the rows with the invalid JSON content, execute the following statement:

SELECT * 
FROM request 
WHERE ISJSON([Msg]) = 0