Question about using PutInfluxDatabaseRecord

[philrogers]

Hi Guys,
I am doing some nifi extraction and are trying to work out how to do something with a nested json array

I have a json array like this
{"device":"JPE19151536","interface":"Port-Channel3.2512","key":"Port-Channel3.2512","rates":{"inBitsRate":{"value":4.687787409031554},"inPktsRate":{"value":0.007918559808362655},"outBitsRate":{"value":1.1345431741251526E-119},"outPktsRate":{"value":3.972490105480225E-123},"statsUpdateTime":1.602638448008944E9},"statistics":{"inOctets":4297765,"outOctets":336472,"inErrors":0,"outErrors":0,"inDiscards":0,"outDiscards":0,"inTotalPkts":0,"inUcastPkts":51714,"outUcastPkts":1085,"inBroadcastPkts":0,"inMulticastPkts":0,"outBroadcastPkts":0,"outMulticastPkts":0,"lastUpdate":1.602638448008944E9}}

I then converted the data to an avro record with a schema ready to write to influx after schema is applied the data looks like this
in the flow file with the formatted option applied. excuse the different record data in in example from the first step

[ {
  "device" : "JPE19151536",
  "interface" : "Ethernet12.1202",
  "key" : "Ethernet12.1202",
  "rates" : {
    "inBitsRate" : {
      "value" : 0
    },
    "inPktsRate" : {
      "value" : 0
    },
    "outBitsRate" : {
      "value" : 0
    },
    "outPktsRate" : {
      "value" : 0
    },
    "statsUpdateTime" : 1.60263846E9
  },
  "statistics" : {
    "inOctets" : 0,
    "outOctets" : 0,
    "inErrors" : 0,
    "outErrors" : 0,
    "inDiscards" : 0,
    "outDiscards" : 0,
    "inTotalPkts" : 0,
    "inUcastPkts" : 0,
    "outUcastPkts" : 0,
    "inBroadcastPkts" : 0,
    "inMulticastPkts" : 0,
    "outBroadcastPkts" : 0,
    "outMulticastPkts" : 0,
    "lastUpdate" : 1.60263846E9
  }
} ]

I then want to write the statistics.inOctets and many others as fields in the PutInfluxDatabaseRecord process

if I add device,interface,key as tags in the Tags property then tags get written to influx correctly.

But when it comes to fields if I try to use anything from statistics or rates I get item not present in record . Not sure if I am referencing the fields incorrectly or its an issue referencing a sub object of the data I have tried statistics.Octets $.statistics.Octets

Avro Schema looks like this

{
  "name": "AristaPerfInterface",
  "type": "record",
  "namespace": "com.acme.avro",
  "fields": [
    {
      "name": "device",
      "type": "string"
    },
    {
      "name": "interface",
      "type": "string"
    },
    {
      "name": "key",
      "type": "string"
    },
    {
      "name": "rates",
      "type": {
        "name": "rates",
        "type": "record",
        "fields": [
          {
            "name": "inBitsRate",
            "type": {
              "name": "inBitsRate",
              "type": "record",
              "fields": [
                {
                  "name": "value",
                  "type": "int"
                }
              ]
            }
          },
          {
            "name": "inPktsRate",
            "type": {
              "name": "inPktsRate",
              "type": "record",
              "fields": [
                {
                  "name": "value",
                  "type": "int"
                }
              ]
            }
          },
          {
            "name": "outBitsRate",
            "type": {
              "name": "outBitsRate",
              "type": "record",
              "fields": [
                {
                  "name": "value",
                  "type": "int"
                }
              ]
            }
          },
          {
            "name": "outPktsRate",
            "type": {
              "name": "outPktsRate",
              "type": "record",
              "fields": [
                {
                  "name": "value",
                  "type": "int"
                }
              ]
            }
          },
          {
            "name": "statsUpdateTime",
            "type": "float"
          }
        ]
      }
    },
    {
      "name": "statistics",
      "type": {
        "name": "statistics",
        "type": "record",
        "fields": [
          {
            "name": "inOctets",
            "type": "int"
          },
          {
            "name": "outOctets",
            "type": "int"
          },
          {
            "name": "inErrors",
            "type": "int"
          },
          {
            "name": "outErrors",
            "type": "int"
          },
          {
            "name": "inDiscards",
            "type": "int"
          },
          {
            "name": "outDiscards",
            "type": "int"
          },
          {
            "name": "inTotalPkts",
            "type": "int"
          },
          {
            "name": "inUcastPkts",
            "type": "int"
          },
          {
            "name": "outUcastPkts",
            "type": "int"
          },
          {
            "name": "inBroadcastPkts",
            "type": "int"
          },
          {
            "name": "inMulticastPkts",
            "type": "int"
          },
          {
            "name": "outBroadcastPkts",
            "type": "int"
          },
          {
            "name": "outMulticastPkts",
            "type": "int"
          },
          {
            "name": "lastUpdate",
            "type": "float"
          }
        ]
      }
    }
  ]
}

Would appreciate some advice thanks
Phil

[bednar]

Hi @philrogers,

PutInfluxDatabaseRecord doesn't support nested fields.

You could flattens your JSON in JsonPathReader:

• https://github.com/influxdata/nifi-influxdb-bundle#twitterjsonreader-configuration

or use FlattenJson Processor.

Regards

[philrogers]

Thanks for the reply If I flatten with the Flattenjson processor when writing the fields I want to use as values to the influx processor are fields then referenced as $.flatten.value1,$.flatten.value2 Or do they need to be extracted as attributes first then placed in the fields property?

…

On 15 Oct 2020, at 7:08 pm, Jakub Bednář ***@***.***> wrote:  Hi @philrogers, PutInfluxDatabaseRecord doesn't support nested fields. You could flattens your JSON in JsonPathReader: https://github.com/influxdata/nifi-influxdb-bundle#twitterjsonreader-configuration or use FlattenJson Processor. Regards — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or unsubscribe.

[bednar]

How you convert data to AvroRecord? By JsonPathReader?

There is also an option to flatten an AvroRecord by ConvertAvroSchema - https://www.datajuice.io/post/nifi-flatten-avro.

[bednar]

This issue has been closed because it has not had recent activity. Please reopen if this issue is still important to you and you have additionally information.