reading .json data

Question

Hello,
I have many large .json files.
In it there is a field name as companyNames for each region
some regions have one companyName and other regions have more than one companyNames
And so the files may as follows:

  ...
    region1[
    ...,
    "companyNames":[{"pNum":"876","uNum":"aaa"},{"add":"564654","uNum":"mm"},{"office":"12","uNum":"zz"}],
    ...
    ]
    region2[
    ...,
    "companyNames":{"pNum":"654","uNum":"vv"},
    ...
    ]
...

Question:
I am unable to flatten the companyNames in dataflow because there is no consistency in arrays.
As you see, sometimes there is an array and sometimes there is none.
At present the flatten activity does not show the data of the companyNames...
And so the only way I get around it is to manually edit the json file and make the following to the following which is to put [] around the single companyNames.
Is there another way to flatten the companyNames and get the data without manually putting the array in there?

  ...
    region1[
    ...,
    "companyNames":[{"pNum":"876","uNum":"aaa"},{"add":"564654","uNum":"mm"},{"office":"12","uNum":"zz"}],
    ...
    ]
    region2[
    ...,
    "companyNames":[{"pNum":"654","uNum":"vv"}],
    ...
    ]
...

Answer 1

Hi @arkiboys ,

Thanks for posting query in Microsoft Q&A Platform.

As I understand ask here you are trying to flatten a key which some times has array and some times not. Please correct me if I am wrong.

Flatten transformation to work the key or column should be in array format only. So we should make sure that column should always contains array value.

We can consider either of below to over come this.

• Check the source system if that can produce json data in proper format.
• Manually edit json source file.
• Use Azure Functions or custom code logic to modify json file and then process in data flow.

Kindly check below option as well if that works.

• Modifying json data in data flows itself and then load that data in to some txt file initially.
• Then using copy activity take that text file as source and load it as json file(in this step use binary format for source and sink).
• And then see if you can pull that json and use flatten transformation.

Hope this helps. Please let us know how it goes. Thank you.