complex json schema not pulling data in dataflow datasource

Question

complex json schema not pulling data in dataflow datasource

arkiboys 9,706

Hello,
I have several complex files... (I closed previous question as I have summarized it all here for simlicity) thank you
Please see attached screenshots. I am unable to pull through the quantities fields from the json file.
Note that the json file is document per line.

In-case it helps, I placed one of the json files (documents per line) into an online json reader and it shows it here, where you can see the quantities are being pulled nicely.

Any suggestions please?
Thank you

from an online json reader site

SAMPLE json

------------------

--File Example 1

-----------------

{"deliveryVenture":"dgdgdg","book":"kjkhjkh","movements":[{"name":"etete","state":"completed","buildDraw":"jhj","planningNumber":"5656fgf","company":"dfgdfgf","movementDate":"2020-02-06","brand":"dfsdf","grade":"jhgj","incoterms":"jhg","quantities":[{"planned":"567","uom":"hj","actual":"456","net":"980","gross":"343"},{"planned":"5675","uom":"tyt","actual":"45","net":"33","gross":"23"}],"locations":[{"name":"source","code":"hjkhjkh","type":"ewrwrw","documentationName":"klkl"},{"name":"destination","code":"jhghjg","type":"ert","documentationName":"kjhk"}],
...
...

--File Example 2

----------------

{"deliveryVenture":"fghfghfh","book":"kjhjk","movements":[{"name":"ljkllhj","state":"completed","buildDraw":"lklk","planningNumber":"98789","company":"jhgjgh","movementDate":"2020-02-06","brand":"jhhhhh","grade":"wewe","incoterms":"FCA","quantities":[{"planned":"76.95","uom":"jj"},{"actual":"23.78","uom":"fff"},{"net":"35.08","uom":"qq"},{"gross":"876.00","uom":"we"}],"locations":[{"name":"source","code":"yutyutu","type":"qwq","documentationName":"ggg"},{"name":"destination","code":"wewewe","type":"Refinery","documentationName":"lll"}],"o
...
...

Nasreen Akter 10,811 Reputation points Volunteer Moderator

2022-03-18T13:01:01.427+00:00

@arkiboys , Thank you for putting all the details here. Would you also please provide a sample JSON file. Thanks!
arkiboys 9,706 Reputation points

2022-03-18T13:12:19.467+00:00

Just added two samples. thank you

Accepted answer

0 additional answers

Your answer

Nasreen Akter 10,811 Reputation points Volunteer Moderator

2022-03-18T13:01:01.427+00:00

@arkiboys , Thank you for putting all the details here. Would you also please provide a sample JSON file. Thanks!
arkiboys 9,706 Reputation points

2022-03-18T13:12:19.467+00:00

Just added two samples. thank you

Answer 1

Nasreen Akter 10,811 Volunteer Moderator

Hi @arkiboys ,

please create a json file with the following: {
"deliveryVenture": "dgdgdg",
"book": "kjkhjkh",
"movements": [{
"name": "etete",
"state": "completed",
"buildDraw": "jhj",
"planningNumber": "5656fgf",
"company": "dfgdfgf",
"movementDate": "2020-02-06",
"brand": "dfsdf",
"grade": "jhgj",
"incoterms": "jhg",
"quantities": [{
"planned": "567",
"uom": "hj",
"actual": "456",
"net": "980",
"gross": "343"
}, {
"planned": "5675",
"uom": "tyt",
"actual": "45",
"net": "33",
"gross": "23"
}],
"locations": [{
"name": "source",
"code": "hjkhjkh",
"type": "ewrwrw",
"documentationName": "klkl"
}, {
"name": "destination",
"code": "jhghjg",
"type": "ert",
"documentationName": "kjhk"
}]
}]
}
from the Dataset, locate the JSON file and then import the schema from the connection/store
as the sample document only contains one json string, so in the dataflow, change setting to Single Document
now if you go to the data DataFlow--> source--> data preview, you should able to see the array list for quantities

Thanks!

arkiboys 9,706 Reputation points

2022-03-18T14:51:23.587+00:00

thank you for doing this but please note that my .json files are all in line by line format, i.e. one document per line. Similar to the format I sent to you as simplified examples.
For my .json files, I will have to set the dataflow source setting to be line per document rather than single otherwise it will error as :
Malformed records are detected in schema inference. Parse Mode: FAILFAST

and so I still have the same issue
Nasreen Akter 10,811 Reputation points Volunteer Moderator

2022-03-18T15:06:23.343+00:00

That is fine @arkiboys . Document form option depends on the JSON file format. You can select Document per line. It would be fine. :)
arkiboys 9,706 Reputation points

2022-03-18T15:31:50.393+00:00

I get the error if I choose document per line while using my .json files (As sent previously the samples), I get this error.
And so I am still having the same issue where I do not get the quantities coming through
arkiboys 9,706 Reputation points

2022-03-18T16:04:27.843+00:00

Hello nasreen. In addition to the error message I sent to you. Have you tried loading both sample files I sent you while you have imported one of the file schemas only? because both files have different quantity schemas.
Thanks
Nasreen Akter 10,811 Reputation points Volunteer Moderator

2022-03-18T16:21:14.993+00:00

Hi @arkiboys , please try with a small valid json file. From the error, it seems like the JSON string has problem.

For the other sample, that should work too, as I loaded similar file before. :)
arkiboys 9,706 Reputation points

2022-03-18T16:23:47.657+00:00

To confirm, do you want me to do the following?
1- create a simplified files. one for each of the examples I sent you. Both have different quantity hierarchies.
Then which one of them should have the schema in dataset?

Thanks
Nasreen Akter 10,811 Reputation points Volunteer Moderator

2022-03-18T16:28:17.173+00:00

Hi @arkiboys , In both cases, Dataset should have complete schema e.g., all the attributes planned, uom, actual, net, gross should be there. Thanks!
arkiboys 9,706 Reputation points

2022-03-18T16:30:42.16+00:00

This is what I have done:
1-
I created one proper .json file with the hierarchies of the quantities in my example 1 I sent you previously. i.e.
...
"quantities":[{"planned":"2.695","uom":"MT","actual":"10.78","net":"35.08","gross":"35.08"},{"planned":"5675","uom":"tyt","actual":"45","net":"33","gross":"23"}],
...

2-
created a dataset from this very file and imported the schema in dataset
3-
dataflow datasource pointing to this dataset
4-
set document form to Document per line
5-
imported projection which shows the quantities hierarchy
6-
data preview does show the quantities data. --> all good so far
7-
I then changed the dataset to point to my other .json file and this time I did NOT import schema. Then the dataflow datapreview does NOT show quantities from the new file which has different quantity schemas

What am I doing wrong? do you see?
Thanks
arkiboys 9,706 Reputation points

2022-03-18T17:42:24.197+00:00

@Nasreen Akter , is it ok if I send you a sample file direct to your email address?
and see if you can load those and see the data in quantities?
It looks like schema import is not solving the issue for other incoming files.
Thanks
Nasreen Akter 10,811 Reputation points Volunteer Moderator

2022-03-18T17:45:34.147+00:00

@arkiboys : please upload the files here as .txt. Thanks!
arkiboys 9,706 Reputation points

2022-03-18T21:59:39.437+00:00

Hi Nasreen.
Thank you for this and I will look into this.
I have downloaded the corrected files you sent.
Please kindly, delete the two corrected files and kindly remove the sample code text you pasted.
Many thanks.

I will do some investigation and come back to you.
arkiboys 9,706 Reputation points

2022-03-18T22:51:12.543+00:00

Nasreen, what is the solution in this case?
As you mentioned the quantities are not consistent in the file.
I receive the files in ADF using rest api to call a url and download these files.
Is there a way to pull the quantities data as I can not change every files that I receive to have similar arrays.
.
Thank you
Nasreen Akter 10,811 Reputation points Volunteer Moderator

2022-03-18T22:54:49.603+00:00

@arkiboys , I think, as this is an error on API side, they should correct the schema + data at their end.
arkiboys 9,706 Reputation points

2022-03-19T16:47:12.833+00:00

@Nasreen Akter many thanks for your help
Nasreen Akter 10,811 Reputation points Volunteer Moderator

2022-03-19T18:20:16.103+00:00

:) happy to help @arkiboys
arkiboys 9,706 Reputation points

2022-03-24T09:08:46.21+00:00

Hi,
But do you think it is really the error on the api side?
I spoke to the api team and they say because there is only one item tehn there is no [ and ]
If there are more than one items then there will be [ and ]
At present to be able to flatten the data I have to do a search and replace in ADF to put [ and ] for the single items.
Is there no other way in ADF to solve this?

Thanks

Share via

complex json schema not pulling data in dataflow datasource

0 additional answers

Your answer