Working with Json

[LeeJBS]

Hi All

Im a bit of a beginner here

I need to pull some data from an API. This part is working ok but what I need to do is extract some data. Below is a snippet of the JSON i have. 

What i need to extract is 

"generic_serial": "044319878852",

"imei": "",

"name": "Microsoft Surface Laptop 3 - Platinum",

"product_category": "Laptop",

"login": "Barry.Jones@domain.com",

How do I extract that out of the different objects so I can add each to a row in a table in a Excel document? 

{
    "response": [
        {
            "id": 3786731,
            "ref_device_id": 1046832330,
            "business_id": 748,
            "esn": "",
            "meid": "",
            "imei": "",
            "msn": "",
            "status": "normal",
            "created_at": "2020-04-20T16:29:49.280-04:00",
            "updated_at": "2020-04-20T17:56:50.281-04:00",
            "managed": true,
            "person_id": 14482033,
            "generic_serial": "044319878852",
            "liability": "corporate",
            "warehouse_status": "not_tracked",
            "external_id": null,
            "full_device_profile": {
                "device_url": "http://URL.com.au/devices/3786731"
            },
            "line_id": null,
            "ref_device": {
                "id": 1046832330,
                "name": "Microsoft Surface Laptop 3 - Platinum",
                "description": "o\tCore i7-1065G7\r\no\t16GB DDR4, \r\no\t256GB SSD \r\no\tColour – Platinum\r\no\tScreen Size 13.5 Inches\r\n",
                "image_file_name": "SurfaceLaptop3PlatinumFront.jpg",
                "sku": "",
                "color": "Platinum",
                "platform": "",
                "product_category": "Laptop",
                "manufacturer": "Microsoft",
                "consumer_sku": "",
                "size": "256 GB",
                "manufacturer_sku": "",
                "carrier_name": null,
                "supplier_sku": null
            },
            "person": {
                "id": 14482033,
                "name_last": "Jones",
                "name_first": "Barry",
                "name_middle": null,
                "group_id": 3469,
                "email": "Barry.Jones@domain.com",
                "login": "Barry.Jones@domain.com",
                "employee_id": "987654",
                "created_at": "2020-03-24T15:59:15.701-04:00",
                "updated_at": "2020-03-27T18:15:47.372-04:00",
                "cost_center": "",
                "active": true,
                "customer_data": {},
                "group_name": "Employees"
            }
        },

 Thanks in advance.

[ChristianAbata · Accepted solution]

hi @LeeJBS  what you could do is parse json twice in the second json you need to use just the item 

ref_device

[LeeJBS]

Hi @ChristianAbata Thankyou so much. Thats exactly what I needed.

So im assuming it I need to pull out data from the Person object i just do the same thing there.

Thanks

[Jcook · Accepted solution]

Hello @LeeJBS 

You need to use the Parse JSON action.

Than you pass in the JSON as the output.

To get the schema, you can either:

a) Generate from sample and put a sample JSON message

OR

b) I have generated a sample for you, which can be pasted inside the schema box.

{
    "type": "object",
    "properties": {
        "response": {
            "type": "array",
            "items": {
                "type": "object",
                "properties": {
                    "id": {
                        "type": "integer"
                    },
                    "ref_device_id": {
                        "type": "integer"
                    },
                    "business_id": {
                        "type": "integer"
                    },
                    "esn": {
                        "type": "string"
                    },
                    "meid": {
                        "type": "string"
                    },
                    "imei": {
                        "type": "string"
                    },
                    "msn": {
                        "type": "string"
                    },
                    "status": {
                        "type": "string"
                    },
                    "created_at": {
                        "type": "string"
                    },
                    "updated_at": {
                        "type": "string"
                    },
                    "managed": {
                        "type": "boolean"
                    },
                    "person_id": {
                        "type": "integer"
                    },
                    "generic_serial": {
                        "type": "string"
                    },
                    "liability": {
                        "type": "string"
                    },
                    "warehouse_status": {
                        "type": "string"
                    },
                    "external_id": {},
                    "full_device_profile": {
                        "type": "object",
                        "properties": {
                            "device_url": {
                                "type": "string"
                            }
                        }
                    },
                    "line_id": {},
                    "ref_device": {
                        "type": "object",
                        "properties": {
                            "id": {
                                "type": "integer"
                            },
                            "name": {
                                "type": "string"
                            },
                            "description": {
                                "type": "string"
                            },
                            "image_file_name": {
                                "type": "string"
                            },
                            "sku": {
                                "type": "string"
                            },
                            "color": {
                                "type": "string"
                            },
                            "platform": {
                                "type": "string"
                            },
                            "product_category": {
                                "type": "string"
                            },
                            "manufacturer": {
                                "type": "string"
                            },
                            "consumer_sku": {
                                "type": "string"
                            },
                            "size": {
                                "type": "string"
                            },
                            "manufacturer_sku": {
                                "type": "string"
                            },
                            "carrier_name": {},
                            "supplier_sku": {}
                        }
                    },
                    "person": {
                        "type": "object",
                        "properties": {
                            "id": {
                                "type": "integer"
                            },
                            "name_last": {
                                "type": "string"
                            },
                            "name_first": {
                                "type": "string"
                            },
                            "name_middle": {},
                            "group_id": {
                                "type": "integer"
                            },
                            "email": {
                                "type": "string"
                            },
                            "login": {
                                "type": "string"
                            },
                            "employee_id": {
                                "type": "string"
                            },
                            "created_at": {
                                "type": "string"
                            },
                            "updated_at": {
                                "type": "string"
                            },
                            "cost_center": {
                                "type": "string"
                            },
                            "active": {
                                "type": "boolean"
                            },
                            "customer_data": {
                                "type": "object",
                                "properties": {}
                            },
                            "group_name": {
                                "type": "string"
                            }
                        }
                    }
                },
                "required": [
                    "id",
                    "ref_device_id",
                    "business_id",
                    "esn",
                    "meid",
                    "imei",
                    "msn",
                    "status",
                    "created_at",
                    "updated_at",
                    "managed",
                    "person_id",
                    "generic_serial",
                    "liability",
                    "warehouse_status",
                    "external_id",
                    "full_device_profile",
                    "line_id",
                    "ref_device",
                    "person"
                ]
            }
        }
    }
}

Now you will be able to use a Compose shape and select the dynamic value you want.

[LeeJBS]

Thanks heaps for that @Jcook 

That gets all that I need. Really appreciate the quick responses guys. Massive help