Group by and merge to a JSON record in SQL Server-CodePudding

I have rows in a table like this:

ORG	Tenant	Dimension	Count
A	T1	MAU	100
A	T1	WAU	70
B	T2	MAU	50
B	T2	Retention	30

Now I need to group based on ORG, Tenant and remaining columns I need to convert into a single JSON as below.

ORG	Tenant	CustomData
A	T1	{MAU:100,WAU:70}
B	T2	{MAU:50,Retention:30}

How can I achieve this in SQL Server or using C#? Do we have an out of the box support for this?

Thanks for your help, Nikesh

CodePudding user response：

Any specific solution is relative to your environment and it may not perform fast enough. However, You can use C# to reshape your list of records. Create a new JSON Object like so:

    {
  "MAU": [
    {
      "ORG": "A",
      "Tenant": "T1",
      "Dimension": "MAU",
      "Count": 100
    }
  ],
  "WAU" : [...]
}

CodePudding user response：

Achieve with LINQ-to-SQL expression.

First group by Org and Tenant, for the grouping result part, the CustomData property is applied as Dictionary/KeyValuePair.

var result = dataList
    .GroupBy(x => new { x.Org, x.Tenant },
            (key, g) => new 
            {
                Org = key.Org,
                Tenant = key.Tenant,
                CustomData = g.ToDictionary(y => y.Dimension, y => y.Count)
            })
    .ToList();

Sample .NET Fiddle

Output

[
  {
    "Org": "A",
    "Tenant": "T1",
    "CustomData": {
      "MAU": 100,
      "WAU": 70
    }
  },
  {
    "Org": "B",
    "Tenant": "T2",
    "CustomData": {
      "MAU": 50,
      "Retention": 30
    }
  }
]

Note:

As the CustomData is applied as Dictionary/KeyValuePair, the key must be unique before mapping. If in the input data there are the records with the same Org, Tenant, and Dimension, then you need to group these 3 properties/columns and perform an aggregate sum first.

CodePudding user response：

your data

CREATE TABLE test(
   ORG       VARCHAR(100) NOT NULL 
  ,Tenant    VARCHAR(100) NOT NULL
  ,Dimension VARCHAR(100) NOT NULL
  ,Count1     INTEGER  NOT NULL
);
INSERT INTO test
(ORG,Tenant,Dimension,Count1) VALUES 
('A','T1','MAU',100),
('A','T1','WAU',70),
('B','T2','MAU',50),
('B','T2','Retention',30);

to get your desired result,you can use String_agg and Concat without using For Json Path

select 
ORG,
Tenant,
concat('{',string_agg(concat(Dimension,':',Count1),','),'}' ) CustomData
from test
group by  ORG,Tenant