英文:
How to retrieve objects out of Json in Azure Data Factory
问题
I have a Json file that when run by Data Factory only contains one nested object "Properties" and it is fine but Value4 looks like another nested object and is being visible as one long string, although it contains multiple columns, same thing with Text1, is this a proper json format? I cannot figure it out how to flatten this, the only value that is detected by ADF is Properties as an object, Value4 and Text1 is not recognized by ADF as an object or array, just one long string of values.
Any idea how to fix this problem or the Json is really badly created?
{"RoleInstance":"HIDDEN","City":"Boston","Country":"UnitedStates","ClientIP":"0.0.0.0","ClientStateOrProvince":"MA"
"Properties":
{"Value1":"Test","Value2":"12345","Value3":"User",
"Value4":
{"HostSpecType":"User","ListSpecType":"User","RoleID":"156","OwnerIDs":[159],"OwnerID":3,"ColumnsToLoad":["UsrID","SpecID","SpecName","SpecTyp","SpecOpt","ParentSpecID","ParentSpecTyp","InternalVersion","Email","Office","DispName","FirstName","LastName","Phone","OfcID","UserPrin","EmplID"]},
"UserID":"498","MachineName":"QG6T",
"Text1":
{"ID":"1596","Name":"LaunchSchedule","Type":"Initialize Mailbox Monitor","AppID":"1002"},
"TaskID":"16415",
"JobID":"167"},
"ResourceGUID":"c94e4","SDKVersion":"dotnet:2-21496"}
英文:
I have a Json file that when run by Data Factory only contains one nested object "Properties" and it is fine but Value4 looks like another nested object and is being visible as one long string, altough it contains multiple columns, same thing with Text1, is this a proper json format ? I cannot figure it out how to flatten this the only value that is detected by ADF is Properties as an object, Value4 and Text1 is not recognized by ADF as an object or array, just one long string of values
Any idea how to fix this problem or the Json is really badly created?
{"RoleInstance":"HIDDEN","City":"Boston","Country":"UnitedStates",]"ClientIP":"0.0.0.0","ClientStateOrProvince":"MA"
"Properties":{"Value1":"Test","Value2":"12345","Value3":"User",
"Value4":"{"HostSpecType":"User","ListSpecType":"User","RoleID":"156","OwnerIDs":[159],"OwnerID":3,"ColumnsToLoad":["UsrID","SpecID","SpecName","SpecTyp","SpecOpt","ParentSpecID","ParentSpecTyp","InternalVersion","Email","Office","DispName","FirstName","LastName","Phone","OfcID","UserPrin","EmplID"]}",
"UserID":"498","MachineName":"QG6T",
"Text1":"{"ID":"1596","Name":"LaunchSchedule","Type":"Initializa Mailbox Monitor","AppID":"1002"}",
"TaskID":"16415",
"JobID":"167"},
"ResourceGUID":"c94e4","SDKVersion":"dotnet:2-21496"}
答案1
得分: 1
根据您的 Json 数据,我尝试在我的环境中复制相同的内容,得到了相同的字符串数值。
如果您将 Json 修改为以下内容,它将解决您的问题:
{
"RoleInstance":"HIDDEN",
"City":"Boston",
"Country":"UnitedStates",
"ClientIP":"0.0.0.0",
"ClientStateOrProvince":"MA",
"Properties":
{
"Value1":"Test",
"Value2":"12345",
"Value3":"User",
"Value4":
{
"HostSpecType":"User",
"ListSpecType":"User",
"RoleID":"156",
"OwnerIDs":[159,144],
"OwnerID":3,
"ColumnsToLoad":
[
"UsrID","SpecID","SpecName","SpecTyp","SpecOpt","ParentSpecID","ParentSpecTyp","InternalVersion","Email","Office","DispName","FirstName","LastName","Phone","OfcID","UserPrin","EmplID"
]
},
"UserID":"498",
"MachineName":"QG6T",
"Text1":
{
"ID":"1596",
"Name":"LaunchSchedule",
"Type":"Initializa Mailbox Monitor",
"AppID":"1002"
},
"TaskID":"16415",
"JobID":"167"
},
"ResourceGUID":"c94e4",
"SDKVersion":"dotnet:2-21496"
}
这将修复您的问题。
英文:
As per your Json, I tried to reproduce the same in my environment I got the same string values.
{
"RoleInstance":"HIDDEN",
"City":"Boston",
"Country":"UnitedStates",
"ClientIP":"0.0.0.0",
"ClientStateOrProvince":"MA",
"Properties":
{
"Value1":"Test",
"Value2":"12345",
"Value3":"User",
"Value4":'{"HostSpecType":"User","ListSpecType":"User","RoleID":"156","OwnerIDs":[159],"OwnerID":3,"ColumnsToLoad":["UsrID","SpecID","SpecName","SpecTyp","SpecOpt","ParentSpecID","ParentSpecTyp","InternalVersion","Email","Office","DispName","FirstName","LastName","Phone","OfcID","UserPrin","EmplID"]}',
"UserID":"498",
"MachineName":"QG6T",
"Text1":'{"ID":"1596","Name":"LaunchSchedule","Type":"Initializa Mailbox Monitor","AppID":"1002"}',
"TaskID":"16415",
"JobID":"167"
},
"ResourceGUID":"c94e4",
"SDKVersion":"dotnet:2-21496"
}
If you modified the Json like this, It will fix your problem:
{
"RoleInstance":"HIDDEN",
"City":"Boston",
"Country":"UnitedStates",
"ClientIP":"0.0.0.0",
"ClientStateOrProvince":"MA",
"Properties":
{
"Value1":"Test",
"Value2":"12345",
"Value3":"User",
"Value4":
{
"HostSpecType":"User",
"ListSpecType":"User",
"RoleID":"156",
"OwnerIDs":[159,144],
"OwnerID":3,
"ColumnsToLoad":
[
"UsrID","SpecID","SpecName","SpecTyp","SpecOpt","ParentSpecID","ParentSpecTyp","InternalVersion","Email","Office","DispName","FirstName","LastName","Phone","OfcID","UserPrin","EmplID"
]
},
"UserID":"498",
"MachineName":"QG6T",
"Text1":
{
"ID":"1596",
"Name":"LaunchSchedule",
"Type":"Initializa Mailbox Monitor",
"AppID":"1002"
},
"TaskID":"16415",
"JobID":"167"
},
"ResourceGUID":"c94e4",
"SDKVersion":"dotnet:2-21496"
}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论