Unable to Upload valid JSON

1

Hello
I am trying to upload a valid JSON file into dremio but it is giving error. And I think its due to it has values in mix of array and non-array for same attribute name. As this is valid JSON I am expecting it to be uploaded seamlessly

image
image1182×244 26.4 KB 

{
	"OrganizationName": "Test Org",
	"Employee": [
		{
			"SkillAssignment": [
				{
					"Proficiency": 1,
					"Skill": "Skil1",
					"Priority": 1
				},
				{
					"Proficiency": 1,
					"Skill": "Skill2",
					"Priority": 2
				}
			]
		},
		{
			"SkillAssignment": {
				"Proficiency": 1,
				"Skill": "Skill1",
				"Priority": 1
			}
		}
	]
}
2

Is there a way to resolve this issue?

@balaji.ramaswamy you seems to be replying most of the posts. So can you shed some light on this issue please?

3

@mohsin.mansur This error means that Dremio is unable to coerce struct and list. Is it possible to make both lists or both as structs?

4

Thanks for reply @balaji.ramaswamy

We are getting this JSON from external system. The size of JSON files are big, so if we re-process (to change you per your suggestion) at our end it then it will be more processing and time consuming.

As this is valid file, it is unreasonable to ask external system to change it.

Do you not think that dremio should be able to handle this, as this is valid JSON file?

5

@balaji.ramaswamy, is there any method to programmatically give dremio instructions for the coercion we desire on specific fields?

6

@adamr

Currently this coercion is not supported, there is an internal schema feature for only certain sources where you can define the incoming schema using alter table

What is the right data type that should be defined? List (Array) or Struct?

7

@balaji.ramaswamy

In this case it would be List (Array)

What sources is the internal schema feature available for?

8

@adamr JSON should be supported, can you try changing the data type to LIST? If the incoming file after that has STRUCT, you may run into the same issue, please check and let us know