JSON To Table format

[AB]

I am trying to Parse JSON and then convert into the table format 

I am getting this result after parsing and some cleansing 

content.seriesCode	0K21
content.seriesName	Tuna alba whs ES
content.seriesDescription	Albacore tuna
content.currencyName	Pound
content.unitName	Kilogram
content.frequencyName	Daily
content.countryOfOriginName	None
content.countryOfDeliveryName	France
content.originType	0
content.points.0.date	25/09/2023
content.points.0.value	4
content.points.1.date	26/09/2023
content.points.1.value	4
content.points.2.date	27/09/2023
content.points.2.value	4
content.points.3.date	28/09/2023
content.points.3.value	4
content.points.4.date	29/09/2023
code	1
content.seriesCode	ON28
content.seriesName	Acetic acid cont ddp W Eur
content.seriesDescription	Acetic acid|
content.currencyName	European Euro
content.unitName	Metric Tonne
content.frequencyName	Daily
content.countryOfOriginName	None
content.countryOfDeliveryName	West Europe
content.originType	0
content.points.0.date	25/09/2023
content.points.0.value	1030
content.points.1.date	26/09/2023
content.points.1.value	1030
content.points.2.date	27/09/2023
content.points.2.value	1030
content.points.3.date	28/09/2023
content.points.3.value	1030
content.points.4.date	29/09/2023
content.points.4.value	1030
code	1

I want to convert it into Table  format like this. Any help would be greatly appreciated! 

content.seriesCode	content.seriesName	content.seriesDescription	content.currencyName	content.unitName	content.frequencyName	content.countryOfOriginName	content.countryOfDeliveryName	Date	VALUE
0K21	Tuna alba whs ES	Albacore tuna|fresh|wholesale price|Spain||[Thunnus alalunga]	Pound	Kilogram	Daily	None	France	25/09/2023	4
0K21	Tuna alba whs ES	Albacore tuna|fresh|wholesale price|Spain||[Thunnus alalunga]	Pound	Kilogram	Daily	None	France	26/09/2023	4
0K21	Tuna alba whs ES	Albacore tuna|fresh|wholesale price|Spain||[Thunnus alalunga]	Pound	Kilogram	Daily	None	France	27/09/2023	4
0K21	Tuna alba whs ES	Albacore tuna|fresh|wholesale price|Spain||[Thunnus alalunga]	Pound	Kilogram	Daily	None	France	28/09/2023	4
0K21	Tuna alba whs ES	Albacore tuna|fresh|wholesale price|Spain||[Thunnus alalunga]	Pound	Kilogram	Daily	None	France	29/09/2023	1
ON28	Acetic acid cont ddp W Eur	Acetic acid|monthly contract price|delivered duty paid|West Europe|	European Euro	Metric Tonne	Daily	None	West Europe	25/09/2023	1030
ON28	Acetic acid cont ddp W Eur	Acetic acid|monthly contract price|delivered duty paid|West Europe|	European Euro	Metric Tonne	Daily	None	West Europe	26/09/2023	1030
ON28	Acetic acid cont ddp W Eur	Acetic acid|monthly contract price|delivered duty paid|West Europe|	European Euro	Metric Tonne	Daily	None	West Europe	27/09/2023	1030
ON28	Acetic acid cont ddp W Eur	Acetic acid|monthly contract price|delivered duty paid|West Europe|	European Euro	Metric Tonne	Daily	None	West Europe	28/09/2023	1030
ON28	Acetic acid cont ddp W Eur	Acetic acid|monthly contract price|delivered duty paid|West Europe|	European Euro	Metric Tonne	Daily	None	West Europe	29/09/2023	1030

[OTrieger]

Columns Tool will do the job. Select horizontally and 19

[CoG]

I'm not sure how the Make Columns Tool solves this particular problem. The Cross Tab Tool is what jumps to the top of my mind for solving this. The added bit of trickery, however comes in the fact that you do not want to Cross Tab all of the data - the [date/value] fields need to be Cross Tab-ed separately. This, then, requires a multi-step process. We will need to index the dataset to maintain each record appropriately with both RecordID and SubID (See workflow for details), and then bring both datasets back together via Join Tool:

Hope this helps and Happy Solving!

[apathetichell]

use multi-row formula to create a primary key (assuming column names are [Name]/[Value]

new text column called Key

if [Name]="content.seriesCode" then [Value] else [Row-1:Key] endif --- make this a vstring or something non numeric)

new - cross tab - use key as a primary key - name is your column/value is your value. concatenate is your default way to handle duplicate entires. 

next use a transpose - > mark all your columns which are not date/values for the date fields as primary keys -> put the dates/values in the fields to be transposed

next we are going to use a formula tool:

we will create a new field called entry - which will correspond to the specific sub entity in your json for dates.

I would use:

regex_replace([Name],".*_(\d+)_.*","$1")

next you will change [Name] in another formula:

regex_replace([Name],"^(.*)_\d+(.*)","$1$2")

this will standardize the [Name] field for the date/value - and drop the number - since you have split this out.

Next - back to cross tab.

everything but [Name] and [Value] are your primary keys. - include entry in the keys.

name is your column name

value is your value.

concatenated because it makes the most sense.

I've never used the make columns tool - but I'm not seeing it here. I think with the date/value nesting there's probably some other handling you'd want to do.

[OTrieger]

@CoG 
The make Columns tool solve that easily, instead of all the tools that you were using to get the same result, 1 tool does it all.

[OTrieger]

This one tool replacing the 9 tools that you were using to get the exact some result.

[AB]

would you be able to share your workflow, please can't seem to make it work as you have! Many thanks

[apathetichell · Accepted solution]

@OTrieger 1) it's not dynamic 2) it doesn't teach a user how to pivot/cross tab data. 3) - 

content.points.0.date	25/09/2023
content.points.0.value	1030
content.points.1.date	26/09/2023
content.points.1.value	1030
content.points.2.date	27/09/2023
content.points.2.value	1030
content.points.3.date	28/09/2023
content.points.3.value	1030
content.points.4.date	29/09/2023
content.points.4.value	1030

need to be transposed so that each point is on a seperate line. so no - it's not just one tool.

workflow attached.