JSON parsing for order data


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!

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.

or this

1 Like

… 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

yup, have a look into the ungroup node