cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nick9one1
Helper I
Helper I

Parse nested JSON

I have parsed simple JSON in the past, but I'm struggling to extract values from this complex nested JSON from a GET to SharePoint Search. 

I need URL, UniquieID, Write, Title. 

thank you 🙂

 

{
  "d": {
    "query": {
      "__metadata": {
        "type": "Microsoft.Office.Server.Search.REST.SearchResult"
      },
      "ElapsedTime": 250,
      "PrimaryQueryResult": {
        "__metadata": {
          "type": "Microsoft.Office.Server.Search.REST.QueryResult"
        },
        "CustomResults": {
          "__metadata": {
            "type": "Collection(Microsoft.Office.Server.Search.REST.CustomResult)"
          },
          "results": []
        },
        "QueryId": "908672f0-07fb-4ad2-b0ab-37db1895eca4",
        "QueryRuleId": "00000000-0000-0000-0000-000000000000",
        "RefinementResults": null,
        "RelevantResults": {
          "__metadata": {
            "type": "Microsoft.Office.Server.Search.REST.RelevantResults"
          },
          "GroupTemplateId": null,
          "ItemTemplateId": null,
          "Properties": {
            "__metadata": {
              "type": "Collection(SP.KeyValue)"
            },
            "results": [
              {
                "Key": "GenerationId",
                "Value": "9223372036854775806",
                "ValueType": "Edm.Int64"
              },
              {
                "Key": "indexSystem",
                "Value": "",
                "ValueType": "Edm.String"
              },
              {
                "Key": "ExecutionTimeMs",
                "Value": "47",
                "ValueType": "Edm.Int32"
              },
              {
                "Key": "QueryModification",
                "Value": "a -ContentClass=urn:content-class:SPSPeople",
                "ValueType": "Edm.String"
              },
              {
                "Key": "RenderTemplateId",
                "Value": "~sitecollection/_catalogs/masterpage/Display Templates/Search/Group_Default.js",
                "ValueType": "Edm.String"
              },
              {
                "Key": "StartRecord",
                "Value": "0",
                "ValueType": "Edm.Int32"
              },
              {
                "Key": "IsLastBlockInSubstrate",
                "Value": "true",
                "ValueType": "Edm.Boolean"
              },
              {
                "Key": "IsFirstBlockInSubstrate",
                "Value": "false",
                "ValueType": "Edm.Boolean"
              },
              {
                "Key": "IsFirstPinnedResultBlock",
                "Value": "false",
                "ValueType": "Edm.Boolean"
              },
              {
                "Key": "IsLastPinnedResultBlock",
                "Value": "false",
                "ValueType": "Edm.Boolean"
              },
              {
                "Key": "IsFirstRankedResultBlock",
                "Value": "true",
                "ValueType": "Edm.Boolean"
              },
              {
                "Key": "IsLastRankedResultBlock",
                "Value": "true",
                "ValueType": "Edm.Boolean"
              },
              {
                "Key": "MixedTableOrder",
                "Value": "0",
                "ValueType": "Edm.Int32"
              },
              {
                "Key": "GeoLocationSource",
                "Value": "EUR",
                "ValueType": "Edm.String"
              }
            ]
          },
          "ResultTitle": null,
          "ResultTitleUrl": null,
          "RowCount": 1,
          "Table": {
            "__metadata": {
              "type": "SP.SimpleDataTable"
            },
            "Rows": {
              "results": [
                {
                  "__metadata": {
                    "type": "SP.SimpleDataRow"
                  },
                  "Cells": {
                    "results": [
                      {
                        "__metadata": {
                          "type": "SP.KeyValue"
                        },
                        "Key": "Rank",
                        "Value": "0",
                        "ValueType": "Edm.Double"
                      },
                      {
                        "__metadata": {
                          "type": "SP.KeyValue"
                        },
                        "Key": "DocId",
                        "Value": "17662559196589",
                        "ValueType": "Edm.Int64"
                      },
                      {
                        "__metadata": {
                          "type": "SP.KeyValue"
                        },
                        "Key": "Path",
                        "Value": "https://url here.aspx",
                        "ValueType": "Edm.String"
                      },
                      {
                        "__metadata": {
                          "type": "SP.KeyValue"
                        },
                        "Key": "Url",
                        "Value": "https://url here.aspx",
                        "ValueType": "Edm.String"
                      },
                      {
                        "__metadata": {
                          "type": "SP.KeyValue"
                        },
                        "Key": "OriginalPath",
                        "Value": "https://url here.aspx",
                        "ValueType": "Edm.String"
                      },
                      {
                        "__metadata": {
                          "type": "SP.KeyValue"
                        },
                        "Key": "Title",
                        "Value": "title here",
                        "ValueType": "Edm.String"
                      },
                      {
                        "__metadata": {
                          "type": "SP.KeyValue"
                        },
                        "Key": "Author",
                        "Value": "name",
                        "ValueType": "Edm.String"
                      },
                      {
                        "__metadata": {
                          "type": "SP.KeyValue"
                        },
                        "Key": "Write",
                        "Value": "2020-07-17T16:53:16.0000000Z",
                        "ValueType": "Edm.DateTime"
                      },
                      {
                        "__metadata": {
                          "type": "SP.KeyValue"
                        },
                        "Key": "Language",
                        "Value": "en",
                        "ValueType": "Edm.String"
                      },
                      {
                        "__metadata": {
                          "type": "SP.KeyValue"
                        },
                        "Key": "SiteId",
                        "Value": "8b916ccb-d28a-4cb8-8d3a-366b2884a2f9",
                        "ValueType": "Edm.String"
                      },
                      {
                        "__metadata": {
                          "type": "SP.KeyValue"
                        },
                        "Key": "UniqueId",
                        "Value": "{1A6A2ECF-38AF-429C-B48A-0FE4695B02BE}",
                        "ValueType": "Edm.String"
                      },
                      {
                        "__metadata": {
                          "type": "SP.KeyValue"
                        },
                        "Key": "WebId",
                        "Value": "5cc43304-8632-44b2-a7f7-23d2c4e45409",
                        "ValueType": "Edm.String"
                      },
                      {
                        "__metadata": {
                          "type": "SP.KeyValue"
                        },
                        "Key": "DocumentSignature",
                        "Value": "-3951990534639608000;-6214053146681014806;-6067154480955288799;5088562660161505817;3060008687003457472;-6294927851860747587",
                        "ValueType": "Edm.String"
                      },
                      {
                        "__metadata": {
                          "type": "SP.KeyValue"
                        },
                        "Key": "contentclass",
                        "Value": "STS_ListItem_WebPageLibrary",
                        "ValueType": "Edm.String"
                      },
                      {
                        "__metadata": {
                          "type": "SP.KeyValue"
                        },
                        "Key": "ResultTypeIdList",
                        "Value": null,
                        "ValueType": "Null"
                      },
                      {
                        "__metadata": {
                          "type": "SP.KeyValue"
                        },
                        "Key": "ResultTypeId",
                        "Value": null,
                        "ValueType": "Null"
                      },
                      {
                        "__metadata": {
                          "type": "SP.KeyValue"
                        },
                        "Key": "RenderTemplateId",
                        "Value": "~sitecollection/_catalogs/masterpage/Display Templates/Search/Item_Default.js",
                        "ValueType": "Edm.String"
                      },
                      {
                        "__metadata": {
                          "type": "SP.KeyValue"
                        },
                        "Key": "piSearchResultId",
                        "Value": "ARIAǂed59c221-761b-49cc-9157-45b9160c6ba5ǂ5631b80b-0307-4755-9ddc-d5e39f346cc0ǂed59c221-761b-49cc-9157-45b9160c6ba5.1000.1ǂ",
                        "ValueType": "Edm.String"
                      },
                      {
                        "__metadata": {
                          "type": "SP.KeyValue"
                        },
                        "Key": "GeoLocationSource",
                        "Value": "EUR",
                        "ValueType": "Edm.String"
                      },
                      {
                        "__metadata": {
                          "type": "SP.KeyValue"
                        },
                        "Key": "PartitionId",
                        "Value": "f3a2e4da-8c30-4f06-bb03-a0fc334c4f9f",
                        "ValueType": "Edm.Guid"
                      },
                      {
                        "__metadata": {
                          "type": "SP.KeyValue"
                        },
                        "Key": "UrlZone",
                        "Value": "0",
                        "ValueType": "Edm.Int32"
                      },
                      {
                        "__metadata": {
                          "type": "SP.KeyValue"
                        },
                        "Key": "Culture",
                        "Value": "en-GB",
                        "ValueType": "Edm.String"
                      },
                      {
                        "__metadata": {
                          "type": "SP.KeyValue"
                        },
                        "Key": "CollapsingStatus",
                        "Value": "0",
                        "ValueType": "Edm.Int32"
                      }
                    ]
                  }
                }
              ]
            }
          },
          "TotalRows": 3018,
          "TotalRowsIncludingDuplicates": 3018
        },
        "SpecialTermResults": null
      },
      "Properties": {
        "__metadata": {
          "type": "Collection(SP.KeyValue)"
        },
        "results": [
          {
            "Key": "WordBreakerLanguage",
            "Value": "en",
            "ValueType": "Edm.String"
          },
          {
            "Key": "RowLimit",
            "Value": "1",
            "ValueType": "Edm.Int32"
          },
          {
            "Key": "SourceId",
            "Value": "8413cd39-2156-4e00-b54d-11efd9abdb89",
            "ValueType": "Edm.Guid"
          },
          {
            "Key": "CorrelationId",
            "Value": "7c36679f-5068-b000-2668-2e911244e05d",
            "ValueType": "Edm.Guid"
          },
          {
            "Key": "WasGroupRestricted",
            "Value": "false",
            "ValueType": "Edm.Boolean"
          },
          {
            "Key": "IsPartialUpnDocIdMapping",
            "Value": "false",
            "ValueType": "Edm.Boolean"
          },
          {
            "Key": "EnableInterleaving",
            "Value": "true",
            "ValueType": "Edm.Boolean"
          },
          {
            "Key": "IsMissingUnifiedGroups",
            "Value": "false",
            "ValueType": "Edm.Boolean"
          },
          {
            "Key": "Constellation",
            "Value": "iC7D56",
            "ValueType": "Edm.String"
          },
          {
            "Key": "MultiGeoSearchStatus",
            "Value": "Full",
            "ValueType": "Edm.String"
          },
          {
            "Key": "HasParseException",
            "Value": "false",
            "ValueType": "Edm.Boolean"
          },
          {
            "Key": "IsPartial",
            "Value": "false",
            "ValueType": "Edm.Boolean"
          },
          {
            "Key": "InternalRequestId",
            "Value": "ed59c221-761b-49cc-9157-45b9160c6ba5",
            "ValueType": "Edm.String"
          },
          {
            "Key": "SerializedQuery",
            "Value": "<Query Culture=\"en-GB\" EnableStemming=\"True\" EnablePhonetic=\"False\" EnableNicknames=\"False\" IgnoreAllNoiseQuery=\"True\" SummaryLength=\"180\" MaxSnippetLength=\"180\" DesiredSnippetLength=\"90\" KeywordInclusion=\"0\" QueryText=\"a\" QueryTemplate=\"\" TrimDuplicates=\"True\" Site=\"8b916ccb-d28a-4cb8-8d3a-366b2884a2f9\" Web=\"d15af069-d2aa-493c-a81e-74c3d00c9f26\" KeywordType=\"True\" HiddenConstraints=\"\" />",
            "ValueType": "Edm.String"
          }
        ]
      },
      "SecondaryQueryResults": {
        "__metadata": {
          "type": "Collection(Microsoft.Office.Server.Search.REST.QueryResult)"
        },
        "results": []
      },
      "SpellingSuggestion": "",
      "TriggeredRules": {
        "__metadata": {
          "type": "Collection(Edm.Guid)"
        },
        "results": []
      }
    }
  }
}

 

