Hi, so I have created workflow as below which basically combination of Python code to call REST API function to return JSON file, write it to a CSV which then got uploaded to a SQL server. The workflow works perfectly when I am using combination of JSON reader, JSON path and Ungroup function. The only problem is below for ShippingAddress column which I want to return as a proper JSON object and store it as a list column. However it did not return the double quotes parameters hence I could not parse the column in Azure using SQL script.

the workflow

the JSONpath config

the JSONpath config for ShippingAddress

as you can see here, the shippingAddress JSON object is ungroup incorrectly without the double quotes

I hope above make sense and anyone can help me! I am stuck here…

Hi @nivla88 -

First off, welcome to the forum!

It’s interesting that the JSONPath expression for shippingAddress is the only one that contains single quotes. Have you tried manually editing those out? You say all the other columns work OK, so maybe it’s as simple as that?

(It’s also entirely possible I’m misunderstanding your problem…)

1 Like

Hi @ScottF,

thank you for your response. Basically I can’t edit those JSON file as it is retrieved from the original API source. Knime allows the other fields such as Items, and ShippingInformation as JSON object/arrays and keep the double quotes when ungroup them into table. The format for shippingAddress is it does not have square bracket before the curly bracket, so when I add JSONpath for the column in Knime it ungrouped succesfully but remove the double quotes. so the format become like this in CSV (which is not a proper JSON format)…

{addressOne=ebay:1qs9r39, addressTwo=11 Sample, addressThree=, city=Fairfield Heights, country=Australia, county=NSW, postCode=2165}

I attached two sample files here, one is for the JSON file. the second is the output file. What I want from the output file is to have shippingAddress retrieved with double quotes. Any help would be really appreciated

JSON sample.json (3.6 KB)

Output Sample.xlsx (10.0 KB)

Hi @nivla88 -

I think I understand a bit better now, but I’m far from a JSON expert - sorry! Is it possible that using the JSON Transformer node would help to reformat the brackets from your original source before proceeding with the JSON Path node?

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.