Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

Avro to Json adding extra delemeters

avatar
Contributor

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 SOLUTIONS

avatar
Contributor

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.

View solution in original post

avatar
Super Guru

@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:

SAMSAL_0-1702321845649.png

The JsonRecorSetWriter looks like this:

SAMSAL_1-1702321891411.png

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

 

 

 

 

 

View solution in original post

12 REPLIES 12

avatar
Super Guru

@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:

SAMSAL_0-1702321845649.png

The JsonRecorSetWriter looks like this:

SAMSAL_1-1702321891411.png

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

 

 

 

 

 

avatar
Contributor

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)

avatar
Super Guru

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.