Azure Data Flow: Parse nested list of objects from JSON String

Question

I need to parse JSON data from a string inside a Azure Data Flow. So far, I was able to parse all my data using the "Parse" function of the Data Flows. But now I am faced with a list of objects, and I don't know how to parse the values of that "complex array".

My data is looking like this:
There are some metadata fields (here null) and a Base64 encoded Body field. Every JSON document is in a separate JSON file.

The example data:

{
    "metadata": null,
    "Body": "eyJpZCI6ICIxIiwgImNvdW50IjogMTIsICJwcm9qZWN0cyI6IFt7Imd1aWQiOiAiMTIzMTItMTI0MzE0LTEyNDEtMTIzNCIsICJzdGF0dXMiOiAic3RvcHBlZCJ9LHsiZ3VpZCI6ICJjc2lzdi1uZDkyM24tMTM0MS0yMzQxIiwgInN0YXR1cyI6ICJydW5uaW5nIn1dfQ"
}

For clarification, the encoded example data looks like this:

{
    "metadata": null,
    "Body": "{"id": "1", "count": 12, "projects": [{"guid": "12312-124314-1241-1234", "status": "stopped"},{"guid": "csisv-nd923n-1341-2341", "status": "running"}]}"
}

My goal is to have a parquet file containing the data from the Body. So there should be three columns: id, count, projects. Projects should contain a list of complex objects.

The final result should look like this:

This is the result, when I load a JSON file, where the Body data is not encoded, but plain JSON containing the list of objects. Note, that this is not feasible for the original problem, where the JSON data is Base64 encoded.

When I load the example data into a dataflow the projection looks like this (as expected):

First, I need to decode the Base64 Body and then I can parse the JSON string:

Decoding Body is done here:

And basic parsing is done here:

How can I parse the field "projects"? Given that every object in the list of the array field has the same schema.

I already tried parsing the field "projects" as string and add another Parse step to parse this string as "Array of documents", but the results are only Null values..

Mike S · Accepted Answer · 2021-07-30 13:41:59Z

The final Dataflow looks like this:

The parsing has to be splitted in several parts.

First, the array needs to be parsed as a string array
The string array has to be exploded
Now, every array entry can be parsed
The exploded array can be collected back to gain the structure I wanted to have
Finally, the exploded and recollected data can be rejoined to the original data

Parse array of objects as string array

Remember: The data I want to parse looks like this:

{
    "metadata": null,
    "Body": "{"id": "1", "count": 12, "projects": [{"guid": "12312-124314-1241-1234", "status": "stopped"},{"guid": "csisv-nd923n-1341-2341", "status": "running"}]}"
}

So first I need to parse the "Body" column, which is BodyDecoded, since I first had to decode from Base64. The array of objects has to be parsed as array of strings. Hence, the "Output column type" of the Parse step looks like this:

(id as string,
count as integer,
projects as string[])

The values are written in the BodyContent column. To make the coming steps easier first the hierarchy is flattened. It is possible to use a column pattern for that, but I will do it explicitly here:

Also, the projects column is now renamed to projectsStringArray

Explode string array

Now the projectsStringArray can be exploded using the "Flatten" step.

The column id is also taken here, to be able to recollect the array later.

The result looks like this:

Parse JSON strings

Now every string can be parsed by a "Parse" step, as usual

(guid as string,
 status as string)

Collect parsed objects

The parsed objects can be aggregated in lists again, using the "collect" function.

Rejoin to original data

To get the desired structure the collected column has to be joined to the original data. The id column can be used to join the data back.

Result

After a final select, the structure looks as required:

Remarks: Thanks to Erik from Microsoft for his help! I was too focused on solving it using only the parsing step, that I didn't think about other ways to tackle the problem..

Collectives™ on Stack Overflow

Azure Data Flow: Parse nested list of objects from JSON String

1 Answer 1

Parse array of objects as string array

Explode string array

Parse JSON strings

Collect parsed objects

Rejoin to original data

Result

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Parse array of objects as string array

Explode string array

Parse JSON strings

Collect parsed objects

Rejoin to original data

Result

Comments

Your Answer

Sign up or log in

Post as a guest

Related