Select create JSON with group column data

Question

I have a table in SQLServer , like this

ID | Name | Date    |
=========================
1  | Me   | 2019-30-10  |
2  | Me   | 2019-31-10  |
3  | Me   | 2019-28-10  |
4  | Me   | 2019-29-10  |
5  | You  | 2019-30-10  |
-------------------------

I want to select to show data or json like group data by date. I use FOR JSON to convert data from query to JSON but i don't known how to write correct SELECT query :

{
  "Name": {
    "2019-31-10": [
      {
        "Name": "Me"
      }
    ],
    "2019-30-10": [
      {
        "Name": "Me"
      },
      {
        "Name": "You"
      }
    ],
    "2019-29-10": [
      {
        "Name": "Me"
      }
    ],
    "2019-28-10": [
      {
        "Name": "Me"
      }
    ]
  }
}

Can someone help me ?

Which database are you using - `MySQL` or `SQL Server`? And do you really want a JSON output? — Zhorov, Oct 30 '19 at 10:06
My SQL <> SQL Server. What are you actually using here? What have *you* tried to solve this problem yourself? That expected result isn't JSON either... Also *"Can someone help me ?"* isn't a programming question. What is your programming question here? — Thom A, Oct 30 '19 at 10:07
Your expected output more closely resembles YAML than JSON, but it isn't valid YAML either. — Tim Biegeleisen, Oct 30 '19 at 10:08
I use SQLServer , i want data display like output to use in mobile app , for create multiple view type :) — EddyLee, Oct 30 '19 at 10:10
I just show how to data display. Actually , in output , will be key - array data — EddyLee, Oct 30 '19 at 10:10
Show us what you *actually* want your end result to look like. Also, please retag the RDBMS you are using, and the version (which is really important here if you want JSON data). Don't forget, as well, to include *your* attempts too. — Thom A, Oct 30 '19 at 10:12
You should look here : [SQL Server SELECT to JSON function](https://stackoverflow.com/questions/6818441/sql-server-select-to-json-function) — Isa Ataseven, Oct 30 '19 at 10:14

Isa Ataseven · Accepted Answer · 2019-10-30T12:21:41.883

I hope it works. First Edit(Added For Json Auto)

DECLARE @Date datetime
DECLARE @RESULT varchar(max)
SET @RESULT = '{ "Name":{'

DECLARE CRS_Date CURSOR FOR
SELECT  Date FROM [dbo].[TableExample]  GROUP BY Date
OPEN CRS_Date FETCH NEXT FROM CRS_Date INTO @Date
WHILE @@FETCH_STATUS = 0
  BEGIN
    SET @RESULT = @RESULT + '"' + CONVERT(varchar, @Date, 23) + '":' + 
    (select Name from  [dbo].[TableExample] t where t.Date = @Date    FOR JSON AUTO) +','
  FETCH NEXT FROM CRS_Date INTO @Date
END
CLOSE CRS_Date
DEALLOCATE CRS_Date
SET @RESULT = LEFT(@RESULT, LEN(@RESULT) - 1)
SET @RESULT = @RESULT + '}}'
SELECT
  @RESULT


My Result : 

    {
       "Name":{
          "2019-10-28":[
             {
                "Name":"Me"
             }
          ],
          "2019-10-29":[
             {
                "Name":"Me"
             }
          ],
          "2019-10-30":[
             {
                "Name":"Me"
             },
             {
                "Name":"You"
             }
          ],
          "2019-10-31":[
             {
                "Name":"Me"
             }
          ]
       }
    }

Dear @Isa Ataseven , can you help me one more thing , the JSON result have wrong unicode data , it will be " L?" instead of "Lá" — EddyLee, Oct 31 '19 at 03:00

score 1 · Answer 2 · answered Oct 30 '19 at 11:37

I don't think that you can directly generate a JSON output with variable key names ("2019-31-10", "2019-30-10", ...) using FOR JSON PATH or FOR JSON AUTO. But if you use SQL Server 2017+, you may try to generate the JSON output with JSON_MODIFY() using an expression to generate the variables key names:

Table:

CREATE TABLE #Data (
   ID int,
   [Name] varchar(10),
   [Date] date
)
INSERT INTO #Data 
   (ID, [Name], [Date])
VALUES   
   (1, 'Me',  '2019-10-30'),
   (2, 'AA',  '2019-10-31'),
   (3, 'BB',  '2019-10-28'),
   (4, 'Me',  '2019-10-29'),
   (5, 'You', '2019-10-30')

Statement:

DECLARE @json nvarchar(max) = N'{"Name": {}}'   

SELECT @json = JSON_MODIFY(
   @json, 
   CONCAT('append $.Name."', d.[Date], '"'),  
   JSON_QUERY((SELECT [Name] FOR JSON PATH, WITHOUT_ARRAY_WRAPPER))
)
FROM #Data d
ORDER BY d.[Date] DESC

SELECT @json

Result:

{
   "Name":{
      "2019-10-31":[
         {
            "Name":"AA"
         }
      ],
      "2019-10-30":[
         {
            "Name":"Me"
         },
         {
            "Name":"You"
         }
      ],
      "2019-10-29":[
         {
            "Name":"Me"
         }
      ],
      "2019-10-28":[
         {
            "Name":"BB"
         }
      ]
   }
}

score 0 · Answer 3 · answered Oct 30 '19 at 10:30

I would strongly advise against returning a single column like this. You want to maintain data integrity in your name column. Mixing data or data types in a column is bad practice. Making SQL Server do data transformation is not what it's meant to do.

I would suggest you use a powershell script to take the input table as is, and work on that to produce an output JSON file. There are plenty of built in functionality to do that.

Nevertheless, if you still want or need to do it this way, one way is by using Grouping Sets:

SELECT
    COALESCE([name],[date]) as Name,
    ROW_NUMBER() OVER (ORDER BY T.[date] ASC) AS order_key
FROM
    dbo.myTable T
GROUP BY
 GROUPING SETS (
        ([Name],[date]),
        ([date])    
)
ORDER BY order_key desc

score 0 · Answer 4 · answered Oct 30 '19 at 10:47

If I understand correctly, you want a self-join:

select tm.date, tm.name, ty.name
from t tm left join
     t ty
     on ty.date = tm.date and ty.name = 'you'
where t.name = 'me';

You can also do this with conditional aggregation:

select t.date,
       max(case when tm.name = 'me' then tm.name end) as name1,
       max(case when tm.name = 'you' then tm.name end) as name2
from t
group by t.date;

Select create JSON with group column data

4 Answers4