Replace and copy data

EstherT

Hi all,

I've been wrapping my head around this, I'm not entirely sure how to do this. I have a dataset that looks like json format and need to rename some fields and copy the data. Some values will be replaced with others. If I only have data with 1 auto_incremented_id, i'm able to change and add the data that I want. If I have multiple data (multiple auto_incremented_id"), I get stuck with my workflow.

So this is my original data:

[{"blue_orchid":"7.89","data_origin":"government_inventory","country_code":"DEU","auto_incremented_id":"1"},{"blue_orchid":"1.56","data_origin":"corporate_lca","country_code":"AUS","auto_incremented_id":"2"},{"blue_orchid":"9.12","data_origin":"third_party_verified","country_code":"MEX","auto_incremented_id":"3"}]

This is what I need to do:

add "blue_orchid_2" with same data as blue_orchid"
field "data_origin" to be replaced with "source"
value "government_inventory" to be replaced with "abc"
value "corporate_lca" to be replaced with "123"
value "third_party_verified" to be replaced with "xxx"
the rest of the data should remain untouched

This is the output wanted:

[{"blue_orchid":"7.89","blue_orchid_2":"7.89","data_origin":"government_inventory","source":"abd","country_code":"DEU","auto_incremented_id":"1"},{"blue_orchid":"1.56","blue_orchid_2":"1.56","data_origin":"corporate_lca","source":"123","country_code":"AUS","auto_incremented_id":"2"},{"blue_orchid":"9.12","blue_orchid_2":"9.12","data_origin":"third_party_verified","source":"xxx","country_code":"MEX","auto_incremented_id":"3"}]

Any ideas on how to tackle this?

Many thanks!

dougperez

Hi EstherT!

I would avoid trying to manipulate the full JSON string directly. A better approach is to parse the JSON into rows, reshape it, apply the field logic, and then rebuild the JSON.

The important part is to use the array index as the grouping key. After using the JSON Parse tool, you should get field paths like:

[0].blue_orchid
[0].data_origin
[1].blue_orchid
[1].data_origin

From there, create two fields with a Formula tool:

Object_Index:

REGEX_Replace([JSON_Name], "^\[(\d+)\].*", "$1")

Field_Name:

REGEX_Replace([JSON_Name], "^\[\d+\]\.", "")

Then use a Cross Tab tool:

• Group by: Object_Index
• New Column Headers: Field_Name
• Values: JSON_ValueString
• Method: First

This will give you one row per JSON object, with fields like blue_orchid, data_origin, country_code, and auto_incremented_id.

After that, use a Formula tool to create the new fields:

blue_orchid_2 = [blue_orchid]

IF [data_origin] = "government_inventory" THEN "abc"
ELSEIF [data_origin] = "corporate_lca" THEN "123"
ELSEIF [data_origin] = "third_party_verified" THEN "xxx"
ELSE [data_origin]
ENDIF

Then you can either use the JSON Build tool to rebuild the JSON, or manually build each JSON object with a Formula tool and concatenate the rows back together with a Summarize tool.

The key is that the multiple auto_incremented_id values are not the real issue. The issue is preserving the object-level grouping, and the array index from the parsed JSON is what lets you do that.