Answered

json file to sharepoint list

(0) Share

Report

Posted on by wlyttle

I'm creating a flow to convert a json file to populate a sharepoint list. I have little knowledage with json files but I'm learning quick. My flow is simple (found a good video on youtube :) ). I start with Get file Content that points to the json file. Next I have Parse Json step where I used sample data from the json file to generate the Schema. Next is an Apply to each with Compose and Create new Item to populate Sharepoint list. The first thing is when I created the Schema the first "type" is an array. The Parse JSON step choaks when I keep the type array and complains that it should be an object. When I get down to the Compose I do not see the options for my inputs only "body" from Parse JSON. I need these fields to populate the Create Item step "Name" IP address: and "UID". How to I extract these fields from the json to populate the Create Item step?

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (1)

Sort by

Suggested answer

11manish 1,941 on at

Like (1)

Report
Copy link

Link copied!
Parse JSON Schema

Use:

{ "type": "array", "items": { "type": "object", "properties": { "Name": { "type": "string" }, "IP": { "type": "string" }, "UID": { "type": "string" } } } }

Was this reply helpful? Yes No
wlyttle 83 on at

Like (0)

Report
Copy link

Link copied!

I tried the shema but it does not work. Thanks for the idea. Whenever I put in the type array it fails.

[

  {

    "message": "Invalid type. Expected Array but got Object.",

    "lineNumber": 0,

    "linePosition": 0,

    "path": "",

    "schemaId": "#",

    "errorType": "type",

    "childErrors": []

  }

]

This is the begining of the json file. These are network objects -

{
"objects" : [ {
"uid" : "xxxxxxxxxxxxxxxxxxxxxx3",
"name" : "Host#1",
"type" : "host",
"domain" : {
"uid" : "xxxxxxxxxxxxxxxxxxxxxx31",
"name" : "SMC User",
"domain-type" : "domain"
},
"ipv4-address" : "10.10.10.99",
"icon" : "Objects/host",
"color" : "black"
}, {
"uid" : "xxxxxxxxxxxxxxxxxxxxxx4",
"name" : "Host#2",
"type" : "host",
"domain" : {
"uid" : "xxxxxxxxxxxxxxxxxxxxxx41",
"name" : "SMC User",
"domain-type" : "domain"
},
"ipv4-address" : "10.10.10.100",
"icon" : "Objects/host",
"color" : "black"
},

Any help with this is much appreciated.

Was this reply helpful? Yes No
Verified answer

Soufyane 69 on at

Like (2)

Report
Copy link

Link copied!
Hi,

The JSON is wrapped in an objects property, it's not a raw array, it's an object containing an array. That's why it fails with type: array.

1st : Try this Schema ;

{ "type": "object", "properties": { "objects": { "type": "array", "items": { "type": "object", "properties": { "uid": { "type": "string" }, "name": { "type": "string" }, "ipv4-address": { "type": "string" } } } } } }

2nd : Fix the Apply to Each

In your Apply to Each, use:

body('Parse_JSON')?['objects']

as the input not the full body.

3rd : Then inside the loop, your dynamic fields will appear:

Name → name

IP → ipv4-address

UID → uid

-----------------------------------------------------------------------------------------------------------------------------------------------------

If this response resolves your issue, please mark it as the Verified Answer so it can help other community members as well.
📩 Need more help? Just mention @Soufyane and I'll be happy to assist.
💛 A Like always motivates me to keep contributing!

Soufyane DARDAZ
LinkedIn : https://www.linkedin.com/in/sdardaz/
Website : https://dardaz.com

Was this reply helpful? Yes No
wlyttle 83 on at

Like (0)

Report
Copy link

Link copied!

Thank you Soufyane - That made it work. Now I'm on to the next step. I need to extract more data from this device. I issued another command that outputs the full details. This changes the json file and the schema. I created the schema again for the parse JSON by inputting a sample of data from the json file. This is the schema i get now. I hope this stuff is easy for you. Again, I appreciate any help with this.

{
"type": "object", Note--If I keep this as array the Parse json does not like it. If I change it to Object the Create Item fails
"items": {
"type": "object",
"properties": {
"uid": {
"type": "string"
},
"name": {
"type": "string"
},
"type": {
"type": "string"
},
"domain": {
"type": "object",
"properties": {
"uid": {
"type": "string"
},
"name": {
"type": "string"
},
"domain-type": {
"type": "string"
}
}
},
"ipv4-address": {
"type": "string"
},
"interfaces": {
"type": "array"
},
"nat-settings": {
"type": "object",
"properties": {
"auto-rule": {
"type": "boolean"
},
"ipv4-address": {
"type": "string"
},
"ipv6-address": {
"type": "string"
},
"hide-behind": {
"type": "string"
},
"install-on": {
"type": "string"
},
"method": {
"type": "string"
}
}
},
"comments": {
"type": "string"
},
"color": {
"type": "string"
},
"icon": {
"type": "string"
},
"tags": {
"type": "array"
},
"meta-info": {
"type": "object",
"properties": {
"lock": {
"type": "string"
},
"validation-state": {
"type": "string"
},
"last-modify-time": {
"type": "object",
"properties": {
"posix": {
"type": "integer"
},
"iso-8601": {
"type": "string"
}
}
},
"last-modifier": {
"type": "string"
},
"creation-time": {
"type": "object",
"properties": {
"posix": {
"type": "integer"
},
"iso-8601": {
"type": "string"
}
}
},
"creator": {
"type": "string"
}
}
},
"read-only": {
"type": "boolean"
},
"available-actions": {
"type": "object",
"properties": {
"edit": {
"type": "string"
},
"delete": {
"type": "string"
},
"clone": {
"type": "string"
}
}
}
},
"required": [
"uid",
"name",
"type",
"domain",
"ipv4-address",
"interfaces",
"nat-settings",
"comments",
"color",
"icon",
"tags",
"meta-info",
"read-only",
"available-actions"
]
}
}

Was this reply helpful? Yes No