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