Parsing an array of arrays from an MQTT topic with json_v2

Telegraf
, ,
1

Just for someone who is struggling on array of arrays to read tags and fields, here is one way to do it.
Goal is to get line protocol output for named tags with energy as a field value.

And if someone is wandering the MQTT topic comes from a go-e EV Charger which has a nice open API to read.

What I get from an MQTT topic is:

[{
	"name": "Device name 1",
	"energy": 0,
	"cardId": true
}, {
	"name": "Device name 2",
	"energy": 0,
	"cardId": true
}, {
	"name": "n/a",
	"energy": 0,
	"cardId": false
}, {
	"name": "n/a",
	"energy": 0,
	"cardId": false
}, {
	"name": "n/a",
	"energy": 0,
	"cardId": false
}]

Output includes up to 10 nodes, but I shortened it for the purpose of example.

I’ve setup an mqtt_consumer configuration as

[[inputs.mqtt_consumer]]
  servers = ["tcp://localhost:1883"]
  topics = [ "goeCharger/+/cards" ]

  data_format = "json_v2"

  [[inputs.mqtt_consumer.topic_parsing]]
    topic = "goeCharger/+/+"
    measurement = "measurement/_/_"
    tags = "_/chargerid/_"

  [[inputs.mqtt_consumer.json_v2]]
      [[inputs.mqtt_consumer.json_v2.object]]
        path = "@this.#(cardId=true)#"
        tags = ["name"]
        include_keys = ["name", "energy"]
        exclude_keys = ["cardId"]
        [inputs.file.json_v2.object.fields]
          energy = "float"
          cardId = "boolean"

The GJSON filter path @this.#(cardId=true)# should match and leave just the nodes with cardId=true as tested with https://gjson.dev :

[{
	"name": "Device name 1",
	"energy": 0,
	"cardId": true
},{
	"name": "Device name 2",
	"energy": 0,
	"cardId": true
}]

This results for output as

goeCharger,chargerid=201397,name="Device name 1" cardId=true,energy=0 1664257051533353589
goeCharger,chargerid=201397,name="Device name 2" cardId=true,energy=0 1664257051533353589

GJSON filters with JSON objects are nice and powerful to leave out unnecessary data to be pushed the the database.

2

Hi @oh2th,
Many thanks for this contribution. If its alright with you I am going to contribute it to our Telegraf community configs located here: GitHub - InfluxCommunity/Telegraf-Community-Configs: The purpose of this repository is to promote the creation, sharing, and reuse of configs among the Telegraf community. Anybody can submit new configs or improvements upon existing configs and use these configs in their own architectures.

Do you have your GitHub username handy so I can add you to the contributors?
Thanks,
Jay

3

Hello @Jay_Clifford,
Happy to contribute, my Github is with the same handle oh2th.

1 Like
4

Hi @oh2th,

Apologies for piggy-backing on your post, but you solved your issue and this seems like the most similar problem someone has posted about I’ve seen.

I have an MQTT broker that is send the following by a three phase energy monitor:
Topic: device/SERIALNO/realtime
Data:

JSON Object 
{
  "method": "uploadsn",
  "mac": "MACADDRESS",
  "version": "22.3",
  "server": "em",
  "SN": "SERIALNO",
  "Data": [
    [
      250,
      5,
      1250,
      0.685,
      0.05,
      49.97,
      0.99
    ],
    [
      250,
      4,
      1000,
      0.885,
      0.05,
      49.97,
      0.99
    ],
    [
      250,
      3,
      750,
      0.385,
      0.05,
      49.97,
      0.99
    ]
  ]
}

The “Data” object has the format as follows:

Data Format 
[
  [voltage_a, current_a, power_a, import energy_a, export energy_a, frequency_a, power factor_a],
  [voltage_b, current_b, power_b, import energy_b, export energy_b, frequency_b, power factor_b],
  [voltage_c, current_c, power_c, import energy_c, export energy_c, frequency_c, power factor_c]
]

The most data I have been able to get out of this, because the data isn’t described in the JSON, is with the following:

Telegraf Config Attempt 
[[inputs.mqtt_consumer.topic_parsing]]
   topic = "device/+/realtime"
   measurement = "powermonitor"
   tags = "_/serial/_"
  [[inputs.mqtt_consumer.json_v2]]  
	measurement_name = "powermonitor"
	[[inputs.mqtt_consumer.json_v2.field]]
	  path = "Data.0.0"
	  rename = "Voltage A"
	  type = "float"	
	[[inputs.mqtt_consumer.json_v2.field]]
	  path = "Data.0.1"
	  rename = "Current A"
	  type = "float"		
	[[inputs.mqtt_consumer.json_v2.field]]
	  path = "Data.0.2"
	  rename = "Power A"
	  type = "float"
.... etc

Which has as a separate line protocol for each value in the nested array, with the only tag being “serial” which is parsed correctly from the topic name.

In an ideal world, I would like for both SN and mac to be parsed from the JSON object and included in the line as tags. I would also like for the phase (ie whether the data is from array 0, 1 or 2, meaning phase A, B or C) to be added as a tag, and not just in the field name. If possible, I’d also like to be able to set the measurement to be “powermonitor” rather than “device” which is what it sets itself to now.

For the above data (for the data parsed in the configuration snippet included above), that would provide the following line protocol:

Line Protocol 
powermonitor,serial="SERIALNO",mac="MACADDRESS",phase="A",Voltage_A=250.0 1665175980655353589
powermonitor,serial="SERIALNO",mac="MACADDRESS",phase="A",Current_A=5.0 1665175980655353589
powermonitor,serial="SERIALNO",mac="MACADDRESS",phase="A",Power_A=1250.0 1665175980655353589

Does anyone have a clue how I can make progress with this, or am I trying to do something with Telegraf that I ought to be processing and adding data with something else first, and using Telegraf afterwards to add it to InfluxDB?

Thanks

1 Like
5

Suggesting that there may be a way of pre-processing the data before feeding it in made me rethink how I was trying to get the data.

I managed to achieve the above by using the Starlark processor to directly process the raw mqtt_consumer input which met all my requirements.

Thanks for the inadvertent push in the right direction! :smiley:

1 Like