How to Rename JSON fields with the Select Tool (or any Tool)

[carltonp]

I have ingested a JSON file from Azure Data Lake using the Azure Data Lake File Input tool. I would now like to make some changes to the fields or should I say key/value pairs.

In order to make the changes, I would like to convert the JSON to a table with fields e.g JSON syntax { "name": "John", "age": 22, "gender": "male", } with fields name, age, gender

name	age	gender
John	22	male

At present I'm attempting to achieve this using the Parse JSON tool. In my live scenario I would like to use an Alteryx tool to remove the key/value pair "JSON_Name":"url"

Any thoughts on how to achieve this?

[Felipe_Ribeir0 · Accepted solution]

Hi @carltonp 

The attached workflow is a way to:

1)convert the output of the  json parse tool into a flat table.

2)select the necessary fields

3)convert the flat table back into a json

If the provided answer helped you to solve the problem/was correct, please accept it as a solution :)

Thanks.

[carltonp]

Felipe,

Thanks for reaching out. I'm about to test your solution.

[carltonp]

Hi Felipe, this solution appears to work, but it seems very complicated. 

[Felipe_Ribeir0 · Accepted solution]

Hi @carltonp 

Maybe it is a little to complicated when you see it the first time, but it is a standard way to transform a JSON into a table, and a table into a JSON. Once you do it some times, you will get used to it.

1) The regex component is separing the id of the register from the field name.

2) the cross tab component is using the id of the register and the field names to transform it into a table.

3)the transpose component is transforming the table into a format that a JSON builder can understand.

4)the JSON Builder is constructing the JSON.

Dealing with JSON/XML structures bring some complexity to the table, its part of the problem.

If the provided answer helped you to solve the problem/was correct, please accept it as a solution :)

Thanks.

[carltonp]

Hi Felipe,

You're quite right.

The more I look it at, the more it makes sense - and the easier it is to understand.

I have just a completed a Udemy course on Alteryx so I am still very new. 

At the moment I use Databricks to create all of my ETL pipelines, but since discovering Alteryx I am loving the way Alteryx uses worksflows to do the same thing as Databricks.

Thank you.

[carltonp]

2) the cross tab component is using the id of the register and the field names to transform it into a table.

I really like this ... :-)_

[carltonp]

Can you let me know why the "id of the register" is captured. I don't get the id register when using other ETL tools e.g. Databricks

[Felipe_Ribeir0]

@carltonp when you look before the regex and after the regex component, you will see that before the regex you have a number concatenated with the field name. This number is unique for each row. You need to separate the number from the field name, so you have a reference for the row and a unique field name for all the rows.

Makes sense?

[carltonp]

It makes sense. However, I guess my question is 'Does Alteryx generate that unique record' e.g 0. and 1. ?