使用u-sql查询名称和城市

Question

我需要从嵌套的json下面过滤名称和城市。

[
{
"Employee": [
{
"name": "John",
"Gender": "Male"
}
],
"Info": {
"location": {
"city": "Kuvala",
"continent": "Asia",
"country": "Singapore"
}
}
},
{
"Employee": [
{
"name": "Mike",
"Gender": "Male"
}
],
"Info": {
"location": {
"city": "Dhabi",
"continent": "Asia",
"country": "Dubai"
}
}
}
]

我的代码

CREATE ASSEMBLY IF NOT EXISTS [Newtonsoft.Json] FROM @"/Assemblies/Newtonsoft.Json.dll";
CREATE ASSEMBLY IF NOT EXISTS [Microsoft.Analytics.Samples.Formats] FROM @"/Assemblies/Microsoft.Analytics.Samples.Formats.dll";
REFERENCE ASSEMBLY [Newtonsoft.Json];
REFERENCE ASSEMBLY [Microsoft.Analytics.Samples.Formats];
USING Microsoft.Analytics.Samples.Formats.Json;
DECLARE @InputFile string = @"/Source/example.blob";
DECLARE @output string = @"/Output/output.csv";
@json  =
EXTRACT Employee String,
Info String
FROM @InputFile
USING new Microsoft.Analytics.Samples.Formats.Json.JsonExtractor("*");
@CreateJSONTuple =
SELECT   JsonFunctions.JsonTuple(Employee) AS EmployeeData,
JsonFunctions.JsonTuple(Info) AS InfoData
FROM @json;
@result =
SELECT EmployeeData["name"] AS Name,
JsonFunctions.JsonTuple(InfoData, "location") AS LocationData
FROM @CreateJSONTuple;
OUTPUT @result
TO @output
USING Outputters.Csv(outputHeader : true, quoting : true);

我在说无法从"Microsoft.Analytics.Types.Sql.SqlArray"转换为"string"时出错，请提供帮助。

请帮我从这里得到名字，城市。这应该输出：约翰·武拉拉Mike Dhabi

Answer 1

我认为您会收到此错误，因为Employee元素的内容定义为数组：

"Employee": [
{
"name": "Mike",
"Gender": "Male"
}
]

尝试将Employee元素更改为以下内容：

"Employee": {
"name": "Mike",
"Gender": "Male"
}

更新

似乎您可能需要对数组项执行JsonTuple，因此这可能会起作用：

@result =
SELECT JsonFunctions.JsonTuple(EmployeeData[0])["name"] AS Name,
JsonFunctions.JsonTuple(InfoData, "location") AS LocationData
FROM @CreateJSONTuple;

需要注意的是，如果Employee数组可能包含多个项目，则可能需要使用交叉应用分解。

更新2

不幸的是，这是我能想到的最后一件事。尝试替换：

@CreateJSONTuple =
SELECT   JsonFunctions.JsonTuple(Employee) AS EmployeeData,
JsonFunctions.JsonTuple(Info) AS InfoData
FROM @json;
@result =
SELECT EmployeeData["name"] AS Name,
JsonFunctions.JsonTuple(InfoData, "location") AS LocationData
FROM @CreateJSONTuple;

有了这个：

@CreateJSONTuple =
SELECT   JsonFunctions.JsonTuple(Employee).Values AS EmployeeData,
JsonFunctions.JsonTuple(Info) AS InfoData
FROM @json;
@result =
SELECT JsonFunctions.JsonTuple(employee)["name"] AS Name,
JsonFunctions.JsonTuple(InfoData, "location") AS LocationData
FROM @CreateJSONTuple
CROSS APPLY
EXPLODE(EmployeeData) AS employees(employee) ;

希望它能有所帮助！