PowerPlatform and NSC Love JSON

Have you ever wanted to do more with your favourite PowerPlatform and hit a wall? Well today we are going to shed some light on one of those walls… JSON

Now. Some will tell you that you require quite a bit of knowledge to understand and use JSON.

I am here to tell you today. They are dumb. You be you. Sit back, relax and read

NextStep Creations Basics of JSON and the PowerPlatform

So we start by ignoring the PowerPlatform as we need to layout some basic concepts first.
In programming there are these things called type definitions. They do exactly as they say. Define types.

Some of the common types you will see as you start to Delve a bit deeper are as follows:

  • Integers (Also known as numbers or values) <– this little guy is a whole number so 123 is an integer and so is -123
  • Strings (Also known as text or str) <– this little guy is normally surrounded by “HI! I’m a string! “, it can also be surrounded by ‘yes these’
  • Booleans (Also known true/false, yes/no, on/off) <–This is as it states only two values. An ‘on’, ‘yes’, ‘1’ or ‘true’ and an ‘off’, ‘no’, ‘0’ or ‘false’
  • Objects (Also known as a record) <– Think of this guy as a row in a table. It will contain data for that entire row.
  • Arrays (Also known as a table) <– Think of this guy as the entire collection of items. You could say an Array is a collection of objects
  • NULL (Also known as blank) <– This one sometimes trips people up. As people assume that 0 or “Zero” is Null. However this is incorrect. Think of it this way:
    Zero: You have a toilet roll but there is no toilet paper left. You still know its a toilet roll though!
    Null: You have no toilet paper and no toilet roll.
  • Undefined <– this guy…. this bloody guy…. will ruin your day if you forget about him. Here is a nice image to assist me in defining what undefined is 🙂

JSON types - 0 null and undefined
Okay so now we have those little guys out of the way. Lets get into the SCARY code stuff.
Here is an example describing a person… We are going to break it down:

{
  "firstName": "John",
  "lastName": "Smith",
  "isAlive": true,
  "age": 27,
  "address": {
    "streetAddress": "21 2nd Street",
    "city": "New York",
    "state": "NY",
    "postalCode": "10021-3100"
  },
  "phoneNumbers": [
    {
      "type": "home",
      "number": "212 555-1234"
    },
    {
      "type": "office",
      "number": "646 555-4567"
    }
  ],
  "children": [],
  "spouse": null
}

Okay so every JSON file will contain { } these guys. They generally start and finish objects.
As you can see above prior to First Name there is an open tag { then finally after spouse there is a close tag }

Starting from the top! “firstName”: is the first name column! now pay close attention to the quotes and the colon. For a column name quotes ‘ ” ‘MUST start and close the name followed by a colon ‘ : ‘.

Now we have {“firstName”: “John”, 
Here John is defined as the value for the firstName column. Please also note its surrounded in quotes as its a string and the line is closed with a comma ‘ , ‘. Something to note here, these commas have to be placed on every line of an object except the last! Remember this!

Moving down. “lastName”: is another column definition with the value “Smith”, <– again notice the comma.

“isAlive”: true,  <– this one is slightly different as the words true and false without quotes surrounding them are defined as boolean values as described above.

“age”: 27, <– this value doesn’t require quotes either due to numbers being defined as integers if quotes are not surrounding the digits. So if you need a number type… Lose the quotes.

now “address”: { <– ooh! This one has another open tag. Meaning we have our first nested object. The contents of this one are clearly defined the same way. The biggest thing to note here is that the object MUST be closed so at the end you will notice }, meaning im finished defining this and move onto the next column

“phoneNumbers”: [ <– yay! learning! This one is the start of an array. Now remembering as above that an array is a collection of objects. Meaning you will generally see something like {object1}, {object2}, {object3} now these objects will be surrounded by [] as this dictates the array nature. Again ensure that you close this Array by using ], saying hey! I’m done here move onto the next column.

Okay now we have broken down how to read JSON. Lets move onto how our beautiful PowerPlatform reads JSON… SCHEMAS! 
We have performed some magic hiddin inside a JSON to JSON Schema converter to create the below:

{
"$schema": "http://json-schema.org/draft-04/schema#",
"type": "object",
"properties": {
"firstName": {
"type": "string"
},
"lastName": {
"type": "string"
},
"isAlive": {
"type": "boolean"
},
"age": {
"type": "integer"
},
"address": {
"type": "object",
"properties": {
"streetAddress": {
"type": "string"
},
"city": {
"type": "string"
},
"state": {
"type": "string"
},
"postalCode": {
"type": "string"
}
},
"required": [
"streetAddress",
"city",
"state",
"postalCode"
]
},
"phoneNumbers": {
"type": "array",
"items": [
{
"type": "object",
"properties": {
"type": {
"type": "string"
},
"number": {
"type": "string"
}
},
"required": [
"type",
"number"
]
},
{
"type": "object",
"properties": {
"type": {
"type": "string"
},
"number": {
"type": "string"
}
},
"required": [
"type",
"number"
]
}
]
},
"children": {
"type": "array",
"items": {}
},
"spouse": {
"type": "null"
}
},
"required": [
"firstName",
"lastName",
"isAlive",
"age",
"address",
"phoneNumbers",
"children",
"spouse"
]
}

Now please don’t get confused here. All you are seeing is the definition of what we have explained above.

These beautiful schemas are VERY important as they allow us to add predictability to our low code solution. We know what will be a string, Boolean, array or object.

In the above you will see the definition of each column and then its type defined just after. Now the reason I wanted to expand into Schemas in this post is these are where the headaches come from. 

In a perfect world the data we enter in the system will be perfect… but lets be real… users are dumb…

So if your isAlive field requires a yes or no and some how the user enters “hello world” your system will catch fire.

Now you may want it to break if this happens. However. Sometimes you may need you system to keep running. What you could do is remove the type definition from your defined column so instead of:
“isAlive”: {
“type”: “boolean”
}
it would be:
“isAlive”: {}

Something to note however. If you are passing data back to PowerApps… PowerApps hates undefined types. So don’t do this.. XD

Also something that might cause you headaches is this bit:
“required”: [
“firstName”,
“lastName”,
“isAlive”,
“age”,
“address”,
“phoneNumbers”,
“children”,
“spouse”
]
This states that all of these fields are REQUIRED for your JSON to parse the data correctly. Now if for some reason age doesn’t always have data. Your environment will cry tears of blood. Not to fear! You can remove the required column names with the simple backspace key along with your hours upon hours of time spent debugging this dumb issue.

So something to note is you can also remove all of them so your required portion of the schema could look like this:
“required”: [] <– just be careful as you MAY need to ensure data is in some fields.

Okay.. This is the walkthrough, breakdown, explanation, whatever you want to call it of something that took me much longer than I would like to admit to FULLY grasp. 

I have not explained everything. Nor will I. However I hope this little post of mine can help you in expanding your apps, workflows or automation! If you have any questions or ideas, don’t hesitate to reach out to myself and the team on Twitter: NextStep Aus

Automate on people.

Ben out!

Check out some of our other posts here:

Leave a Reply

Your email address will not be published. Required fields are marked *