Help request: Json parsing

Telegraf
, ,
1

Dear community,

I am a bit desperated trying to parse a json payload comming in a MQTT message to be stored in influxdb. The payload is following OPC UA PubSub Part14 specification so I think will be helpful for a lot of people. An example of the payload is the following:

{"MessageId":"936107","MessageType":"ua-data","PublisherId":"ua-test-fronius","Messages":[{"DataSetWriterId":28715,"Timestamp":"2023-11-29T22:22:13.323661Z","Payload":{"ConnectedToBroker":{"Value":true}}},{"DataSetWriterId":7076,"Timestamp":"2023-11-29T22:22:13.3237068Z","Payload":{"ConnectedToCloudStorage":{"Value":false}}},{"DataSetWriterId":36507,"Timestamp":"2023-11-29T22:22:13.3237082Z","Payload":{"NumOpcSessions":{"Value":0}}},{"DataSetWriterId":62162,"Timestamp":"2023-11-29T22:22:13.3237088Z","Payload":{"NumOpcSubscriptions":{"Value":1}}},{"DataSetWriterId":60075,"Timestamp":"2023-11-29T22:22:13.3237094Z","Payload":{"NumOpcMonitoredItems":{"Value":74}}},{"DataSetWriterId":58227,"Timestamp":"2023-11-29T22:22:13.3237099Z","Payload":{"QueueCapacity":{"Value":1000}}},{"DataSetWriterId":20362,"Timestamp":"2023-11-29T22:22:13.3237105Z","Payload":{"QueueCount":{"Value":"6"}}},{"DataSetWriterId":11913,"Timestamp":"2023-11-29T22:22:13.3237112Z","Payload":{"EnqueueFailures":{"Value":"0"}}},{"DataSetWriterId":37064,"Timestamp":"2023-11-29T22:22:13.3237932Z","Payload":{"SentMessages":{"Value":"935317"}}},{"DataSetWriterId":45217,"Timestamp":"2023-11-29T22:22:13.3237938Z","Payload":{"BrokerMessageSize":{"Value":"455"}}},{"DataSetWriterId":58512,"Timestamp":"2023-11-29T22:22:13.3237944Z","Payload":{"BrokerMessageLatency":{"Value":"0"}}},{"DataSetWriterId":20143,"Timestamp":"2023-11-29T22:22:13.3237953Z","Payload":{"BrokerMessagesSecond":{"Value":0}}},{"DataSetWriterId":62951,"Timestamp":"2023-11-29T22:22:13.3237958Z","Payload":{"NumOpcMonitoredItemsSecond":{"Value":0}}},{"DataSetWriterId":10713,"Timestamp":"2023-11-29T22:22:13.3237963Z","Payload":{"BrokerMessageSendFailures":{"Value":"0"}}},{"DataSetWriterId":41506,"Timestamp":"2023-11-29T22:22:13.3244247Z","Payload":{"CurrentWorkingSetMBs":{"Value":"260"}}}]}

An the expected output, for example for the last line would be:

CurrentWorkingSetMBs,DataSetWriterId=41506,PublisherId="ua-test-fronius",MessageType="ua-data" Value=260 (converted timestamp "2023-11-29T22:22:13.3244247Z").

Could someone help me with the json or json_v2 parsing? I read all the examples but none of them reflects a similar structure.

Thank you very much in advance,

2

Hello,

Please, could someone help me to parse the payload? I tried several methods but I am not able to find a solution when there is no key pair inside the Payload…

THANKS in advance

3

Hi , to increase the odds of someone helping you, you could “pretty print” the json to eliminate the left-right scrolling which is really really hard for humans to process.

You can do this with python out of the box

python -mjson.tool < yourjsonfile.txt

Edit your original post or reply with the formatted version in a </> pre-formatted text block

4

You are fully right:

{
  "MessageId": "936107",
  "MessageType": "ua-data",
  "PublisherId": "ua-test-fronius",
  "Messages": [
    {
      "DataSetWriterId": 28715,
      "Timestamp": "2023-11-29T22:22:13.323661Z",
      "Payload": {
        "ConnectedToBroker": {
          "Value": true
        }
      }
    },
    {
      "DataSetWriterId": 7076,
      "Timestamp": "2023-11-29T22:22:13.3237068Z",
      "Payload": {
        "ConnectedToCloudStorage": {
          "Value": false
        }
      }
    },
    {
      "DataSetWriterId": 36507,
      "Timestamp": "2023-11-29T22:22:13.3237082Z",
      "Payload": {
        "NumOpcSessions": {
          "Value": 0
        }
      }
    },
    {
      "DataSetWriterId": 62162,
      "Timestamp": "2023-11-29T22:22:13.3237088Z",
      "Payload": {
        "NumOpcSubscriptions": {
          "Value": 1
        }
      }
    },
    {
      "DataSetWriterId": 60075,
      "Timestamp": "2023-11-29T22:22:13.3237094Z",
      "Payload": {
        "NumOpcMonitoredItems": {
          "Value": 74
        }
      }
    },
    {
      "DataSetWriterId": 58227,
      "Timestamp": "2023-11-29T22:22:13.3237099Z",
      "Payload": {
        "QueueCapacity": {
          "Value": 1000
        }
      }
    },
    {
      "DataSetWriterId": 20362,
      "Timestamp": "2023-11-29T22:22:13.3237105Z",
      "Payload": {
        "QueueCount": {
          "Value": "6"
        }
      }
    },
    {
      "DataSetWriterId": 11913,
      "Timestamp": "2023-11-29T22:22:13.3237112Z",
      "Payload": {
        "EnqueueFailures": {
          "Value": "0"
        }
      }
    },
    {
      "DataSetWriterId": 37064,
      "Timestamp": "2023-11-29T22:22:13.3237932Z",
      "Payload": {
        "SentMessages": {
          "Value": "935317"
        }
      }
    },
    {
      "DataSetWriterId": 45217,
      "Timestamp": "2023-11-29T22:22:13.3237938Z",
      "Payload": {
        "BrokerMessageSize": {
          "Value": "455"
        }
      }
    },
    {
      "DataSetWriterId": 58512,
      "Timestamp": "2023-11-29T22:22:13.3237944Z",
      "Payload": {
        "BrokerMessageLatency": {
          "Value": "0"
        }
      }
    },
    {
      "DataSetWriterId": 20143,
      "Timestamp": "2023-11-29T22:22:13.3237953Z",
      "Payload": {
        "BrokerMessagesSecond": {
          "Value": 0
        }
      }
    },
    {
      "DataSetWriterId": 62951,
      "Timestamp": "2023-11-29T22:22:13.3237958Z",
      "Payload": {
        "NumOpcMonitoredItemsSecond": {
          "Value": 0
        }
      }
    },
    {
      "DataSetWriterId": 10713,
      "Timestamp": "2023-11-29T22:22:13.3237963Z",
      "Payload": {
        "BrokerMessageSendFailures": {
          "Value": "0"
        }
      }
    },
    {
      "DataSetWriterId": 41506,
      "Timestamp": "2023-11-29T22:22:13.3244247Z",
      "Payload": {
        "CurrentWorkingSetMBs": {
          "Value": "260"
        }
      }
    }
  ]
}

