SQL Server在嵌套的JSON数据结构中获取数据

问题描述：

我嵌套了JSON文件，我试图用它作为报表的数据源。我正在“扁平”结构，但不知道如何评估兄弟姐妹的数据。SQL Server在嵌套的JSON数据结构中获取数据

样本数据：

{ 
"Cources": [{ 
     "ID": 1, 
     "Name": "MAC100", 
     "Room": 100, 
     "TAID": 123, 
     "StudentsIDs": [ 
      1, 2 
     ] 
    } 
], 

"TAs": [{ 
    "ID": 123, 
    "Name": "Joe", 
    "LName": "Smith" 
}], 

"Students": [{ 
    "ID": 1, 
    "LName": "Clark" 
}, { 
    "ID": 2, 
    "LName": "Peterson" 
}] 
}

SQL服务器：提取数据文件和报告扁平化数据：

SELECT Cource.ID, 
Cource.Name as CName, 
Cource.Room as CRoom, 
CourceStudents.LName 

FROM OPENROWSET (BULK 'C:\Data\file.json', SINGLE_CLOB) as jsonfile 

CROSS APPLY OPENJSON(BulkColumn,'$[0]') WITH( 

    Cources nvarchar(max) AS JSON, 
    TAs nvarchar(max) AS JSON, 
    Students nvarchar(max) AS JSON 

    ) AS [SampleData] 

CROSS APPLY OPENJSON(Cources) WITH (
    Room integer, 
    Name nvarchar(max), 
    StudentsIDs nvarchar(max) AS JSON 
) as [Cources] 

CROSS APPLY OPENJSON(Students) WITH ( 
    ID integer, 
    LName nvarchar(max), 
) as [Students] 

CROSS APPLY OPENJSON(StudentsIDs) WITH ( 
**//??? how to get full data for the student from "Students" joining by the student ID ?** 
) as [CourceStudents]

我无意中发现了如何从“学生”让所有学生的数据为“StudentIDs “加入ID。

你想要什么，输出的样子？另外，有一串数字就像那些学生在Cource里面的数据不是很好的db设计。 – dfundako

此数据不存储在数据库中。我仅使用SQL Server从生命数据流生成报告。我正在尝试将JSON结构扁平化为与所有信息的表格行类似，例如：Cources.ID，Students.ID，Students.LName – kaplievabell

答

我创建临时表，进口JSON数据到它，这allowerd我有WHERE子句：

DROP TABLE IF EXISTS tempdb.dbo.#temp; 
CREATE TABLE #temp (
InfoJson nvarchar(max) 
) 

ALTER TABLE #temp 
ADD CONSTRAINT [Content should be formatted as JSON] 
CHECK (ISJSON(InfoJson)> 0) 

Insert INTO #temp (InfoJson) 
SELECT * 
FROM OPENROWSET (BULK 'C:\Data\file.json', SINGLE_CLOB) as j; 

SELECT Cource.ID, 
Cource.Name as CName, 
Cource.Room as CRoom, 
CourceStudents.LName 
FROM #temp 

CROSS APPLY OPENJSON(InfoJson,'$') WITH( 
    Cources nvarchar(max) AS JSON, 
    TAs nvarchar(max) AS JSON, 
    Students nvarchar(max) AS JSON 
    ) AS [SampleData] 

CROSS APPLY OPENJSON(Cources) WITH (
    Room integer, 
    Name nvarchar(max), 
    StudentsIDs nvarchar(max) AS JSON 
) as [Cources] 

CROSS APPLY OPENJSON(StudentsIDs) WITH (
    value nvarchar(100) '$' 
) as [StudentsIDs] 

CROSS APPLY OPENJSON(Students) WITH ( 
    ID integer, 
    LName nvarchar(max), 
) as [Students] 

WHERE StudentsIDs.value=Students.ID

SQL Server在嵌套的JSON数据结构中获取数据

相关推荐