Handling Null / Absent Geometry Arrays within JSON

[Anonymous]

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: 

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. 

[Brad_Groux]

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!

[Anonymous]

@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"
        ]
    }
}

[Brad_Groux]

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!

[Anonymous]

@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? 

[Anonymous]

@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.  

[Anonymous]

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

Would someone be able to help me?