7 REPLIES 7
nick9one1
Helper I
Helper I

If I use the 'generate from sample' tool in the parse json action, I get the following. 

but when It runs there is an error 

[
  {
    "message": "Invalid type. Expected String but got Null.",
    "lineNumber": 0,
    "linePosition": 0,
    "path": "d.query.PrimaryQueryResult.RelevantResults.Table.Rows.results[0].Cells.results[14].Value",
    "schemaId": "#/properties/d/properties/query/properties/PrimaryQueryResult/properties/RelevantResults/properties/Table/properties/Rows/properties/results/items/properties/Cells/properties/results/items/properties/Value",
    "errorType": "type",
    "childErrors": []
  },
  {
    "message": "Invalid type. Expected String but got Null.",
    "lineNumber": 0,
    "linePosition": 0,
    "path": "d.query.PrimaryQueryResult.RelevantResults.Table.Rows.results[0].Cells.results[15].Value",
    "schemaId": "#/properties/d/properties/query/properties/PrimaryQueryResult/properties/RelevantResults/properties/Table/properties/Rows/properties/results/items/properties/Cells/properties/results/items/properties/Value",
    "errorType": "type",
    "childErrors": []
  }
]

 

 

{
    "type": "object",
    "properties": {
        "d": {
            "type": "object",
            "properties": {
                "query": {
                    "type": "object",
                    "properties": {
                        "__metadata": {
                            "type": "object",
                            "properties": {
                                "type": {
                                    "type": "string"
                                }
                            }
                        },
                        "ElapsedTime": {
                            "type": "integer"
                        },
                        "PrimaryQueryResult": {
                            "type": "object",
                            "properties": {
                                "__metadata": {
                                    "type": "object",
                                    "properties": {
                                        "type": {
                                            "type": "string"
                                        }
                                    }
                                },
                                "CustomResults": {
                                    "type": "object",
                                    "properties": {
                                        "__metadata": {
                                            "type": "object",
                                            "properties": {
                                                "type": {
                                                    "type": "string"
                                                }
                                            }
                                        },
                                        "results": {
                                            "type": "array"
                                        }
                                    }
                                },
                                "QueryId": {
                                    "type": "string"
                                },
                                "QueryRuleId": {
                                    "type": "string"
                                },
                                "RefinementResults": {},
                                "RelevantResults": {
                                    "type": "object",
                                    "properties": {
                                        "__metadata": {
                                            "type": "object",
                                            "properties": {
                                                "type": {
                                                    "type": "string"
                                                }
                                            }
                                        },
                                        "GroupTemplateId": {},
                                        "ItemTemplateId": {},
                                        "Properties": {
                                            "type": "object",
                                            "properties": {
                                                "__metadata": {
                                                    "type": "object",
                                                    "properties": {
                                                        "type": {
                                                            "type": "string"
                                                        }
                                                    }
                                                },
                                                "results": {
                                                    "type": "array",
                                                    "items": {
                                                        "type": "object",
                                                        "properties": {
                                                            "Key": {
                                                                "type": "string"
                                                            },
                                                            "Value": {
                                                                "type": "string"
                                                            },
                                                            "ValueType": {
                                                                "type": "string"
                                                            }
                                                        },
                                                        "required": [
                                                            "Key",
                                                            "Value",
                                                            "ValueType"
                                                        ]
                                                    }
                                                }
                                            }
                                        },
                                        "ResultTitle": {},
                                        "ResultTitleUrl": {},
                                        "RowCount": {
                                            "type": "integer"
                                        },
                                        "Table": {
                                            "type": "object",
                                            "properties": {
                                                "__metadata": {
                                                    "type": "object",
                                                    "properties": {
                                                        "type": {
                                                            "type": "string"
                                                        }
                                                    }
                                                },
                                                "Rows": {
                                                    "type": "object",
                                                    "properties": {
                                                        "results": {
                                                            "type": "array",
                                                            "items": {
                                                                "type": "object",
                                                                "properties": {
                                                                    "__metadata": {
                                                                        "type": "object",
                                                                        "properties": {
                                                                            "type": {
                                                                                "type": "string"
                                                                            }
                                                                        }
                                                                    },
                                                                    "Cells": {
                                                                        "type": "object",
                                                                        "properties": {
                                                                            "results": {
                                                                                "type": "array",
                                                                                "items": {
                                                                                    "type": "object",
                                                                                    "properties": {
                                                                                        "__metadata": {
                                                                                            "type": "object",
                                                                                            "properties": {
                                                                                                "type": {
                                                                                                    "type": "string"
                                                                                                }
                                                                                            }
                                                                                        },
                                                                                        "Key": {
                                                                                            "type": "string"
                                                                                        },
                                                                                        "Value": {
                                                                                            "type": "string"
                                                                                        },
                                                                                        "ValueType": {
                                                                                            "type": "string"
                                                                                        }
                                                                                    },
                                                                                    "required": [
                                                                                        "__metadata",
                                                                                        "Key",
                                                                                        "Value",
                                                                                        "ValueType"
                                                                                    ]
                                                                                }
                                                                            }
                                                                        }
                                                                    }
                                                                },
                                                                "required": [
                                                                    "__metadata",
                                                                    "Cells"
                                                                ]
                                                            }
                                                        }
                                                    }
                                                }
                                            }
                                        },
                                        "TotalRows": {
                                            "type": "integer"
                                        },
                                        "TotalRowsIncludingDuplicates": {
                                            "type": "integer"
                                        }
                                    }
                                },
                                "SpecialTermResults": {}
                            }
                        },
                        "Properties": {
                            "type": "object",
                            "properties": {
                                "__metadata": {
                                    "type": "object",
                                    "properties": {
                                        "type": {
                                            "type": "string"
                                        }
                                    }
                                },
                                "results": {
                                    "type": "array",
                                    "items": {
                                        "type": "object",
                                        "properties": {
                                            "Key": {
                                                "type": "string"
                                            },
                                            "Value": {
                                                "type": "string"
                                            },
                                            "ValueType": {
                                                "type": "string"
                                            }
                                        },
                                        "required": [
                                            "Key",
                                            "Value",
                                            "ValueType"
                                        ]
                                    }
                                }
                            }
                        },
                        "SecondaryQueryResults": {
                            "type": "object",
                            "properties": {
                                "__metadata": {
                                    "type": "object",
                                    "properties": {
                                        "type": {
                                            "type": "string"
                                        }
                                    }
                                },
                                "results": {
                                    "type": "array"
                                }
                            }
                        },
                        "SpellingSuggestion": {
                            "type": "string"
                        },
                        "TriggeredRules": {
                            "type": "object",
                            "properties": {
                                "__metadata": {
                                    "type": "object",
                                    "properties": {
                                        "type": {
                                            "type": "string"
                                        }
                                    }
                                },
                                "results": {
                                    "type": "array"
                                }
                            }
                        }
                    }
                }
            }
        }
    }
}

 

