cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LeeJBS
Advocate I
Advocate I

Working with Json

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.

2 ACCEPTED SOLUTIONS

Accepted Solutions
ChristianAbata
Super User II
Super User II

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

ref_device


Did I answer your question? Please consider to Mark
my post as a solution! to guide others :winking_face:

Proud to be a Flownaut!


If you want you can follow me at www.christianabata.com Quieres contenido en español? Síguenos en Power Automate LA

View solution in original post

Jcook
Super User III
Super User III

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.

 

Parse JSON.png

 


Did I answer your question? Mark my post as a solution!

If you like my post please hit the Thumbs Up


Proud to be a Flownaut!


Check out my blog for Power Automate tips,
tricks, and guides
FlowAltDelete





View solution in original post

4 REPLIES 4
ChristianAbata
Super User II
Super User II

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

ref_device


Did I answer your question? Please consider to Mark
my post as a solution! to guide others :winking_face:

Proud to be a Flownaut!


If you want you can follow me at www.christianabata.com Quieres contenido en español? Síguenos en Power Automate LA

View solution in original post

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
Super User III
Super User III

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.

 

Parse JSON.png

 


Did I answer your question? Mark my post as a solution!

If you like my post please hit the Thumbs Up


Proud to be a Flownaut!


Check out my blog for Power Automate tips,
tricks, and guides
FlowAltDelete





View solution in original post

Thanks heaps for that @Jcook 

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

Helpful resources

Announcements
MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

Top Solution Authors
Top Kudoed Authors
Users online (17,641)