Workaround for "fields have different lengths"

[yaegashi]

Hi, I understand what is meant by Fields have different lengths, but is there any workaround to avoid this without losing any array elements by coalescing missing values to some default? It's a critical requirement for us.

For example, form the following input:

{
  "services": [
    {
      "name": "order-api",
      "version": "1"
    },
    {
      "name": "billing-api"
    }
  ]
}

I want to get ["order-api", "billing-api"] for name field, ["1", ""] for version field.

[kstaken]

I'm battling with the same issue and it makes this plugin pretty problematic on a dataset that allows missing fields (which is common on JSON APIs). Is this issue here by design? Simply using the empty string in this case seems like it would make a lot more sense. Or allow it as an option at least.

[marcusolsson]

I'm running into this myself from time to time, so I'm definitely aware of the struggle here.

The problem right now is that the JSON Path queries for each field are run independently of each other. I'd love if JSONPath had supported setting defaults or something similar.

I have some ideas on how to solve it. For example, the JSONPath library seem to allow you to run a callback for the values, which could help to solve this.

Another idea I've had is to support nested JSONPaths where you could define a default value if a nested query returns empty results.

This will be supported as soon as we figure out a reasonable way to do it :)

[kstaken]

I'm not familiar with grafana development but I took a quick look at the code and can't this be fixed by simply adjusting the empty array return from JSONPath? Since you're just trying to ensure the generated DataFrame is valid, equating no return from JSONPath with the empty string seems like it should be a valid thing to do and more usable than throwing an error.

Something simple like:

        const values = JSONPath({ path, json });
        if (values.length === 0) {
          values.push("");
        }

[kstaken]

Spent a few minutes to figure out how to run the plugin in dev mode and I see the issue now. The return value from JSONPath is already a full array and you completely lose the record alignments in the DataFrame. It also looks like even if you use the callback to JSONPath it doesn't appear to actually call it when the field is completely missing.

[kstaken]

It looks like this likely requires a change to the underlying JSONPath-Plus library. The other JSONPath lib (https://github.com/json-path/JsonPath) has an option DEFAULT_PATH_LEAF_TO_NULL which appears to accommodate this scenario but the JSONPath-Plus library doesn't appear to have a similar option that I can see.

I went to open a ticket on JSONPath-Plus and realized that project is now un-maintained so I definitely get why you hit a wall with this.

[marcusolsson]

The return value from JSONPath is already a full array and you completely lose the record alignments in the DataFrame.

Yes, that's better explanation than mine :)

It also looks like even if you use the callback to JSONPath it doesn't appear to actually call it when the field is completely missing.

Thank you for taking the time to look into this 🙏 One of my plans for the next major release is to rewrite it into a backend plugin (in Go) to support alerting. The tricky thing will be to find a JSONPath library for Go. Unfortunately, switching to any other library will likely make it hard to maintain feature parity.

I'll be somewhat unavailable this week, but I'm planning to spend the next two weeks of my vacation on plugin work, I might take the opportunity to start working on this then.

[agrimpelhuber]

I think (hope, guess) I have found a workaround. For me at least it works around the issue of missing fields. Maybe it helps others, too. Let me know how universal that is. The trick for me was to use JSONata instead of JSONPath for the affected fields. Took a little bit of time to get used to it, but I have a few issues sorted out now. Two features in JSONata seem important to me:

• JSONata always returns an array for values, or so I've read. Which is tedious when picking the only / single value with [0], but at least there's a value to work with all the time!
• JSONata supports in-line functions, conditions etc. Yeah!

Here's a very basic JSONata expression that does it for me with the field "h" (for "hours")

$.rows.($count(h) > 0 ? ( $string(h) = "null" ? "Null value" : h ) : "Empty Value")

Explanation:

• 2 nested conditions (Condition ? True : False syntax) setting default / replacement values for 2 different "empty field" situations, depending on what the JSON API returns
• The outer one testing for an empty array (remember, values are always arrays)
• The inner one testing for the fact that the array could still be a null value. Not sure whether it should technically be $string(h[0]), but $string(h) works fine for me
• Not sure which of the possible JSON situations (missing / empty /null fields) triggers which condition - you tell me :-)

Hope this helps someone

[sebas1986]

Hello,

very interesting discussion.
Any way to understand which is the field that is causing the issue? I've got a table with more than 50 fields and i really struggle in finding the one(s) not working.

Many thanks,

Sebastian

[agrimpelhuber]

@sebas1986, for me, it was a bit trial and error, but I have to admit that I had the fortunate situation that I had just one field with missing values, and just a few records with those missing values.

One thing I would probably try is to pull the JSON data into some sort of table (possibly into Excel, using Power Query) in order to find out where the missing data sits.

To help you, can you post a sample of the JSON data that's causing your trouble?

Edit: Another, rather radical way could be to just use JSONata. A bit awkward, but more robust, I guess.

[ljheidel]

Maybe I misunderstand the question, but I'm pulling data from openweathermap.org and I have (truncating) this as my JSON:

{
  "main": {
    "temp": 72.88,
    "feels_like": 73.63,
    "temp_min": 67.01,
    "temp_max": 78.57,
    "pressure": 1014,
    "humidity": 80
  },
  "visibility": 10000
}

The thing is, only if there's rain, this object is present:

    "rain": {
      "1hr": 0.231,
      "3hr": 0.312
    }

I can't make it work with JSONPath as a field, but this JSONana ternary seems to do the trick:

($exists($.rain."1hr") ? $.rain."1hr" : 0)

It hasn't rained since I started this project, but I've tested it replacing rain."1hr" with wind.gust and it seems to work fine.

[Glandos]

I have the same issues with weather data from https://donneespubliques.meteofrance.fr/?fond=produit&id_produit=90&id_rubrique=32
All temperatures are in Kelvin, so I used JSONata to subtract 273.15. But some values are null, and I tried $v = null ? null : $v - 273.15, but then, it makes an error…
So my workaround is to get the last non null value from the array and use it, to have a flat line (t is the temperature):

$map($.measures.t, function($v, $i, $a) {
   $v != null ? $v - 273.15 : $a[[0..$i]][$!=null][-1] - 273.15
})

Yes, it is not really beautiful. Grafana is able to handle null values instead of number, so I would have expected the same here 😉

[jasplund]

Hi
This discussion is quite old and my question is slightly different so feel free to direct me to another thread.
So here is my problem:
I'm trying to show pending changes in Gerrit in a table and in one cell I want a comma separated list of the names of the reviewers assigned.
Simplified the JSON looks something like this
[
{
"id": "demomasterIdaf5e098d70898b7119f6f4af5a6c13343d64b57",
"project": "demo",
"branch": "master",
"reviewers": {
"REVIEWER": [
{
"_account_id": 1000096,
"name": "John Doe",
"email": "[email protected]",
"username": "jdoe"
},
{
"_account_id": 1000097,
"name": "Jane Roe",
"email": "[email protected]",
"username": "jroe"
}
]
}
}
}
]

It feels like I have tried everything but I cant figure out how to do it....

Thanks!

[jasplund]

So I found the solution my self :)
JSONata expression reviewers.[REVIEWER].[name]