abm
Super User III
Super User III

Hi @nick9one1 

 

Looks like you are getting nullable values for an expected type string. This is because when you generate the schema with sample data this string value was present.  If this particular string value is optional then you just need to manually modify a type of property in the schema. By default you get type String but it is not nullable type. 

 

Please see this post

 

https://blog.thenetw.org/2019/03/11/quick-fix-microsoft-flow-error-invalid-type-expected-string-but-...

 

Thanks



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blogPower Automate Video Tutorials

thank you, that helped. I replaced each "string" with "string", "null". Now the parse works.

The next challenge is looping through the results and extracting the relevant information.

I have got this far;

Untitled1.png
The email seems to contain all the information I need, but in a single string?
e.g. 
{"type":"SP.KeyValue"},"Key":"Write","Value":"2020-07-15T18:14:46.0000000Z","ValueType":"Edm.DateTime"}
{"type":"SP.KeyValue"},"Key":"Url","Value":" http etc. "}

is data I need to run an If statement against, but I doesn't seem to be accessible in the dynamic content. 



Untitled.png

 

Try using a Filter Array or Select I had a similar issue with a complex JSON and i was able to use the Filter Array to pull out only the bits I needed. 

Hi @nick9one1 

As stated by @mousman85 the best option is to use the action "Select"

js-dattics_0-1595193464461.png

This action selects the field you want to keep only.

 

Greetings from Colombia.

 

If this reply helped you to solve the issue, please mark the post as Accepted Solution. Marking this post as Accepted Solution, will help many other users to use this post to solve same or similar issue without re-posting the issue in the group. Saves a lot of time for everyone.

 

thanks for the help so far, I think I'm nearly there. 

I realised I needed to run a second 'Parse JASON' action in a nested for each loop. 

this probably explains it better; 

Untitled3.png

 

these are the only options in the dynamic content in the second for each parse. 

 

Capture1.PNG

is there another step to expose the Key and Value seen in the first image?

abm
Super User III
Super User III

Hi @nick9one1 

 

When you iterate the results of second loop parse 2 you get each item which is key, value and valuetype. The best way to do is add another action Parse JSON and set the schema as key, value and valuetype. 

 

Thanks



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blogPower Automate Video Tutorials

Helpful resources

Announcements
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors
Users online (929)