read json file with arrays

Question

read json file with arrays

arkiboys 9,706

Hello,
The dataflow in the data factory pipeline has been reading a complex json file. All is fine...But recently the new requirement is to read extra information form the same json file we receive daily.
The new array to read is within an existing array...
Obviously there is an existing adtaset which has been reading the json file when the dataflow source is running.
Now to get the new array in the json file, I refresh the schema in the dataset to pull the schema with the new array. All good and the dataset reads all the data including the new array in the file...
But in the source of the dataflow (Which always has had the json setting to Document per line), I import projection but when reading the json file, it reads everything except the new array which shows the names but not values of it.
What can I do to solve this and to be able to see the values of this array in the file?

Thank you

QuantumCache 20,366 Reputation points Moderator

2023-07-12T21:58:45.7533333+00:00

Hello @arkiboys

Yes, you are right, good observation on the cached issue!

I have seen the same behavior during the development phase from the Data Factory Studio.

This will not be an issue during runtime after we deploy the Pipeline!

Scenario:

If the Source file has some updated content , the data preview will not load the fresh data.

Workaround/a little ToDo thing :

One of the ways to tackle this is to refresh the dataset with Preview option or importing the Source Schemas once again, but even that as well not helped me.

I personally face this issue, so I always makes sure to update the file name and link the new file name to read the data and do the source data preview.

Just change the file name and save , which will then help with the Preview of correct data.
arkiboys 9,706 Reputation points

2023-07-13T05:18:47.07+00:00

hello,
I tried your suggestion...
my json file is dynamically built daily with dynamic name and so I always leave the file name blank.
so, dataset always does show the data, so dataset is fine.
the datapreview of the source which points to the dataset, does not show one of the arrays values but it does show the values.
So then I tried your suggestion as follows:
I created a simple version of the json file and named it xyz_simplified.json and placed the name of this file in the dataset as you have in your example. Obviouly the dataset shows all the data ok. I then saved the dataset.

Then I tried the datapreview of the source in the dataflow, the usual json setting which is always set to "Document per line", gave error: ...failfast...format... so then I changed it to "Array of documents", it worked and showed the new array values in datapreview.
Then I went back to the dataset and removed the filename from it as the dataset picks up whatever json file is present in that location. Then set the source json setting in dataflow to "Document per line" as usual and again the arrays are not showing the values. Note that all other arrays are fine except this one.
Any thoughts,

thank you
arkiboys 9,706 Reputation points

2023-07-14T06:42:34.5766667+00:00

hi, your test suggestion worked where I edit the original json file and place the new name into the dataset...Note, when I modify the json it becomes a json document whereas when I have the original .json files they are of type: UTF-8 with BOM

for the modified .json file, I have to use the json setting of array of Documents in source dataflow.

for the original .json files, I have to use the json setting as Document per line
So the issue still remains because I need ot get the original files to work...Remember that the original json file is happily allowing the source dataflow to read the contents except that very array in question.
hope this helps

Thank you
QuantumCache 20,366 Reputation points Moderator

2023-07-17T19:13:53.58+00:00

Hello @arkiboys,

Could you please provide me a sample of the Source JSON files with Array? I would like to test the scenario from my side!
QuantumCache 20,366 Reputation points Moderator

2023-08-01T01:14:17.5033333+00:00

Hello @arkiboys, Did you find any resolution on this scenario?

Accepted answer

0 additional answers

Your answer

QuantumCache 20,366 Reputation points Moderator

2023-07-12T21:58:45.7533333+00:00

Hello @arkiboys

Yes, you are right, good observation on the cached issue!

I have seen the same behavior during the development phase from the Data Factory Studio.

This will not be an issue during runtime after we deploy the Pipeline!

Scenario:

If the Source file has some updated content , the data preview will not load the fresh data.

Workaround/a little ToDo thing :

One of the ways to tackle this is to refresh the dataset with Preview option or importing the Source Schemas once again, but even that as well not helped me.

I personally face this issue, so I always makes sure to update the file name and link the new file name to read the data and do the source data preview.

Just change the file name and save , which will then help with the Preview of correct data.
arkiboys 9,706 Reputation points

2023-07-13T05:18:47.07+00:00

hello,
I tried your suggestion...
my json file is dynamically built daily with dynamic name and so I always leave the file name blank.
so, dataset always does show the data, so dataset is fine.
the datapreview of the source which points to the dataset, does not show one of the arrays values but it does show the values.
So then I tried your suggestion as follows:
I created a simple version of the json file and named it xyz_simplified.json and placed the name of this file in the dataset as you have in your example. Obviouly the dataset shows all the data ok. I then saved the dataset.

Then I tried the datapreview of the source in the dataflow, the usual json setting which is always set to "Document per line", gave error: ...failfast...format... so then I changed it to "Array of documents", it worked and showed the new array values in datapreview.
Then I went back to the dataset and removed the filename from it as the dataset picks up whatever json file is present in that location. Then set the source json setting in dataflow to "Document per line" as usual and again the arrays are not showing the values. Note that all other arrays are fine except this one.
Any thoughts,

thank you
arkiboys 9,706 Reputation points

2023-07-14T06:42:34.5766667+00:00

hi, your test suggestion worked where I edit the original json file and place the new name into the dataset...Note, when I modify the json it becomes a json document whereas when I have the original .json files they are of type: UTF-8 with BOM

for the modified .json file, I have to use the json setting of array of Documents in source dataflow.

for the original .json files, I have to use the json setting as Document per line
So the issue still remains because I need ot get the original files to work...Remember that the original json file is happily allowing the source dataflow to read the contents except that very array in question.
hope this helps

Thank you
QuantumCache 20,366 Reputation points Moderator

2023-07-17T19:13:53.58+00:00

Hello @arkiboys,

Could you please provide me a sample of the Source JSON files with Array? I would like to test the scenario from my side!
QuantumCache 20,366 Reputation points Moderator

2023-08-01T01:14:17.5033333+00:00

Hello @arkiboys, Did you find any resolution on this scenario?

Answer 1

QuantumCache 20,366 Moderator

Hello @arkiboys,

I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to "Accept " the answer.

Solution: Provided by OP @arkiboys

Json must be in correct formatthankyou

Previous discussion:

User's image

If you have any other questions or are still running into more issues, please let me know.
Thank you again for your time and patience throughout this issue.

Please remember to "Accept Answer" if any answer/reply helped, so that others in the community facing similar issues can easily find the solution.

arkiboys 9,706 Reputation points

2023-08-01T04:14:26.6466667+00:00

Json must be in correct formatthankyou
QuantumCache 20,366 Reputation points Moderator

2023-08-01T04:24:05.8733333+00:00

Hello @arkiboys Thanks for sharing the resolution, to help the community find the Answer, I have posted your comment in the above Answer.
Please "Accept Answer" and we can close this case. Thanks for understanding!
arkiboys 9,706 Reputation points

2023-08-01T04:26:00.74+00:00

Thankyou

Share via

read json file with arrays

0 additional answers

Your answer