reading .json data

arkiboys 9,691

Hello,
This question is regarding flattening .json files which have inconsistent arrays as follows.
For example, I have put a simplified example of one of the .json files I receive.
Looking at the quantities field, you see the file at first does not have quantities array and then further down it has quantities array...
This therefore prevents dataflow datasource and flatten activity to read the quantities field values. and so in data preview you only get to see blank "planned" and "uom" because as you may know the dataset only gets the schema of the first appearance of the fields...
In this example file, locations is fine because throughout the file, it has arrays and it is consistent across the data.
The issue is the quantities field.

Question
How is it possible to get the values of quantities whether there is an array or not, i.e. in this case below.
Thank you

Jag 1 Reputation point

2022-03-20T22:47:15.337+00:00

ideally, in case of blank or no data, there is always an empty attribute. In your case, it should have quantities: [] for consistency.
Alternatively, you can check with ? before access quantities field

If you can further elaborate where you are using this json in ADF, that will further narrow down to certain solution.
KranthiPakala-MSFT 46,512 Reputation points Microsoft Employee

2022-03-24T23:03:45.753+00:00

Hello @arkiboys ,

Just checking in to see if the below answer provided by @Jag helped. If this answers your query, do click Accept Answer and Up-Vote for the same. And, if you have any further query do let us know.
arkiboys 9,691 Reputation points

2022-03-25T05:57:24.17+00:00

Hi,
Thanks for your message.
As mentioned before, if there is a single item then the .json files do not have []
Thanks
KranthiPakala-MSFT 46,512 Reputation points Microsoft Employee

2022-04-06T17:46:36.847+00:00

A similar question has been answered here in another thread - https://learn.microsoft.com/en-us/answers/questions/783422/reading-json-data-1.html
arkiboys 9,691 Reputation points

2022-04-07T05:49:37.777+00:00

Thank you

Share via

reading .json data

Your answer