So I need to pull the some substrings from the following data from a query

[
  {
    "rel": "System.LinkTypes.Hierarchy-Forward",
    "url": "https://fakelink/workItems/247155",
    "attributes": {
      "isLocked": false,
      "name": "Child"
    }
  },
  {
    "rel": "System.LinkTypes.Hierarchy-Forward",
    "url": "https://fakelink/workItems/247154 ",
    "attributes": {
      "isLocked": false,
      "name": "Child"
    }
  },
  {
    "rel": "System.LinkTypes.Hierarchy-Forward",
    "url": "https://fakelink/247160",
    "attributes": {
      "isLocked": false,
      "name": "Child"
    }
  },
  {
    "rel": "System.LinkTypes.Hierarchy-Forward",
    "url": "https://fakelink/247156",
    "attributes": {
      "isLocked": false,
      "name": "Child"
    }
  },
  {
    "rel": "System.LinkTypes.Hierarchy-Forward",
    "url": "https://fakelink/workItems/247159",
    "attributes": {
      "isLocked": false,
      "name": "Child"
    }
  },
  {
    "rel": "System.LinkTypes.Hierarchy-Forward",
    "url": "https://fakelink/workItems/247157",
    "attributes": {
      "isLocked": false,
      "name": "Child"
    }
  },
  {
    "rel": "System.LinkTypes.Hierarchy-Forward",
    "url": "https://fakelink/247158",
    "attributes": {
      "isLocked": false,
      "name": "Child"
    }
  }
]

In grafana obviously having that long string in a table is useless to me. I need to somehow parse the data and pull the number at the end of each web link as well as the "Child" phrases and somehow connect those preferably as a Child column (or whatever phrase is in that spot) with the numbers at the end of the web links being the data in the table. I was told I needed to "write a loop to go through the JSON" any help at all, suggestions, anything, would be massively appreciated

note: the string is different for every single item the query pulled up and there's a couple thousand

CodePudding user response：

mv-expand operator, to explode the array.
parse operator, to extract the number from each url.

datatable(doc:dynamic)[dynamic([ { "rel": "System.LinkTypes.Hierarchy-Forward", "url": "https://fakelink/workItems/247155", "attributes": { "isLocked": false, "name": "Child" } }, { "rel": "System.LinkTypes.Hierarchy-Forward", "url": "https://fakelink/workItems/247154 ", "attributes": { "isLocked": false, "name": "Child" } }, { "rel": "System.LinkTypes.Hierarchy-Forward", "url": "https://fakelink/247160", "attributes": { "isLocked": false, "name": "Child" } }, { "rel": "System.LinkTypes.Hierarchy-Forward", "url": "https://fakelink/247156", "attributes": { "isLocked": false, "name": "Child" } }, { "rel": "System.LinkTypes.Hierarchy-Forward", "url": "https://fakelink/workItems/247159", "attributes": { "isLocked": false, "name": "Child" } }, { "rel": "System.LinkTypes.Hierarchy-Forward", "url": "https://fakelink/workItems/247157", "attributes": { "isLocked": false, "name": "Child" } }, { "rel": "System.LinkTypes.Hierarchy-Forward", "url": "https://fakelink/247158", "attributes": { "isLocked": false, "name": "Child" } } ])]
| mv-expand doc
| project doc.url
| parse doc_url with * "/" num:long

Fiddle