Question 105906 in Azure Question posted in
The official documentation to get you started can be found here.

Azure – Group by and merge to a JSON record in SQL Server

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

Tags:

3

Answers


  1. 0

    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" : [...]
}
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
  3. 0

    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);

    For Json Path change Sql tables into Json data, but you can get your desired result by using String_agg and Concat

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

    dbfiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search