How to extract the array data from json using uql query

Grafana
,
1

So at present I have json data as displayed below

[
{
“inv”: 10,
“geo”: [{“key”: “usa”, “value”: 10}, {“key”: “canada”, “value”: 0}],
},
{
“inv”: 3,
“geo”: [{“key”: “usa”, “value”: 3}, {“key”: “canada”, “value”: 0}],
},
]

Now I want to show values in table with columns as “inv”, “usa”, “canada”

How can I achieve this?

2

Hi, I guess you’re using Infinity Plugin for that?

Unfortunately, I’m not sure how to do it in plain UQL.

You could use UQL parser with jsonata query like this:

parse-json
| jsonata "$map($,function($v) {{'inv': $v.inv,'usa': $v.geo[key = 'usa'].value,'canada': $v.geo[key = 'canada'].value }})"

Or you could choose to use Backend parser, you could use jsonata code out of the box:

$map(
  $,
  function($v) {
    {
      "inv": $v.inv,
      "usa": $v.geo[key = "usa"].value,
      "canada": $v.geo[key = "canada"].value
    }
  }
}

(I find jsonata easier and ChatGPT can do jsonata :smile:).

Here’s a screen with jsonata configuration

image
image1369×797 47.6 KB

Hope that helps anyway!

2 Likes
3

how does this look?

$.geo.{
    'inv': %.inv,
    key : value
}

https://try.jsonata.org/oFbxrqMOk

4

You can also use the following UQL

parse-json
| mv-expand "geo_item"="geo"
| project "inv", "key"="geo_item.key", "value"="geo_item.value"

Learn more about this UQL commands at UQL Parser | Grafana Plugins documentation

1 Like
5

This one won’t work as inv, canada, usa belongs to only one item property.

[
{
“inv”:10,
“canada”:0,
“usa”: 10,
},
{
{
“inv”:10,
“canada”:0,
“usa”: 3,
},
]

is what I am looking for

6

But this will create two records with same inv and each having different country

[
{
“inv”:10,
“canada”:0,
“usa”: 10,
},
{
{
“inv”:10,
“canada”:0,
“usa”: 3,
},
]

I need a structure like this

7

image
image1858×157 10.1 KB

I am getting this error while trying to replicate your solution. Any help?

8

Okay you had an syntax error, the end braces should be ‘)’ not ‘}’. Then it works

1 Like