Flux: Extract element within json and group only one key-value

Fluxlang
1

Inside my “planner” bucket I have an “updatedTemplates” tag which is a JSON object that contains three key-value pairs: “id”, “fragmentName” and “name” → Here is an example: {“id”: " 3435463hf57uj7", “fragmentName”: “example”, “name”: “Glenoid”} .
My goal is to retain only the “name” values and then, using a pie chart, be able to have a distribution of the different values.

I started by creating this query:
from(bucket: “planner”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r._measurement =~ /^$measurement$/ and r[“environment”] =~ /^$environment$/ and r[“step”] == “update-templates” and exists r[“updatedTemplate”])
|> map(fn: (r) => ({
updateName: r[“updatedTemplate”]
}))

and then through the “Transform” tab I did an “Extract fields” transformation with the source “updateName” and format “JSON” and here I managed to have just the names in one column, then I tried to do a “Group by” transformation of the name to make a count for each result however nothing happens and I can’t create the desired graph.
Can anyone help me in this regard?
Thanks

2

@Claudia_Silva It sounds like you’re doing the JSON transformation in Grafana, but this doesn’t actually change the data in the query, so grouping the data wouldn’t work. The JSON transformation needs to happen as part of the query to be able to group by the parsed JSON values.

There is a little known, but incredibly powerful feature of Flux called dynamic types that we built specifically for working with JSON data (links to docs down below). The issue with JSON data in Flux is that Flux doesn’t know exactly how JSON data should be typed. Dynamic types act as a temporary type that you can then use to explicitly cast JSON types to Flux types. So for your query, you’d do the following:

  1. Import the “experimental/dynamic” package.
  2. In your map() call:
    • Cast the JSON string to bytes
    • Use dynamic.jsonParse() to parse the bytes into a dynamic JSON object.
    • Use the dynamic JSON object to build a new Flux record with explicit types.
    • Return a new row record with the updated column that uses the template name.
import "experimental/dynamic"

from(bucket: "planner")
    |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
    |> filter(
        fn: (r) =>
            r._measurement =~ /^$measurement$/ and r["environment"] =~ /^$environment$/ and r["step"]
                ==
                "update-templates" and exists r["updatedTemplate"],
    )
    |> map(fn: (r) => {
        _templateAsBytes = bytes(v: r["updatedTemplate"])
        _templateParsed = dynamic.jsonParse(data: _templateAsBytes)
        _templateTyped = {
            id: string(v: _templateParsed.id),
            fragmentName: string(v: _templateParsed.fragmentName),
            name: string(v: _templateParsed.name),
        }

        return {r with updateName: string(v: _templateTyped.name)}
        }
    )
1 Like
3

Understood, I now understand how I can extract the data in the query itself, thank you!!
Still, I’m getting three columns _time, _value and updateName and a dropdown to select the measurement options (in this case success and error). After return, I just wanted to make the various updateName options visible so that I could understand the % of each of the options.
I tried to create a group by with set and count() but it doesn’t work…

4

This does not work?

import "experimental/dynamic"

from(bucket: "planner")
    |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
    |> filter(
        fn: (r) =>
            r._measurement =~ /^$measurement$/ and r["environment"] =~ /^$environment$/ and r["step"]
                ==
                "update-templates" and exists r["updatedTemplate"],
    )
    |> map(fn: (r) => {
        _templateAsBytes = bytes(v: r["updatedTemplate"])
        _templateParsed = dynamic.jsonParse(data: _templateAsBytes)
        _templateTyped = {
            id: string(v: _templateParsed.id),
            fragmentName: string(v: _templateParsed.fragmentName),
            name: string(v: _templateParsed.name),
        }

        return {r with updateName: string(v: _templateTyped.name)}
        }
    )
    |> group(columns: ["updateName"])
    |> count()
5

Yes it works thank you very much, in the meantime I realized that I was gathering the results for two measurements where one works but the one I was testing is giving the error "count: schema collision detected: column “_value” is both of type float and string ". But I will try to understand the difference in data in my logs.

6

