How to read arrays from JSON where the keys are dates and value is the array using Infinity datasource plugin

[jorgeuos]

• What Grafana version and what operating system are you using?
  Grafana v10.4.0 (03f502a94d) Community Edition on Docker

• What are you trying to achieve?
  I want to show a table with the provided JSON below.

• How are you trying to achieve it?
  With the Infinity data source, also tried with the JSON API
  I switched to Infinity to try out the UQL.

• What happened?
  The rows in my tables are JSON strings instead of displaying them in the table.

• What did you expect to happen?
  Display a table, preferably with the dates as columns and the objects within as rows.

The Json

{
    "2024-07-05": [
        {
            "label": "student",
            "nb_visits": "32203"
        },
        {
            "label": "shopper",
            "nb_visits": "8145"
        }
    ],
    "2024-07-06": [
        {
            "label": "student",
            "nb_visits": "31703"
        },
        {
            "label": "shopper",
            "nb_visits": "6700"
        }
    ],
    "2024-07-07": [
        {
            "label": "student",
            "nb_visits": "32680"
        },
        {
            "label": "shopper",
            "nb_visits": "7916"
        }
    ],
    "2024-07-08": [
        {
            "label": "student",
            "nb_visits": "49184"
        },
        {
            "label": "shopper",
            "nb_visits": "11167"
        }
    ],
    "2024-07-09": [
        {
            "label": "student",
            "nb_visits": "47390"
        },
        {
            "label": "shopper",
            "nb_visits": "10809"
        }
    ],
    "2024-07-10": [
        {
            "label": "student",
            "nb_visits": "43619"
        },
        {
            "label": "shopper",
            "nb_visits": "8664"
        }
    ],
    "2024-07-11": []
}

The closest I get to a table with UQL is:

parse-json
| project kv()

But it gives me a table like this:

Trying to add logic like this:

parse-json
| project kv()
| mv-expand date=key, visits=value
| mv-expand visit=visits
| project date, label=visit.label, nb_visits=visit.nb_visits

Just returns no data.

Using just the JSON type gives me a table like this:

Which is more like what I want, but I need the JSON arrays in the rows displayed correctly, something like:

label	2024-07-05	2024-07-06	2024-07-07	…and so on
student	32203	31703	32680	…
shopper	8145	6700	7916	…

How do I proceed?

[grant2]

Welcome @jorgeuos to the forum. I think you made a good start at this!

I am not very good at UQL, but I think that approach plus JSONata would work, but none of my attempts got what you desired.

Instead, I did a hybrid approach whereby I used some transformations to parse the JSON data:

You can summarize the data even further using transformations.

[jorgeuos]

Nice! Thank you for your help.

I played around with the dataset now and I managed actually to do something that works for me. It even ended up being better than I expected according to my initial vision/goal.

parse-json
| project kv()
| extend "key", "Students"=tonumber("value[0].nb_visits"), "Shoppers"=tonumber("value[1].nb_visits"), "Total"=sum("Students","Shoppers")
| project-away "value"

I also added another query, with a transformation:

The other dataset:

{
  "2024-07-05": 40348,
  "2024-07-06": 38403,
  "2024-07-07": 40596,
  "2024-07-08": 60351,
  "2024-07-09": 58199,
  "2024-07-10": 66286,
  "2024-07-11": 5819
}

parse-json
| project kv()
| extend "Visits"="value"
| project-away "value"

And I could calculate the Delta in new column and give it a color: