How to parse JSON using json_query

Telegraf
, ,
1

I’m trying to parse a JSON file received by a MQTT Broker (Mosquitto) connection to feed an already create database. I’m using telegraf and influxDB on a VM(Ubuntu 18.04). I can see the value from mqtt_consumer inside the database, however, it is a not well parsed JSON where variable and data are shifted.

This is the JSON file:
{ "message":{ "basic_container":{ "confidence":{ "altitude":15, "semi_major_confidence":0, "semi_major_orientation":0, "semi_minor_confidence":0 }, "reference_position":{ "altitude":0, "latitude":414066546, "longitude":21750345 }, "station_type":5 }, "generation_delta_time":45315, "high_frequency_container":{ "confidence":{ "curvature":7, "heading":127, "longitudinal_acceleration":102, "speed":127, "vehicle_length":4, "yaw_rate":8 }, "curvature":1023, "curvature_calculation_mode":2, "drive_direction":2, "heading":3601, "longitudinal_acceleration":161, "speed":500, "vehicle_length":1023, "vehicle_width":62, "yaw_rate":32767 }, "protocol_version":2, "station_id":224 }, "origin":"on_board_application", "source_uuid":"cttc_car_224", "type":"cam", "version":"1.0.0", "timestamp":1605886891388 }

The part of telegraf.conf is this:

[[inputs.mqtt_consumer]] servers = [“tcp://*.*.*.*:1883”] topics = [ “one/#”, “two#”, ] qos = 0 connection_timeout = "30s" max_undelivered_messages = 1000 persistent_session = false json_query = "" data_format = "json" tag_keys = ["timestamp"]

I’ve tried to get just a simple tag “station_type” but it is not shown at the influx measurements.
This is the influxdb part:

[[outputs.influxdb]] urls = ["http://127.0.0.1:8086"] database = "v2x_manager" write_consistency = "any" timeout = "5s"

This is the influx output

VirtualBox:~$ influx
Connected to http://localhost:8086 version 1.8.3
InfluxDB shell version: 1.8.3

use v2x_manager
Using database v2x_manager
show measurements
name: measurements
name

cpu
disk
diskio
kernel
mem
processes
swap
system

This example only shows how to get the value of timestamp, however, I need to get all the variable values. How can I do it?

2

Hello @rparada,
The json query is used to exclude data from converting to line protocol or writing it to InfluxDB>
By default the JSON input file plugin will convert all of the data into fields. You’ll want to specify the timestamp as a the timestamp config option.

  files = ["your_json.json"]
  ## Data format to consume.
  ## Each data format has its own unique set of configuration options, read
  ## more about them here:
  ## https://github.com/influxdata/telegraf/blob/master/docs/DATA_FORMATS_INPUT.md
  data_format = "json"
  json_time_key = "timestamp"
  json_time_format = "unix"
  tag_keys = [
    "curvature",
    "altitude",
    "whatever else you want to be a tag"]

If you want to drop any tags or fields you can use fieldpass or taginclude to specify the fields and tags you want to keep.

  taginclude = ["message_basic_container_station_type"]
  fieldpass = ["message_station_id"]

Does that help at all?

3

Thanks @Anaisdg, it was easier that expect, only using data_format="json".

4

@Anaisdg, however, know I’m getting this error when reading the same JSON through socket_listener (UDP), I get this error [inputs.socket_listener] Unable to parse incoming packet: invalid character I have data_format="json", any clue?

1 Like
5 
###############  telegraf config ################

  data_format = "json"


 tag_keys = [
    "name"
  ]

  fieldpass = ["points"]
Why doesn't this configuration take effect

################  json data #############


{
        "y": 1111111.354243171,
        "x": 1111111.9858582001,
        "speed": [0.0, 1.0, 2.0, 4.0],
        "angle": [
            0.35431448844127544,
            0.09063859006636221,
            0.35431448844127544,
            0.09063859006636221,
            -0.08514534218357994,
            0.35431448844127544,
            -0.2609292744335221,
            0.1785305561913333
        ],
        "name": "A001",
        "left_behind_at": 1634607917.8445928,
        "odometer": [9.9,
                     9.1,
                     9.5,
                     6.6000000000000005,
                     9.9,
                     10.100000000000001,
                     9.5,
                     7.9],
        "distance": 0.0,
        "gy": 957004.5313795683,
        "current_throttle": 0.0,
        "points": [{"type": 0, "x": -27.771453857421875, "y": 7.960874557495117},
                   {"type": 0, "x": -26.048484802246094, "y": 7.958527565002441},
                   {"type": 16, "x": 14.540033340454102, "y": -25.705089569091797}
                  ],
        "time": str(datetime.datetime.utcnow().strftime("%Y-%m-%dT %H:%M%SZ"))
    }