JSONPath conditionnal selection issue while using JSON Extractor
- Dominant language
- C#
- Stars
- 231
- Forks
- 666
- PR merge metrics
- No merged PRs in 30d
Description
I have the following JSON file
`{
"Person": {
"PersonType": "BC",
"PersonIdentification": {
"IdentificationIdType": "INFOWARE_ID",
"IdentificationId": "1009"
},
"MatriculeRH": {
"MatriculeIdType": null,
"MatriculeId": null
},
"Affectation": {
"POSIdentification": {
"POSIdType": "INFOWARE",
"POSId": "G504"
},
"ActivitySector": null,
"BrandCode": null,
"MarketCode": "DE",
"Channel": "06",
"POSStartDate": null,
"POSEndDate": null,
"JobTitle": "Beauty consultant",
"Status": "PERMANENT"
},
"Classification": {
"IdGroup": null,
"Specialization": {
"Specialization": null
},
"Responsable": null
},
"BirthDate": null,
"Name": [{
"NameType": "FIRST_NAME",
"NameValue": "Rosemarie"
},
{
"NameType": "LAST_NAME",
"NameValue": "Algermissen"
},
{
"NameType": "FULL_NAME",
"NameValue": "Rosemarie Algermissen"
}]
}
}
`
I want to retrieve the NameValue in Name Array matching the condition NameType == "FULL_NAME" but for some reason I didn't manage to make it work.
Here is the code I used
`REFERENCE ASSEMBLY JSONBlog.[Newtonsoft.Json];
REFERENCE ASSEMBLY JSONBlog.[Microsoft.Analytics.Samples.Formats];
USING Microsoft.Analytics.Samples.Formats.Json;
DECLARE @InputPath string = "/Samples/PCISData/BC__DE_20170920113743_18.json";
DECLARE @OutputFile string = "/Samples/StandardizedData/Output2.csv";
@RawData =
EXTRACT
[Person] string
,filename string//virtual column
FROM @InputPath
USING new JsonExtractor();
@CreateJSONTuple =
SELECT
JsonFunctions.JsonTuple([Person], "..*") AS PersonData
FROM @RawData;
@Dataset =
SELECT
PersonData["PersonType"] AS PersonType,
PersonData["PersonIdentification.IdentificationId"] AS PersonIdentification,
PersonData["Name[?(@.NameType == 'FULL_NAME').NameType[0]"] ?? "NotFound" AS FirstName
FROM @CreateJSONTuple;
OUTPUT @Dataset
TO @OutputFile
USING Outputters.Csv(outputHeader:true,quoting:true);`
And here is the output I get
`"PersonType","PersonIdentification","FirstName"`
`"BC","1009","NotFound"`
Any help would be really appreciated.
Regards
Contributor guide
Assessment
This issue has not been assessed yet.