Convert JSON Attribute to Number in NiFi workflow

[#1]

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!

[#2 · Accepted solution]

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.

[#3]

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.

[#4]

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"
      }
    }
  }
]

[#5]

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

[#6]

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!