Transformation of Json file

[pietrocorsaro]

Dear Community,
I am using Grafana 11.6.
I need your support to convert a json file in a readable set of data for the XY chart.
Here the file I get:

{
“2023-10-10T00-00-02 UTC”: {
“x”: [0, 2, 4],
“y”: [27.11, 26.77, 26.90]
},
“item_type”: “OTS3TemperatureData”
}
for each x and Y there are thousands of points.
I need to convert to a file where time is duplicated as timestamp, x as position column and y as measured value column.
Thanks for supporting

[infofcc3]

Step 1: Create input.json file and past your json

Step 2: using python script conver the json to csv (this format ready by xy chart easily)

import json
import csv
from datetime import datetime

# Path to your input file
input_path = r"C:\Users\Dell\Desktop\script\input.json"
output_path = r"C:\Users\Dell\Desktop\script\output.csv"

# Load JSON file
with open(input_path, "r") as f:
    data = json.load(f)

# Write to CSV file
with open(output_path, "w", newline="") as csvfile:
    writer = csv.writer(csvfile)
    writer.writerow(["timestamp", "position", "measured_value"])  # header row

    for ts, values in data.items():
        if ts == "item_type":
            continue  # ignore metadata

        # Format timestamp: "2023-10-10T00-00-02 UTC" → "2023-10-10T00:00:02Z"
        dt = datetime.strptime(ts.split(" ")[0], "%Y-%m-%dT%H-%M-%S")
        formatted_time = dt.strftime("%Y-%m-%dT%H:%M:%SZ")

        # Extract x and y values
        x_vals = values["x"]
        y_vals = values["y"]

        # Write rows
        for x, y in zip(x_vals, y_vals):
            writer.writerow([formatted_time, x, y])

print("✅ Done! CSV saved to output.csv")

Step 3:
install and add datasource

note : while adding datasour it will give error then you can go to folder where python script, input.json, and output.csv are present run this command to adding datasource

Step 4: after adding datasource add transformation

final output:

[pietrocorsaro]

I appreciate the detailed answer. My issue, I apologize if I was not clear, is that I can rely only on transformation inside Grafana, maybe using JSONata or the Transformations.
I am currently struggling with JSONata

[yosiasz]

you can use jsonata to carve up the data the way you want it within grafana using infinity plugin

https://try.jsonata.org/JuGYwxHJ3

[pietrocorsaro]

Many thanks,
but for whatever reason it doesn’t work.

[yosiasz]

checkout my jsonata and compare it to yours. few things missing there.

[pietrocorsaro]

Many thanks!
As you said there were some missing “” and some hidden character.
I’ve appreciated your help