cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jyeamans
Level: Powered On

Handling Null / Absent Geometry Arrays within JSON

Hi All, 

 

Looking for help on parsing JSON where it looks at inspections data on where an inspection is completed. Not all inspection have the 'geometry' point so I'm looking for a way to parse this out when there and ignore when it's not within the items Array. 

 

Here's what I have so far: 

Screen Shot 2019-05-28 at 12.04.26 PM.png

 

Input 1 (with geometry): 

[
  {
    "parent_id": "f3245d39-ea77-11e1-aff1-0800200c9a66",
    "item_id": "f3245d44-ea77-11e1-aff1-0800200c9a66",
    "label": "Location: City, State",
    "type": "address",
    "options": {
      "weighting": 1,
      "is_mandatory": true
    },
    "responses": {
      "location_text": "(41.68690374124952, -107.9782995210103)",
      "location": {
        "name": "",
        "country": "",
        "geometry": {
          "type": "Point",
          "coordinates": [
            -107.97829952101034,
            41.68690374124952
          ]
        },
        "locality": "",
        "postal_code": "",
        "sub_locality": "",
        "thoroughfare": "",
        "iso_country_code": "",
        "sub_thoroughfare": "",
        "formatted_address": [],
        "administrative_area": "",
        "sub_administrative_area": ""
      }
    }
  }
]

Input 2 (without Geometry):

[
  {
    "parent_id": "f3245d39-ea77-11e1-aff1-0800200c9a66",
    "item_id": "f3245d44-ea77-11e1-aff1-0800200c9a66",
    "label": "Location: City, State",
    "type": "address",
    "options": {
      "weighting": 1,
      "is_mandatory": true
    },
    "responses": {
      "location_text": "Test Site"
    }
  }
]

I tried to place the following into the output of excel, however if this Array doesn't exist inside input, this even will fail.  

body('Location_Item')?['responses']['location']['geometry']['coordinates'][0]

Any help with adding an expression to output Long the lat straight to the excel would be greatly appreciated. 

6 REPLIES 6
Highlighted
Super User
Super User

Re: Handling Null / Absent Geometry Arrays within JSON

See Jon "Flow Ninja" Liu's blog post, A Thesis on the Parse JSON action in Microsoft Flow, specifically the Problem 4 - null value properties section

In general, Parse JSON generates the schema based on the first object it sees from the sample. This is not always correct - since sometimes the properties may be null in the later objects. For example in the football games data - earlier records has scores as integers, but later records haven't been played yet so scores are null.

This error usually fails the Flow. Another mutation of this problem is when the number is a decimal, but the schema thinks it's integer.

The easiest way to fix nullable-values is to delete the type information for this property. 

Using no type information is better than changing to something like type: "object" because the Dynamic Content Panel will always show these properties.


If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

jyeamans
Level: Powered On

Re: Handling Null / Absent Geometry Arrays within JSON

@Brad_Groux  Thank you for sending this article across. I'm not 100% sure where to place the varible John Liu is talking about. 

 

Do I place it into the following place under geometry under the "type": "integer"?

 

"geometry": {
    "type": "object",
    "properties": {
        "type": {
            "type": "string"
            },
            "coordinates": {
                "type": "array",
                "items": {
                    "type": "number"
                }
          }
     }
}

 

 

 

Old Schema: 

 

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "parent_id": {
                "type": "string"
            },
            "item_id": {
                "type": "string"
            },
            "label": {
                "type": "string"
            },
            "type": {
                "type": "string"
            },
            "options": {
                "type": "object",
                "properties": {
                    "weighting": {
                        "type": "integer"
                    },
                    "is_mandatory": {
                        "type": "boolean"
                    }
                }
            },
            "responses": {
                "type": [
                    "object",
                    "null"
                ],
                "properties": {
                    "location_text": {
                        "type": [
                            "string",
                            "null"
                        ]
                    },
                    "location": {
                        "type": [
                            "object",
                            "null"
                        ],
                        "properties": {
                            "name": {
                                "type": "string"
                            },
                            "country": {
                                "type": "string"
                            },
                            "geometry": {
                                "type": "object",
                                "properties": {
                                    "type": {
                                        "type": "string"
                                    },
                                    "coordinates": {
                                        "type": "array",
                                        "items": {
                                            "type": "number"
                                        }
                                    }
                                }
                            },
                            "locality": {
                                "type": "string"
                            },
                            "postal_code": {
                                "type": "string"
                            },
                            "sub_locality": {
                                "type": "string"
                            },
                            "thoroughfare": {
                                "type": "string"
                            },
                            "iso_country_code": {
                                "type": "string"
                            },
                            "sub_thoroughfare": {
                                "type": "string"
                            },
                            "formatted_address": {
                                "type": "array"
                            },
                            "administrative_area": {
                                "type": "string"
                            },
                            "sub_administrative_area": {
                                "type": "string"
                            }
                        }
                    }
                }
            }
        },
        "required": [
            "item_id"
        ]
    }
}

 

 

Super User
Super User

Re: Handling Null / Absent Geometry Arrays within JSON

You have multiple options for how best to do this - it all depends on if you delete or filter all entries with null values (as his blog post indicates), or if you create a variable for all parsed values. I'd likely opt for the latter, but it all depends on what is best for your data and what you are looking to do with your data output.

