Azure / Azure/usql

JSONPath conditionnal selection issue while using JSON Extractor

Open
#110 1 comment 0 reactions 0 assignees View on GitHub
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.