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.
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…)
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
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?