Better to read.

Thanks for the advice.

5

In addition to what FixTestRepeat mentioned, showing your work would help. This is not the first time you have asked the community to figure out the parsing for you. In fact, I took the Xpath config you were given last time and applied it to the new data:

[[inputs.file]]
  files = ["metrics.json"]
  data_format = "xpath_json"

  xpath_allow_empty_selection = true
  xpath_native_types = true

  [[inputs.file.xpath]]
    metric_name = "'CurrentWorkingSetMBs'"
    metric_selection = "//Messages/*"
    timestamp = "Timestamp"
    timestamp_format = "2006-01-02T15:04:05.9999999Z"
    field_selection = "/Payload/*/Value"

    [inputs.file.xpath.tags]
      MessageType   = "/MessageType"
      PublisherId = "/PublisherId"
      DataSetWriterId = "DataSetWriterId"

CurrentWorkingSetMBs,DataSetWriterId=28715,MessageType=ua-data,PublisherId=ua-test-fronius Value=true 1701296533323661000
CurrentWorkingSetMBs,DataSetWriterId=7076,MessageType=ua-data,PublisherId=ua-test-fronius Value=false 1701296533323706800
CurrentWorkingSetMBs,DataSetWriterId=36507,MessageType=ua-data,PublisherId=ua-test-fronius Value=0 1701296533323708200
CurrentWorkingSetMBs,DataSetWriterId=62162,MessageType=ua-data,PublisherId=ua-test-fronius Value=1 1701296533323708800
CurrentWorkingSetMBs,DataSetWriterId=60075,MessageType=ua-data,PublisherId=ua-test-fronius Value=74 1701296533323709400
CurrentWorkingSetMBs,DataSetWriterId=58227,MessageType=ua-data,PublisherId=ua-test-fronius Value=1000 1701296533323709900
CurrentWorkingSetMBs,DataSetWriterId=20362,MessageType=ua-data,PublisherId=ua-test-fronius Value="6" 1701296533323710500
CurrentWorkingSetMBs,DataSetWriterId=11913,MessageType=ua-data,PublisherId=ua-test-fronius Value="0" 1701296533323711200
CurrentWorkingSetMBs,DataSetWriterId=37064,MessageType=ua-data,PublisherId=ua-test-fronius Value="935317" 1701296533323793200
CurrentWorkingSetMBs,DataSetWriterId=45217,MessageType=ua-data,PublisherId=ua-test-fronius Value="455" 1701296533323793800
CurrentWorkingSetMBs,DataSetWriterId=58512,MessageType=ua-data,PublisherId=ua-test-fronius Value="0" 1701296533323794400
CurrentWorkingSetMBs,DataSetWriterId=20143,MessageType=ua-data,PublisherId=ua-test-fronius Value=0 1701296533323795300
CurrentWorkingSetMBs,DataSetWriterId=62951,MessageType=ua-data,PublisherId=ua-test-fronius Value=0 1701296533323795800
CurrentWorkingSetMBs,DataSetWriterId=10713,MessageType=ua-data,PublisherId=ua-test-fronius Value="0" 1701296533323796300
CurrentWorkingSetMBs,DataSetWriterId=41506,MessageType=ua-data,PublisherId=ua-test-fronius Value="260" 1701296533324424700
6

Thank you very much…sorry for bother you but json_xpath is not well documented at all. In your example you have hardcoded the name of the metric, nevertheless, as you see in my example, the name is changing…how could I retrieve this name when it is not a objetc in the json?

Thanks in advance

7

This is when you take a step back and look at the config that was provided to you. How are the other fields dynamically set? Can you apply the same thing to the metric_name field?

In short you need to provide the path to the value you want (e.g. /Payload/*), but you want the value itself, and wrap it e.g. name(/Payload/*)`. Both of these are laid out in the xpath docs.

In the future, please provide what you actually tried when asking for assistance.

1 Like
8

Thanks. Now I better understand xpath and I see the power of the parser…Thanks @jpowers