JSON parsing for order data

[cason]

Hi,

I have JSON order data from an e-commerce site in the following structure:

{
    "id": 3340,
    "parent_id": 0,
    "status": "processing",
    "customer_id": 0,
    "total": "920",
    "total_tax": "0",
    "billing": {
        "first_name": "John",
        "last_name": "Doe",
        "company": "",
        "address_1": "8 Something Row",
        "address_2": "",
        "city": "London",
        "state": "",
        "postcode": "WC6 3XB",
        "country": "GB",

My goal here to create two tables, one called OrderHeader where all attributes before ‘billing’ is included and called OrderCustomer where all the attributes in the ‘billing’ is included and create a key in-between (perhaps orderID).

I have tried to do this with so any tries with the JSON path node but this only results in not extracting single attributes but all of them:

Screen Shot 2018-11-03 at 15.24.46

Do anyone have a good structure of how to set this up (including the settings needed) or perhaps a workflow saved that completes this?

Thanks so much for your help!

[mlauber71]

You could have a look at this examples. Key is to define a correct path (this took me a few tries sometimes) and you might need to ungroup the result in order to get a ‘normal’ table.

Json to Table

I just stumbled across this entry and maybe I can help since I had a similar problem recently. The point is that in order to import a JSON file you have to Import it with the JSON reader define a JSON path to the variables ungroup the variables Then you have a normal table with columns. json.knar

or this

Problems with parsing a XML-like file

I attach a workflow that extracts your values. I converted the XML file to JSON and then used the JSON path editor to define a path. The tricky thing is to find the path. That took some time. I exported the JSON file and used an editor (Atom on MacOS) to find the right row. Then you would have to click on an item you want and hopefully it would select and mark blue the values you want. I am not a JSON expert so I would have to click around until it fits. Then the workflow would ungroup the valu…

[qqilihq]

… and an additional note which jumped to my eye:

Your structure is not a single object { … }, but an/many object(s) within an array [ { … } ] (note the [ character in the JSON-Cell Preview).

– Philipp

[Geo]

yup, have a look into the ungroup node