If you create a variable for all values, including the Geometry data, then You could Add a condition to a Flow, comparing that Geo data to null in an apply to each loop. If it is null, then skip it.

You could even parse the JSON to a SharePoint List with matching columns to get further functionality, the sky is basically the limit for what you can do once the data is parsed. 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

jyeamans
Level: Powered On

Re: Handling Null / Absent Geometry Arrays within JSON

@Brad_Groux  Maybe I'm not building the steps within my Flow in the most effective way. 

 

  1. Reoccurance (Run once an hour)
  2. Past Time (last 1 hours inspection data)
  3. Search modified inspection (iAuditor, up to 1000 inspection)
  4. Apply to each inspection ID
  5. Get a specific inspection (pulls meta data and body of inspection JSON)
  6. 'Data Operations - Filter Array' for Title page items (3 of these for 3 different items)
  7. 'Data Operations - Compose' to parse out response label for each Filter Array
  8. Retrieve an audit web report (iAuditor)
  9. Apply to each (date/time response, automatically added when I pulled one of the Data Operation)
  10. Add row to an Excel Table (mapping specific fields from inspection)

Any thoughts to handle errors and null better? Maybe I could get 15-30 minutes of your time where I can walk you through it? 

jyeamans
Level: Powered On

Re: Handling Null / Absent Geometry Arrays within JSON

@Brad_Groux  Thank you for providing me with so many links. Let see if I can explain my ulitmate goal, as this was a means to get something going. 

 

The end goal is to create a flow that would look at each iAuditor inspections presented to the flow via the 'search modified inspection', then I would look at the header and items array then pull out each item object where there are specific items in every item object, but there are slight variation with in the scoring, options, responses, media, and children. 

 

Would you say I need to create a variable for each string inside the obnject?  Using the below object, would I need to create 51 different variables within the scoring, options, responses, media, and children onjects? 

 

{
      "parent_id": "35bc4a6b-0b92-4062-b179-24242b4593aa",
      "item_id": "1662dd2e-ae94-494a-8bdc-cbc693c54a6f",
      "label": "Temp",
      "type": "temperature",
      "evaluation": true,
      "scoring": {
        "score": 2,
        "max_score": 10,
        "score_percentage": 20,
        "combined_score": 8,
        "combined_max_score": 16,
        "combined_score_percentage": 50
      },
      "options": {
        "weighting": 1,
        "increment": 1,
        "max": 10,
        "min": 1,
        "is_mandatory": false,
        "enable_signature_timestamp": true,
        "multiple_selection": true,
        "enable_date": true,
        "enable_time": true,
        "condition": "3f206180-e4f6-11e1-aff1-0800200c9a66",
        "response_set": "7bb1cb10-7020-11e2-bcfd-0800200c9a66"
      },
      "responses": {
        "text": "Smugglers",
        "value": 2,
        "datetime": "2019-05-28T21:44:56.000Z",
        "timestamp": "2019-05-28T21:45:13.647Z",
        "selected": [
          {
            "id": "9bf0fa6e-36bc-40e6-8905-a389968a1e3a",
            "label": "Compliant",
            "colour": "128,128,128",
            "score": 1,
            "short_label": "",
            "type": "text",
            "enable_score": true
          }
        ],
        "location_text": "8111 W 87th St, Overland Park, KS 66212, USA",
        "location": {
          "name": "",
          "country": "United States",
          "geometry": {
            "type": "Point",
            "coordinates": [
              -94.67992989255015,
              38.97068809986928
            ]
          },
          "locality": "Overland Park",
          "postal_code": "66212",
          "sub_locality": "Glenwood Estates",
          "thoroughfare": "West 87th Street",
          "iso_country_code": "US",
          "sub_thoroughfare": "8111",
          "administrative_area": "Kansas"
        },
          "image": {
          "date_created": "2019-05-28T21:45:13.348Z",
          "file_ext": "png",
          "label": "Active Users Users and Seats 2019-05-28T1423.png",
          "media_id": "df733060-8191-11e9-a5d4-8dd2f90d27e9",
          "href": "https://api.safetyculture.io/audits/audit_96d0e0a2cd424ec4bd0ac24050b04ab5/media/df733060-8191-11e9-a5d4-8dd2f90d27e9"},
      "media": [
            {
              "date_created": "2019-05-28T21:45:08.331Z",
              "file_ext": "jpg",
              "label": "howthecustomerexplainedit.jpg",
              "media_id": "dc6e06b0-8191-11e9-a5d4-8dd2f90d27e9",
              "href": "https://api.safetyculture.io/audits/audit_96d0e0a2cd424ec4bd0ac24050b04ab5/media/dc6e06b0-8191-11e9-a5d4-8dd2f90d27e9"
            }
          ]
      },
      "children": [
        "f58682b6-44c6-4559-85b8-faf91d13c816"
      ]
    }

 

I can add a full inspection JSON if needed.  

jyeamans
Level: Powered On

Re: Handling Null / Absent Geometry Arrays within JSON

Bumping this thread, as I have been unsuccessful in getting this flow created and working. 

 

Would someone be able to help me? 

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Kudoed Authors
Users Online
Currently online: 155 members 5,107 guests
Please welcome our newest community members: