How to get an json array into InfluxDB Table with Telegraf

Telegraf
, ,
1

Hey Guys,

I am trying to feed a MQTT-Message in json_v2 format which contains an array of integers into InfluxDB. Currently it just takes the last number of the array and all others are lost.

if the array in the message looks like this:

{
    "data" : {
        "energy" : [11,12,13]
    }
}

The config looks like this:

[[inputs.mqtt_consumer.json_v2.field]]
    rename = "energy_list"
    path = "data.energy"
    data_type = "array"

Influx will only give me the “13” in the table, but Id like to have it “11, 12, 13”

2

Hello @Nils_Franke,
Welcome!
So this documentation might be helpful.

I would try .object and maybe the prepend too.
I think that should get you all of your metrics. Though I wonder if/bet that they’re being overwritten. ’
Yup just confirmed with the --test flag and printed lines to stdout

> file,host=MacBook-Pro-4.local energy=11 1706728355000000000
> file,host=MacBook-Pro-4.local energy=12 1706728355000000000
> file,host=MacBook-Pro-4.local energy=13 1706728355000000000

You would need to add a timestamp to each energy value. or make energy a tag (which is not ideal).

You can add a timestamp with the execd processor plugin.
Your python script for example could look like:

import sys
import json
from datetime import datetime, timedelta

def add_timestamps(data):
    base_time = datetime.now()
    if 'energy' in data:
        for i, value in enumerate(data['energy']):
            data['energy'][i] = {
                "value": value,
                "timestamp": (base_time + timedelta(seconds=i)).isoformat() + "Z"
            }
    return data

# Read JSON from stdin
input_data = json.load(sys.stdin)

# Process the data
output_data = add_timestamps(input_data)

# Write JSON to stdout
json.dump(output_data, sys.stdout)

Your telegraf config would be:

 [[inputs.file]]
    files = ["./tel_test.json"]
    data_format = "json_v2"
  [[inputs.file.json_v2]]
        [[inputs.file.json_v2.object]]
            path = "data"
#             disable_prepend_keys = true
[[processors.execd]]
  command = ["python3", "./add_timestamps.py"]
  data_format = "json"

and your add_timestamps.py would change the timestamps slightly.

Or you could make 3 field keys this is the better solution i’m guessing. Still the approach could be the same. Youd just change the logic in your python script.

1 Like
3

@Nils_Franke so there is a way to do this with xpath apparently.

Wooo! Thank you for asking

4

Hey Anaisdg,

thank you for your quick answers. I actually came along these posts before reaching out to you and they are not very helpful to be honest. Not least because it says “Closed, solution found” without pointing out the solution. Every example, also on other webpages, shows only line protocol outputs, but we want to have it being shown correctly in the Influx->Data Explorer-> “Simple Table”. Can you give me a doc or a step-by-step guide on this one?

Thzanks for your support,
Nils