question Re: Convert JSON Attribute to Number in NiFi workflow in Archives of Support Questions (Read Only)

Convert JSON Attribute to Number in NiFi workflow

dmueller1607 — Tue, 28 Nov 2017 23:42:42 GMT

I created a NiFi Workflow that reads in data from a Database table into Avro, and later into JSON format.

However, at the moment my JSON object looks the following:

{
  "metric" : "asdpacaduPwirkL1",
  "value" : "-495.07000732421875",
  "timestamp" : "2017-05-16 12:57:39.0",
  "tags" : {
    "a" : "b",
    "c" : "d"
  }
}

This is quite good, but as a last step I have to convert to "value" attribute value into a Number (it is a String at the moment), so my final JSON should look like

{
  "metric" : "asdpacaduPwirkL1",
  "value" : -495.07000732421875,			// this should be changed into a number
  "timestamp" : "2017-05-16 12:57:39.0",
  "tags" : {
    "a" : "b",
    "c" : "d"
  }
}

Which NiFi processor can I use for that and how to convert the data type from String to Number?

Thank you for your help!

Re: Convert JSON Attribute to Number in NiFi workflow

mburgess — Wed, 29 Nov 2017 02:42:50 GMT

What version of NiFi are you using? Is the "value" column in your database table a String or a Float/Double? What processor(s) are you using to read from the database? If using ExecuteSQL, could you do something like the following?

SELECT metric, CAST(value AS DOUBLE) AS value, timestamp, tags from myTable

Alternatively, as of NiFi 1.2.0 (HDF 3.0) you can use the JoltTransformJSON processor to do type conversion (see an example here). Also if you know what the schema is supposed to be, you could use ConvertRecord with a JsonRecordSetWriter which is associated with the "correct" schema. The reader can be an AvroReader which uses the Embedded Schema.

Re: Convert JSON Attribute to Number in NiFi workflow

dmueller1607 — Wed, 29 Nov 2017 15:05:41 GMT

First of all, thank you for the fast reply!

I'm using NiFi version 1.3.0 and the original column in my table is of type Double. I'm reading out the data by a QueryDatabaseTable processor, afterwards calling a ConvertAvroToJson. To split the array into separete objects I'm using a SplitJson processor. So far, everything seems to be good. Each object looks like this:

{"ID":null,"Timestamp":"2017-05-17 17:45:55.0","Name":"asdhybridUL2N","Value":225.0}

In order to remove the "ID" attribute (is mostly null and not needed later), adding the "tags" attribute (which shall contain the fix values (a=b, c=d) as shown in the question) and changing "Name" attribute to "metric", I use a mix of UpdateAttribute, AttributesToJson and ReplaceText processors. And that's were the type of the value attribute changes to String. I also saw this JoltTransformJSON processor, but to be honest, I'm not the JSON expert and I didn't understand how to use it in my case.

Re: Convert JSON Attribute to Number in NiFi workflow

mburgess — Thu, 30 Nov 2017 02:20:11 GMT

Yeah the JOLT DSL can be confusing at times. Here's a Chain Spec that does what you describe above, so you can replace your processors with JoltTransformJSON:

[
  {
    "operation": "shift",
    "spec": {
      "Name": "metric",
      "Timestamp": "&"
    }
  },
  {
    "operation": "default",
    "spec": {
      "tags": {
        "c": "d",
        "a": "b"
      }
    }
  }
]

Re: Convert JSON Attribute to Number in NiFi workflow

dmueller1607 — Thu, 30 Nov 2017 15:17:50 GMT

If I have this template now, I'll try to change my workflow. Thank you very much for this easy-looking solution!

Re: Convert JSON Attribute to Number in NiFi workflow

wblake — Tue, 11 Feb 2020 12:07:36 GMT

Hello @mburgess ,

If you have an input string but don't know what the value might be, e.g. it could be "8" or "8.4", and want to convert this into an int. Is there a way you can convert the "8" to an 8 and the "8.4" to an 8.4 float?

Currently I am only able to convert both to ints as 8, or both to floats as 8.0, and 8.4.

For context, I am using a ValidateRecord to validate the number is an int, so would not like float values to be validated. This means that if an input is converted from a string into a number, I would like to know whether it is a decimal or integer.

Are you able to please assist?

Many thanks!