Removing Special Characters from JSON

[#1]

Hi,

I have a streaming use case where I'm ingesting JSON data via an MQ. I am trying to pull out some key-value pairs from a JSON to be sent to a CEP for windowing functions. The issue is that the JSON file is storing the key-value pairs in a nested JSON map with special characters embedded in it.

Below are the details regarding the steps I'm trying to take. Any suggestions on how to achieve my goal would be greatly appreciated.

Current JSON:

"Message": "[{\"Key\":\"Key1\",\"ResponseTime\":\"54\"},{\"Key\":\"Key2\",\"ResponseTime\":\"2186\"},{\"Key\":\"Key3\",\"ResponseTime\":\"2242\"}]",

{
	"IncludeExclude": true,
	"Description": "ResponseTimes",
	"TimeStamp": "2016-07-02T18:59:59.6162528-05:00",
	"Sequence": 0,
	"Loglevel": 0,
	"$type": "Information",
	"OperationName": "BeforeSendReply",
	"StateInfos": null,
	"FileName": "CSS.cs",
	"ClassName": null,
	"RequestUri": "https://ILoveHadoop.com",
	"AssemblyInfo": null,
	"LineNumber": "170",
	"TimeZone": null,
	"Message": "[{\"Key\":\"Key1\",\"ResponseTime\":\"54\"},{\"Key\":\"Key2\",\"ResponseTime\":\"2186\"},{\"Key\":\"Key3\",\"ResponseTime\":\"2242\"}]",
	"EventInfo": {
		"EventLevel": null,
		"$type": "Event123",
		"EventSuccess": null,
		"EventType": "Information"
	}
}

Trying to remove special characters so the JSON looks like this:

"Message": [{"Key":"Key1","ResponseTime":"54"},{"Key":"Key2","ResponseTime":"2186"},{"Key":"Key3","ResponseTime":"2242"}],

{
	"IncludeExclude": true,
	"Description": "ResponseTimes",
	"TimeStamp": "2016-07-02T18:59:59.6162528-05:00",
	"Sequence": 0,
	"Loglevel": 0,
	"$type": "Information",
	"OperationName": "BeforeSendReply",
	"StateInfos": null,
	"FileName": "CSS.cs",
	"ClassName": null,
	"RequestUri": "https://ILoveHadoop.com",
	"AssemblyInfo": null,
	"LineNumber": "170",
	"TimeZone": null,
	"Message": [{"Key":"Key1","ResponseTime":"54"},{"Key":"Key2","ResponseTime":"2186"},{"Key":"Key3","ResponseTime":"2242"}],
	"EventInfo": {
		"EventLevel": null,
		"$type": "Event123",
		"EventSuccess": null,
		"EventType": "Information"
	}
}

Then I plan to run the below JOLT shift via the JOLT Processor in NiFi to transpose the map to a list:

\\Output for transposing message data
[
  {
    "operation": "shift",
    "spec": {
      "Message": {
        "*": {
          "@ResponseTime": "ApplicationResponseTimes.@Key"
        }
      }
    }
  }
]

With an ultimate end output of:

{
  "ApplicationResponseTimes" : {
    "Key1" : "54",
    "Key3" : "2242",
    "Key2" : "2186"
  }
}

Thanks,

Andrew

[#2 · Accepted solution]

Hey Andrew,

If your message is a valid JSON (it seems to be), then a parser is able to read it an decode a literal string. The next trick is to put this results into a FlowFile content/body and run another EvaluateJsonPath/JoltTransformer chain. I did a quick experiment here, and it seems to work fine.