How can i parse string nested json with Nifi?

[#1]

Hello,

I have a JSON data in the ClickHouse database with a type of string. The JSON data looks like this:

[{ "Name": "Gta V" "Type": "Xyz" "content": "{"Game":{"Subject":[{"Time":{"@value":"201511021057"}}]}}" }]

The output I get using Nifida ExecuteSqlRecord is like this. I can remove the \ character using the ReplaceText process. The output looks like this: 

[{ "Name": "Gta V" "Type": "Xyz" "content": "{"Game":{"Subject":[{"Time":{"@value":"201511021057"}}]}}" }]

However, the value part for the content key is still a string. How can I make its type as JSON?

The desired output is like this:

[{ "Name": "Gta V" "Type": "Xyz" "content": {"Game":{"Subject":[{"Time":{"@value":"201511021057"}}]}} }]

[#2]

HI @mstfo,

Take the output to UpdateRecord processor, and cast the desire field to any specific data type example

formula: ${field.value:ToInteger()}

[#3]

I am currently using the 'executesqlrecord -> replacetext' (for unescapeJson) process. How should I use the 'updateRecord' processor here? Could you explain a bit more ?

[#4]

Are you fetching and processing the records as it is ? or defining the json path (flatting the JSON) ?

[{ "Name": "Gta V" "Type": "Xyz" "content": "{"Game":{"Subject":[{"Time":{"@value":"201511021057"}}]}}" }]

[#5]

The content field in the source table is in string format. When I receive the data from the source, it comes like this:

[{ "Name": "Gta V" "Type": "Xyz" "content": "{"Game":{"Subject":[{"Time":{"@value":"201511021057" }}]}}" }]

After I fetch this data with NiFi, I need to use a Jolt transformation on the content field, but since the content field is in string format, I can't perform the transformation. My actual problem is to eliminate the string expression in the content field and convert it to the following format so that I can use Jolt for the content field:

[{ "Name": "Gta V" "Type": "Xyz" "content": {"Game":{"Subject":[{"Time":{"@value":"201511021057"}}]}} }]

[#6]

Can you send the JOLT just to understand what you are exactly extracting?

for example if you are extracting @value  then something like "@value": "=toInteger" in JOLT can be written for typeconversion.

I belive type conversion can not be done in list.

[#7]

"This is the output I obtained with executesqlrecord: [{ "Name": "Gta V" "Type": "Xyz" "content": "{"Game":{"Subject":[{"Time":{"@value":"201511021057" }}]}}" }] After executesqlrecord, I use ${'$1':unescapeJson()} in the replacetext processor to remove the backslash characters. Then my output looks like this: [{ "Name": "Gta V" "Type": "Xyz" "content": "{"Game":{"Subject":[{"Time":{"@value":"201511021057" }}]}}" }]

What I want is, after removing the backslashes, the nested JSON data in the content field should not come within double quotes. I want to learn the ways to remove it after ReplaceText. So, ultimately, the desired output is:

[{ "Name": "Gta V" "Type": "Xyz" "content": {"Game":{"Subject":[{"Time":{"@value":"201511021057"}}]}} }]