Help with Formatting JSON Data into a Table

[wagnerfranchin]

Hi everyone,

I have the following JSON data:

I’d like to format this data into a table, where each “metric” (p50, p90, p99) appears as a row, and the other details (timestamp, attributes like git_repo, branch, git_commit) are columns. Here’s an example of how I imagine the table:

I have tried using UQL like, to just extract some data:

parse-json | jsonata "metrics.{ 'time': $$.timestamp * 1000, 'Metric Name': name, 'Metric Value': value }"

But I still have this in Grafana:

How can I structure this in code? Any advice or examples would be greatly appreciated!

Thanks in advance!

[sowdenraymond]

can you post the actual json, instead of a snip please?

[dawiddebowski]

Hi, not a UQL fan, but if you’re using infinity plugin (probably you do) and switch to a backend parser, you can use following jsonata code:

$map(metrics, function($v, $i) {
    $merge([{
        "timestamp": timestamp,
        "metric": $v.name,
        "unit": $v.unit,
        "Value": $v.value
    }, attributes])
})

might need to organize fields though

[wagnerfranchin]

Sample:

[
  {
    "timestamp": 1706220908,
    "metrics": [
      {
        "name": "p50",
        "unit": "us",
        "value": 56
      },
      {
        "name": "p90",
        "unit": "us",
        "value": 125
      },
      {
        "name": "p99",
        "unit": "us",
        "value": 280
      }
    ],
    "attributes": {
      "git_repo": "https://github.com/mytest/test",
      "branch": "main",
      "git_commit": "6995e2de6891c724bfeb2db33d7b87775f913ad1"
    }
  },
  {
    "timestamp": 1706221200,
    "metrics": [
      {
        "name": "p50",
        "unit": "us",
        "value": 60
      },
      {
        "name": "p90",
        "unit": "us",
        "value": 130
      },
      {
        "name": "p99",
        "unit": "us",
        "value": 300
      }
    ],
    "attributes": {
      "git_repo": "https://github.com/mytest/test",
      "branch": "main",
      "git_commit": "7a9f8c1b2dcf134defabcfe329cf5f9a58c8d9d6"
    }
  }
]

[dawiddebowski]

Update after data sample:

$reduce($map($, function($item) {
    $map(metrics, function($v, $i) {
        $merge([{
            "timestamp": $item.timestamp,
            "metric": $v.name,
            "unit": $v.unit,
            "Value": $v.value
        }, $item.attributes])
    })
}), $append)

something like this should work

[wagnerfranchin]

Thanks for the suggestions, but nothing seems to show (No data) after adding it.

Before:

After:

[sowdenraymond]

try changing the parser to backend…

[dawiddebowski]

You could use jsonnata in uql, but it’s less readable, so I’d suggest changing the parser to backend instead of UQL

[wagnerfranchin]

That’s it. Thank you very much @dawiddebowski and @dawiddebowski !

[wagnerfranchin]

Just one question, when I added a second timestamp with similar data, the table seems to show results duplicated.

Removing the function:

[dawiddebowski]

when I did the jsonata code I created a loop for every item in the root list (the first map) and then inside I made a loop over all metrics (from all of the objects), so right now you got duplicated data and if you had three jsons, you’d get tripled sorry bout that.
Fixed code:

$reduce(
    $map($, function($item) {
        $map($item.metrics, function($v, $i) {
            $merge([{
                "timestamp": $item.timestamp,
                "metric": $v.name,
                "unit": $v.unit,
                "Value": $v.value
            }, $item.attributes])
        })
    })
, $append)

notice $item.metrics in the second map

[wagnerfranchin]

Perfect, thanks!

[yosiasz]

crawling up the ladder to the parent using %

Path Operators · JSONata

$.metrics.{
  'name': name,
  'unit': unit,
  'value': value,
  'timestamp': %.timestamp,
  'git_repo': %.attributes.git_repo,
  'branch': %.attributes.branch,
  'git_commit': %.attributes.git_commit
}

https://try.jsonata.org/XrbztwdP9

[wagnerfranchin]

I have tried also this inline approach, but I don’t know what I’m missing in the UQL

parse-json
jsonata "$.metrics.{
  'name': name,
  'unit': unit,
  'value': value,
  'timestamp': %.timestamp,
  'git_repo': %.attributes.git_repo,
  'branch': %.attributes.branch,
  'git_commit': %.attributes.git_commit
}"

[yosiasz]

pay close attention to the uql I posted and also checkout the uql doc for jsonata

https://grafana.github.io/grafana-infinity-datasource/docs/uql

[wagnerfranchin]

parse-json
jsonata "$.metrics.{'name': name,'unit': unit,'value': value,'timestamp': %.timestamp,'git_repo': %.attributes.git_repo,'branch': %.attributes.branch,'git_commit': %.attributes.git_commit}"

This works, thanks!