Blogs

Parse JSON for Multi-Type Properties

Inkey, February 6, 2024 1589 Views

JSON (JavaScript Object Notation) is a widely used data format for storing and exchanging data. It is easy to read, write, and parse, making it a popular choice for data interchange. However, when working with JSON data, you may encounter scenarios where a column or field can contain multiple types of data. In this blog post, we will explore how to handle such situations by making minor adjustments to your parsing schema in power automate flow or logic apps, meaning we will see how to parse JSON for multi-type properties.

Let’s begin by exploring the types of errors that can arise due to irregularities in data types within a field.
Below is the sample data.

[
  {
    "firstname": "test1",
    "lastname": "User1",
    "Company": "PQR infotech",
    "hobbies": [
      "reading",
      "boxing",
      "gaming"
    ]
  },
  {
    "firstname": "Test2",
    "lastname": "User2",
    "Company": "ABC infotech",
    "hobbies": null
  },
  {
    "firstname": "Test3",
    "lastname": "User3",
    "Company": "PQR infotech",
    "hobbies": [
      "reading",
      "boxing",
      "gaming"
    ]
  }
]

In the example provided above, it’s evident that all three items showcase a distinct data type for the “hobbies” property.

When we attempt to parse these records one by one using a loop and define the “Hobbies” property in the schema as an “Array” type, we need to be cautious. Here’s the catch: if we input an item that doesn’t have “Hobbies” structured as an array, the system will inevitably raise an error.

In the example below, the system is looking for “Hobbies” to be in the form of an array, but item actually contains an object within the “Hobbies” property, and that’s causing an error. We’ll now discuss how to deal with this situation.

So, how do we resolve this challenge? To tackle this issue, we can introduce minor adjustments to the schema, allowing for properties (or columns) to accommodate multiple data types. For instance, let’s say we have a “Hobbies” property that can either be an array or an object. In such a scenario, our schema should be designed as follows:

Schema:

  {
    "type": "object",
    "properties": {
        "firstname": {
            "type": "string"
        },
        "lastname": {
            "type": "string"
        },
        "Company": {
            "type": "string"
        },
        "hobbies": {
            "type": ["array", "object", “null”],
            "items": {
                "type": "string"
            }
        }
    }
}

This simple change in schema configuration empowers us to gracefully handle data with varying data types for the same property. It’s a small adjustment, but it can make a world of difference in ensuring smooth and error-free data parsing.

To fix this, we can keep it simple. In our schema, we describe a property as a mix of possible types, making it open to any valid type. Once we make this change, any type we mention in the schema for that property becomes acceptable. This uncomplicated approach provides flexibility and ensures that we can handle various data types without errors.

In below example you can see it can handle more than one data type.

Handling JSON data with dynamic data types within Power Automate flows/Logic apps is a common challenge, especially when automating data processes. By implementing the strategies mentioned above and making minor adjustments to your flow, you can parse JSON data effectively, even when dealing with dynamic data types. Power Automate/Logic app’s dynamic data handling capabilities, flexibility, and adaptability are key to overcoming these challenges successfully.

And so this is how we can parse JSON for multi-type properties.

---

Know the number of records in any Dataverse entity or table.
https://www.inkeysolutions.com/entity-record-counter

ATM Inspection PowerApp to ease ATM inspection and report generation process.
https://www.inkeysolutions.com/microsoft-power-platform/power-app/atm-inspection

Insert data into Many-to-Many relationship in Dynamics CRM very easily & quickly, using the Drag and drop listbox.
http://www.inkeysolutions.com/what-we-do/dynamicscrmaddons/drag-and-drop-listbox

Comply your Lead, Contact, and User entities of D365 CRM with GDPR compliance using the GDPR add-on.
https://www.inkeysolutions.com/microsoft-dynamics-365/dynamicscrmaddons/gdpr

Create a personal / system view in Dynamics CRM with all the fields on the form/s which you select for a particular entity using the View Creator.
http://www.inkeysolutions.com/what-we-do/dynamicscrmaddons/view-creator