Trouble converting JSON to AVRO in Nifi

[#1]

I'm trying to ingest JSON data and store it in AVRO format for consumption downstream. I'm using the ConvertJSONToAvro processor (1.3). The data is coming out of the processor as successful, but there is no data.

Here is the incoming JSON

{
  "type" : "record",
  "name" : "vendor",
  "fields" : [ {
    "businessentityid" : 1518,
    "accountnumber" : "INTERNAT0004",
    "name" : "International Trek Center",
    "creditrating" : 1
  }, {
    "businessentityid" : 1520,
    "accountnumber" : "G&KBI0001",
    "name" : "G & K Bicycle Corp.",
    "creditrating" : 1
  }, {
    "businessentityid" : 1546,
    "accountnumber" : "GREENLA0001",
    "name" : "Green Lake Bike Company",
    "creditrating" : 1
  }, {
    "businessentityid" : 1574,
    "accountnumber" : "JEFFSSP0001",
    "name" : "Jeff's Sporting Goods",
    "creditrating" : 1
  }, {
    "businessentityid" : 1594,
    "accountnumber" : "FITNESS0001",
    "name" : "Fitness Association",
    "creditrating" : 1
  }, {
    "businessentityid" : 1636,
    "accountnumber" : "INTEGRAT0001",
    "name" : "Integrated Sport Products",
    "creditrating" : 1
  }, {
    "businessentityid" : 1676,
    "accountnumber" : "TEAMATH0001",
    "name" : "Team Athletic Co.",
    "creditrating" : 1
  } ]
}

Here is the AVRO schema

{
		"type":"${schema.type}",
		"name":"${schema.name}",
		"fields":
		[
			{
				"name":"businessentityid",
				"type":["int","null"]
			},
			{
				"name":"accountnumber",
				"type":["string","null"]
			},
			{
				"name":"name",
				"type":["string","null"]
			},
			{
				"name":"creditrating",
				"type":["int","null"]
			}
		]
}	

Here is the JSON output, after converting it back to JSON.

{
  "businessentityid" : null,
  "accountnumber" : null,
  "name" : "vendor",
  "creditrating" : null
}

Any assistance is greatly appreciated. BTW, trying to turn up the logging to anything other than INFO in Nifi has not yielded any different logging behavior.

Thanks,

M

[#2]

The incoming JSON you showed does not match your schema...

Your schema above shows that there is a single record with the fields "businessentityid", "accountnumber", "name", "creditrating".

Your incoming JSON shows a field named "fields" with an array of JSON documents where each JSON document then has the four fields.

If you want to make a schema that matches that JSON, your schema needs to declare a field called "fields" of type array, where each entry of the array is a type record with the four fields.

[#3]

So the schema should look something like? ...

{
		"type":"record",
		"name":"vendor",
		"fields":
		[
			"name":"children",
			"type":
			{
				"type":"array",
				"items":
				{
					"name":"Child",
					"type":"record",
					"fields":
					[
						{
							"name":"businessentityid",
							"type":["int","null"]
						},
						{
							"name":"accountnumber",
							"type":["string","null"]
						},
						{
							"name":"name",
							"type":["string","null"]
						},
						{
							"name":"creditrating",
							"type":["int","null"]
						}
					]
				}
			}
		]
}	

[#4]

That looks close... where you have "name" : "children" it seems like that should be "name" : "fields" since "fields" is the array element in your JSON.

Your example JSON also has top-level fields for "type" and "name" so I would think you want to declare those in the schema, unless you just want to skip over them.

[#5]

Hmm, the following schema doesn't validate. Any ideas?

{
		"type":"record",
		"name":"vendors",
		"fields":
		[
			"name":"fields",
			"type":
			{
				"type":"array",
				"items":
				{
					"name":"vendor",
					"type":"record",
					"fields":
					[
						{
							"name":"businessentityid",
							"type":["int","null"]
						},
						{
							"name":"accountnumber",
							"type":["string","null"]
						},
						{
							"name":"name",
							"type":["string","null"]
						},
						{
							"name":"creditrating",
							"type":["int","null"]
						}
					]
				}
			}
		]
}

[#6 · Accepted solution]

Thanks for your help. Finally figured out the Nifi JSONToAvro processor can only accept 1 row at a time, versus an array of rows. The following worked for me: <Json array containing multiple rows of data> --> SplitJson --> ConvertJSONToAvro --> MergeContent ...

Thanks again for your help.

[#7]

Can u please help me...while converting json to avro format,in avro data logical type is not coming

...need to add anything in json

[#8]

@Avrotojson, as this is an older post, you would have a better chance of receiving a resolution by starting a new thread. This will also be an opportunity to provide details specific to your environment that could aid others in assisting you with a more accurate answer to your question. You can link this thread as a reference in your new post.