Avro to Json adding extra delemeters

[#1]

I am using ExecuteSQL to pull a colb column from DB. ExecuteSQL provides the result in the avro format. Then I am converting that to Json using AvroToJsonProcessor but it is adding escape characters. 
I want the value that is being pulled from DB as a Json but I am getting it as a string with escape characters. 

[

    {

        "SEARCH_RESULT": "{ \"requestId\": \"203680\", \"responseData\": [ { \"searchKey\": \"cardNumber\", \"data\": [ { \"firstName\": \"Martin\", \"lastName\": \"Garry\" }, { \"firstName\": \"Martin\", \"lastName\": \"Garry\"}, { \"firstName\": \"Martin\", \"lastName\": \"Garry\",  } ] } ] }"

    }

]

I want that value of Search result to be a Json instead of a String so that I can use JOLT to transform it as needed. 
Can you someone pls suggest how to do it.

[#2 · Accepted solution]

one thing that worked for me use combination of EvaluateJsonPath -> UpdateAttribute -> ReplaceText
I have added the entire json to attribute and in UpdateAttribute I have used "${searchValue:unescapeJson():replace('"{','{'):replace('"}','}')}" which does the trick and then used ReplaceText to replace the Entire Json content.

[#3 · Accepted solution]

@Anderosn,

Another option is to use the UpdateRecod with an Avro schema in the JsonRecordSetWriter that reflects the actual json structure coming out of the ExecuteSQL

The UpdateRecord will look like this:

The JsonRecorSetWriter looks like this:

The AvroSchema provided in the ShemaText property is the following:

{
	"name": "nifi",
	"type": "record",
	"namespace": "nifi.com",
	"fields": [
		{
			"name": "SEARCH_RESULT",
			"type": {
				"name": "SEARCH_RESULT",
				"type": "record",
				"fields": [
					{
						"name": "requestId",
						"type": "string"
					},
					{
						"name": "responseData",
						"type": {
							"name": "responseData",
							"type": "array",
							"items": {
								"name": "responseData",
								"type": "record",
								"fields": [
									{
										"name": "searchKey",
										"type": "string"
									},
									{
										"name": "data",
										"type": {
											"name": "data",
											"type": "array",
											"items": {
												"name": "data",
												"type": "record",
												"fields": [
													{
														"name": "firstName",
														"type": "string"
													},
													{
														"name": "lastName",
														"type": "string"
													}
												]
											}
										}
									}
								]
							}
						}
					}
				]
			}
		}
	]
}

This will produce the following output json out of the UpdateRecord:

[ {
  "SEARCH_RESULT" : {
    "requestId" : "203680",
    "responseData" : [ {
      "searchKey" : "cardNumber",
      "data" : [ {
        "firstName" : "Martin",
        "lastName" : "Garry"
      }, {
        "firstName" : "Martin",
        "lastName" : "Garry"
      }, {
        "firstName" : "Martin",
        "lastName" : "Garry"
      } ]
    } ]
  }
} ]

You can use EvaluateJsonPath to get the data as json array to do the needed processing.

If that helps please accept solution.

Thanks

[#4]

Hi @SAMSAL 
when I try in my local with the schema text that you provided.
I am seeing this error:

UpdateRecord[id=5e779eb4-018c-1000-b9ea-047535bdcfb5] Failed to write MapRecord[{SEARCH_RESULT=unescapeJson(/SEARCH_RESULT, 'true')}] with reader schema ["SEARCH_RESULT" : "STRING"] and writer schema {"type":"record","name":"nifi","namespace":"nifi.com","fields":[{"name":"SEARCH_RESULT","type":{"type":"record","name":"SEARCH_RESULT","fields":[{"name":"requestId","type":"string"},{"name":"responseData","type":{"type":"array","items":{"type":"record","name":"responseData","fields":[{"name":"searchKey","type":"string"},{"name":"data","type":{"type":"array","items":{"type":"record","name":"data","fields":[{"name":"firstName","type":"string"},{"name":"lastName","type":"string"}]}}}]}}}]}}]} as a JSON Object due to org.apache.nifi.serialization.record.util.IllegalTypeConversionException: Cannot convert value [unescapeJson(/SEARCH_RESULT, 'true')] of type class java.lang.String to Record for field SEARCH_RESULT

org.apache.nifi.serialization.record.util.IllegalTypeConversionException: Cannot convert value [unescapeJson(/SEARCH_RESULT, 'true')] of type class java.lang.String to Record for field SEARCH_RESULT

    at
Input that I am using is: [{
"SEARCH_RESULT": "{ \"requestId\": \"203680\", \"responseData\": [ { \"searchKey\": \"cardNumber\", \"data\": [ { \"firstName\": \"Martin\", \"lastName\": \"Garry\" }, { \"firstName\": \"Martin\", \"lastName\": \"Garry\"}, { \"firstName\": \"Martin\", \"lastName\": \"Garry\", } ] } ] }"
}]
also tried with 
{
"SEARCH_RESULT": "{ \"requestId\": \"203680\", \"responseData\": [ { \"searchKey\": \"cardNumber\", \"data\": [ { \"firstName\": \"Martin\", \"lastName\": \"Garry\" }, { \"firstName\": \"Martin\", \"lastName\": \"Garry\"}, { \"firstName\": \"Martin\", \"lastName\": \"Garry\", } ] } ] }"
}
org.apache.nifi.serialization.record.util.DataTypeUtils.toRecord(DataTypeUtils.java:444)

[#5]

Can you post screenshot of the UpdateRecord processor configuration? Also you have to be careful with the provided input because there is an extra comma after last Garry value which makes the json invalid.