2

I have the following JSON file :

Json Image

Created with the following source code :

{ "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

Brian Rogers
  • 125,747
  • 31
  • 299
  • 300
saad0n87
  • 67
  • 6

1 Answers1

1

I figured out another way to do it using the MultiLevelJsonExtractor . Here is the code :

 REFERENCE ASSEMBLY JSONBlog.[Newtonsoft.Json];
 REFERENCE ASSEMBLY JSONBlog.[Microsoft.Analytics.Samples.Formats]; 

 USING Microsoft.Analytics.Samples.Formats.Json;

 DECLARE @InputPath string = "/Samples/PCISData/BC_INFOWARE_PCD_FR_20171208072518_6.json";

 DECLARE @OutputFile string =      "/Samples/StandardizedData/Output_BC_Multilevel.csv";

 @resultintint =
EXTRACT PersonType string,
        IdentificationId string,
        FullName string
FROM @InputPath
USING new MultiLevelJsonExtractor("Person[*]",
      true,
      "PersonType", "PersonIdentification.IdentificationId",            
      "Name[?(@.NameType == 'FULL_NAME')].NameValue");

 //Output the file to a tool of your choice.
 OUTPUT @resultint
 TO @OutputFile
 USING Outputters.Csv(outputHeader:true,quoting:false);
saad0n87
  • 67
  • 6