Working with Json

(0) Share

Report

Posted on by 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.

I have the same question (0)

All responses (4)

Answers (2)

Verified answer

ChristianAbata 8,949 Most Valuable Professional on at

Like (1)

Report
Re: Working with Json

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

ref_device

Was this reply helpful? Yes No
LeeJBS 59 on at

Like (1)

Report

Re: Working with Json

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

Was this reply helpful? Yes No
Verified answer

Jcook 7,779 Most Valuable Professional on at

Like (0)

Report
Re: Working with Json

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.

Was this reply helpful? Yes No
LeeJBS 59 on at

Like (0)

Report

Re: Working with Json

Thanks heaps for that @Jcook
That gets all that I need. Really appreciate the quick responses guys. Massive help

Was this reply helpful? Yes No