This means you’re querying fields with two different types and when grouping by updateName, those types get grouped into the same table(s). I’d suggest casting all your values to strings before `count():

// ... the rest of your query
    |> group(columns: ["updateName"])
    |> toString()
    |> count()
1 Like
7

Still in this context of updateTemplate. In this case, I am creating a table in which I am not going to get the updateName but rather the updateID and I want to compare it with the value in the oldTemplateID column to understand whether there was a change or not. And with this query that I adapted, everything is fine. However, I am seeing several responses for the same Plan ID. I would like to return only the last record for each Plan ID. Could you help me? Thanks!!

Current query:

import “experimental/dynamic”

from(bucket: “planner”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r._measurement =~ /^$measurement$/ and r[“environment”] =~ /^$environment$/ and r[“plan_id”] =~ /^$plan_id$/ and r[“step”] == “update-templates” and exists r[“updatedTemplate”])
|> map(fn: (r) => {
_templateAsBytes = bytes(v: r[“updatedTemplate”])
_templateParsed = dynamic.jsonParse(data: _templateAsBytes)
_templateTyped = {
id: string(v: _templateParsed.id),
fragmentName: string(v: _templateParsed.fragmentName),
name: string(v: _templateParsed.name),
}

  return {r with updateID: string(v: _templateTyped.id)}
  }

)
|> map(fn: (r) => ({r with ID_diff: r.updateID != r[“oldTemplateId”]}))
|> map(fn: (r) => ({
_time: r._time,
measurementName: r._measurement,
planId: r[“plan_id”],
procedurename: r[“procedure”],
modalityname: r[“modality”],
updateIDFinal: r[“updateID”],
oldID: r[“oldTemplateId”],
changeID: r[“ID_diff”]
}))

Note: I have already tried to create a set to fetch the last →
|> last(column: “oldTemplateId”)
|> set(key: “context”, value: “oldTemplateId_last”) but it keeps giving me error after error…

8

In this last question I asked, I didn’t explain myself well… what I want is now that I have the updateID, I want to compare the last value of the updateID with the first value of the oldTemplateID column for the same plan_id and see if it has the same value.

So I made this query:

import “experimental/dynamic”

data =
from(bucket: “planner”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r._measurement =~ /^$measurement$/ and r[“environment”] =~ /^$environment$/ and r[“plan_id”] =~ /^$plan_id$/ and r[“step”] == “update-templates” and exists r[“oldTemplateId”])
|> pivot(rowKey: [“_time”], columnKey: [“_field”], valueColumn: “_value”)
|> group(columns: [“_measurement”,“plan_id”, “userId”, “procedure”, “modality”])

first =
data
|> first(column: “oldTemplateId”)
|> set(key: “context”, value: “oldTemplateId_first”)

last =
data
|> last(column: “updatedTemplate”)
|> map(fn: (r) => {
_templateAsBytes = bytes(v: r[“updatedTemplate”])
_templateParsed = dynamic.jsonParse(data: _templateAsBytes)
_templateTyped = {
id: string(v: _templateParsed.id),
fragmentName: string(v: _templateParsed.fragmentName),
name: string(v: _templateParsed.name),
}

        return {r with context: "updateID_last", updateID: string(v: _templateTyped.id)}
        }
    )

union(tables: [first, last])
|> pivot(rowKey: [“plan_id”], columnKey: [“context”], valueColumn: “oldTemplateId”)
|> map(fn: (r) => ({r with updateID_diff: r.oldTemplateId_first != r.updateID_last}))
|> map(fn: (r) => ({
measurementName: r._measurement,
planId: r[“plan_id”],
userID: r[“userId”],
procedureName: r[“procedure”],
modalityName: r[“modality”],
firstOldTemplateID: r[“oldTemplateId_first”],
lasttUpdateID: r[“updateID_last”],
changeOLD: r[“updateID_diff”]
}))

The issue is that it is fetching the first value from firstOldTemplateID, but in lasttUpdateID it is also returning the first instead of the last and I think it is because in the pivot it is putting valueColumn: “oldTemplateId”… Will it be that?

Right now it’s giving me:

Plan ID Old Template ID Update Template ID Change ID?
123456 7654321 1e9u465 True

But it should give false because it is true that the Update Template has already changed it to “1e9u465” but the last value is the same as the Old Template ID “7654321” .
Contextualizing the same plan ID can make changes over time to the updateID value but I want to know if the last plan_id returns to the initial value or is equal to the Old Template ID.
Could you help me @scott ? Thanks!!

9

I leave the solution here in the hope that it can help someone too :slightly_smiling_face:

import “experimental/dynamic”

data =
from(bucket: “planner”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r._measurement =~ /^$measurement$/ and r[“environment”] =~ /^$environment$/ and r[“plan_id”] =~ /^$plan_id$/ and r[“step”] == “update-templates” and exists r[“oldTemplateId”] and r[“userId”] =~ /^$userId$/)
|> pivot(rowKey: [“_time”], columnKey: [“_field”], valueColumn: “_value”)
|> group(columns: [“_measurement”,“plan_id”, “userId”, “procedure”, “modality”])

first =
data
|> first(column: “oldTemplateId”)
|> set(key: “context”, value: “oldTemplateId_first”)

last =
data
|> last(column: “updatedTemplate”)
|> map(fn: (r) => {
_templateAsBytes = bytes(v: r[“updatedTemplate”])
_templateParsed = dynamic.jsonParse(data: _templateAsBytes)
_templateTyped = {
id: string(v: _templateParsed.id),
fragmentName: string(v: _templateParsed.fragmentName),
name: string(v: _templateParsed.name),
}

        return {r with context: "updatedTemplate_last", updatedTemplate: string(v: _templateTyped.id)}
        }
    )

join(tables: {first: first, last: last}, on: [“plan_id”,“userId”,“_measurement”])
|> map(fn: (r) => ({
measurementName: r._measurement,
planId: r[“plan_id”],
userID: r[“userId”],
procedureName: r[“procedure”],
firstOLD: r[“oldTemplateId_first”],
lasttUpdate: r[“updatedTemplate_last”],
changeOLD: r[“oldTemplateId_first”] != r[“updatedTemplate_last”]
}))