File with Multiple JSON doc's - How to Merge them?

[KBD]

Hello:

I am working with files that contain multiple JSON doc's.

The files look something like this:

{"@odata.context":"https://xxxxdev.yyyy.us/contentapi/Facilities/$metadata#Facilities","value":[{"Fac_Id":247246,.........
{"@odata.context":"https://xxxxdev.yyyy.us/contentapi/Facilities/$metadata#Facilities","value":[]}

Each @odata  is return from a REST call.  There can be several until the empty one , the final one.

I need to combine the JSON docs.  Create one JSON object which will be processed futher. 

In a data flow I created a Source on top of a JSON dataset and set Document form to Document per line.

This read two records in the file 

Value in the first row contains the data I am interested in and in the second row value is empty.

Good.

How do I concatinate the two values to create one JSON object?

What transformations do I use?

Am I approaching this correctly?

Best Regards

KBD

[v-venuppu · Accepted solution]

Hi @KBD ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @lbendlin for the prompt response.

You are reading multiple JSON documents (one per line), each with a value array.
Use Document per line in your JSON source to load them.Then apply a Flatten transformation on the value column to extract all items.This will combine all records from each JSON's value array into one dataset.Empty value arrays (like the last line) will be skipped automatically.

Thank you.

[KBD]

The solution proposed by venuppu got us on the right track.  Many thanks.

But along the way another issue came along.

As stated above data is being return from a REST API and there are multi JSON Docs in the file .

Looks like this:

{"@odata.context":"https://nypaegrcdev.archerirm.us/contentapi/Findings/$metadata#Findings","value":[{"F_Id":248765,"........
{"@odata.context":"https://nypaegrcdev.archerirm.us/contentapi/Findings/$metadata#Findings","value":[{"F_Id":256556,"........
{"@odata.context":"https://nypaegrcdev.archerirm.us/contentapi/Findings/$metadata#Findings","value":[{"F_Id":336518,"_.......
{"@odata.context":"https://nypaegrcdev.archerirm.us/contentapi/Findings/$metadata#Findings","value":[]}

 Note the @data.context.

So @data.context becomes an attribute in the pipeline Source object.

Question:   Do the pipeline and dataflow transformation objects, 

              have trouble with an attribute/field name that starts with an at sign (@)

We found that when we replaced @data.context with odata  in the JSON doc .

Things worked.   Of course that is not how the data will come in.

Thanks for your attention to this matter.

KBD

[lbendlin]

please provide valid JSON responses. Yours are truncated and thus invalid.

[lbendlin]

A standard "expand column"  should suffice.

please provide valid JSON responses for both scenarios.