CODEWITHSUNDEEP
sqljsonsql-server
EddyLee
EddyLee

Reputation: 803

Select create JSON with group column data

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 ?

Upvotes: 0

Views: 1583

Answers (4)

Isa Ataseven
Isa Ataseven

Reputation: 176

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"
             }
          ]
       }
    }

Upvotes: 2

Zhorov
Zhorov

Reputation: 29983

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"
         }
      ]
   }
}

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270051

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;

Upvotes: 0

Erechtheus
Erechtheus

Reputation: 765

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

Upvotes: 0

Related Questions