Need result with Nested Json Object based on group

InfluxDB 2 Dashboards
1

Hi,

My problem is -

  • I need to group my data with one of my column.
  • I need to jsonify (nested) and return json object

My query is like -

import "json"
from(bucket: "TESTDB")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "data_a9313")
  |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
  |> map(fn: (r) => ({ r with
    jsonStr: string(v: json.encode(v: {"time":r._time,"data":r.call_id}))}))
  |> keep(columns: ["jsonStr","assetId"])
  |> group(columns: ["assetId"])
  |> yield()

I got a solution which stringify the json object may be.

image
image1351×449 54.5 KB

I need the end result as group asset_id like -

{
    "assetId": {
        "time" : <_time>,
        "call_id": <call_id>
    },
    "assetId": {
        "time" : <_time>,
        "call_id": <call_id>
    }...
}

Example -

{
    "67egjwqeh": {"call_id":785,"time":"2021-08-28T00:39:21.17Z"},
    "uiwey87rew": {"call_id":112,"time":"2021-09-27T00:39:21.17Z"},
    "lkda789": {"call_id":112,"time":"2021-09-26T00:39:21.17Z"}...
}

Can you help me out how can i do that.
Thank you

2

@debnath can you share what the table/s look like before the pivot()?

3

@samdillard
Before pivort() it look like this -

image
image1349×659 111 KB

4

Thanks, I think something is missing here. I’m unclear where the original screenshot’s JSON string came from. Where is “mean” coming from? Where is “location” coming from?

5

@samdillard
Sorry i gave you the wrong one small mistake -

import "json"
from(bucket: "TESTDB")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "sensor_data_a9313")
  |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
  |> map(fn: (r) => ({ r with
    jsonStr: string(v: json.encode(v: {"time":r._time,"call_id":r.call_id}))}))
  |> keep(columns: ["jsonStr","assetId"])
  |> group(columns: ["assetId"])
  |> yield()

You can consider this -

image
image1230×241 38.8 KB

JSON String i have done for experiment purpose -
I did pivort() first then each row value i made as a JSON string.
I did string but need object.
Except those mistakes you can follow my above requirement and expected output example.

Thank you

6

In the original question, you wanted:

{
    "assetId": {
        "time" : <_time>,
        "call_id": <call_id>
    }
}

in each jsonStr column, right?

Can you not just add the assetId field directly, like:

|> map(fn: (r) => ({ r with
    jsonStr: string(v: json.encode(v: {"assetId": {"time":r._time,"data":r.call_id}}))}))
7

@samdillard
Thanks for the reply. But that’s not that i looking for.

Actually i want the whole result on one line.

This is the result after your solution -

image
image1219×230 32.7 KB

But i am expecting -

{
	"03aec595d95c98c746781d635c464d97": [{
			"data": 3279740,
			"time": "2022-01-21T12:58:02.399Z"
		},
		{
			"data": 3603900,
			"time": "2022-01-21T12:58:02.684Z"
		}
	],
	"055caa286818d0d18c73196c926ec624": [{
			"data": 4041538,
			"time": "2021-11-23T11:35:36.103Z"
		},
		{
			"data": 1480819,
			"time": "2021-11-23T11:35:36.345Z"
		},
		{
			"data": 3118567,
			"time": "2021-11-23T11:35:36.591Z"
		}
	]
}

There may be millions of assetId and millions of data corresponding to each asset. So I’m expecting that whole result as JSON object and in 1 record.

It might look like this -

image
image1219×230 11 KB

Is that possible?

8

Interesting you’d want this in a single record. Can you come over to the Slack community so we can chat more efficiently? https://influxdata.com/slack

Once there, feel free to DM me at @Sam Dillard