时间戳

首页 > 解决方案 > 如何将以下 SQL 数据转换为以下给定的 JSON 数据

.net - 如何将以下 SQL 数据转换为以下给定的 JSON 数据

问题描述

我从 SQL 获取以下数据。

Person  Occupation  Salary  Count
Ajay    Farmer      5000    1
Ajay    Barber      6000    1
Ajay    Carpenter   8000    1
Mahesh  Farmer      3000    1
Mahesh  Barber      8000    1
Mahesh  Carpenter   9000    1

我需要将其转换为以下 JSON 格式：我尝试过，但未能应用适当的组。

{
  "Ajay": [
    {
      "Job Details": [
        {
          "Occupation": "Farmer",
          "Salary": "5000",
          "count": 1
        },
        {
          "Occupation": "Barber",
          "Salary": "6000",
          "count": 1
        },
        {
          "Occupation": "Carpenter",
          "Salary": "8000",
          "count": 1
        }
      ]
    }
  ],
  "Mahesh": [
    {
      "Job Details": [
        {
          "Occupation": "Farmer",
          "Salary": "3000",
          "count": 1
        },
        {
          "Occupation": "Barber",
          "Salary": "8000",
          "count": 1
        },
        {
          "Occupation": "Carpenter",
          "Salary": "9000",
          "count": 1
        }
      ]
    }
  ]
}

标签： .netsql-serverasp.net-mvcentity-frameworklinq

解决方案

更新为更符合 OP 的预期结果

用一点字符串操作

例子

Declare @YourTable Table ([Person] varchar(50),[Occupation] varchar(50),[Salary] int,[Count] int)  Insert Into @YourTable Values 
 ('Ajay','Farmer',5000,1)
,('Ajay','Barber',6000,1)
,('Ajay','Carpenter',8000,1)
,('Mahesh','Farmer',3000,1)
,('Mahesh','Barber',8000,1)
,('Mahesh','Carpenter',9000,1)


 ;with cte as (
 Select Person,JS= (Select [Occupation]=Occupation
                          ,[Salary]    =Salary
                          ,[Count]     =Count
                      From @YourTable where Person=A.Person
                      FOR JSON PATH,Root('Job Details')
                   )
 From @YourTable A
 Group By Person
)
Select '{'+string_agg('"'+string_escape(Person,'json')+'":['+JS+']',',')+'}'
 From  cte

结果

{
    "Ajay": [{
        "Job Details": [{
            "Occupation": "Farmer",
            "Salary": 5000,
            "Count": 1
        }, {
            "Occupation": "Barber",
            "Salary": 6000,
            "Count": 1
        }, {
            "Occupation": "Carpenter",
            "Salary": 8000,
            "Count": 1
        }]
    }],
    "Mahesh": [{
        "Job Details": [{
            "Occupation": "Farmer",
            "Salary": 3000,
            "Count": 1
        }, {
            "Occupation": "Barber",
            "Salary": 8000,
            "Count": 1
        }, {
            "Occupation": "Carpenter",
            "Salary": 9000,
            "Count": 1
        }]
    }]
}

推荐阅读