CODEWITHSUNDEEP
azureazure-data-lakeu-sql
Tdawg90
Tdawg90

Reputation: 117

U-SQL trying to Extract a list of ints from nested JSON object

I'm trying to extract the ErrorReasons, along with the State and Id from this structure

{
    "id": "abcGuid",
    "RegistrationStatus": {
        "State": 2,
        "ErrorReasons": [
            1,2,3
        ]
    },
}

I can extract the Id, RegistrationStatus, and State, however I'm struggling with getting the list of ints out of the ErrorReasons list.

My starting Extract

@result =
    EXTRACT 
         [id] string
        ,[RegistrationStatus] string
    FROM @inputFileDaily
    USING new Microsoft.Analytics.Samples.Formats.Json.JsonExtractor();

Then I pull out the State, and ErrorReasons into their own fields

@pre =
    SELECT JsonFunctions.JsonTuple(RegistrationStatus) ["State"] AS State,
           JsonFunctions.JsonTuple(RegistrationStatus) ["ErrorReasons"] AS ErrorReasons_data
    FROM @result;

@pre =
    SELECT State,
           JsonFunctions.JsonTuple(ErrorReasons_data).Values AS ErrorReasons_Array
    FROM @pre;

@CreateJSONTuple =
    SELECT State,
           JsonFunctions.JsonTuple(ErrorReasons)["0"] AS ErrorReason
    FROM @pre
         CROSS APPLY
        EXPLODE(ErrorReasons_Array) AS c(ErrorReasons);

This of course gets nothing, cause it's looking for the field "0" if I leave out the ["0"], then it complains about outputting a sqlmap<string,string> to csv. I've tried .Value, .Values, ["1"], [0], [1] .Values doesn't work for me either of course. I feel I'm really close, just missing a key operation to pull out the list of values from Error Reasons

Upvotes: 0

Views: 68

Answers (1)

Tdawg90
Tdawg90

Reputation: 117

with a little rubber ducking, and less over thinking it I figured it out,

@CreateJSONTuple = 
SELECT 
       A.ErrorReason AS Reason
FROM @pre
     CROSS APPLY
        EXPLODE (ErrorReasons_Array) AS A(ErrorReason);

and having found the correct documentation to follow https://learn.microsoft.com/en-us/u-sql/data-types/built-in/complex-built-in-u-sql-types

Upvotes: 1

Related Questions