Forum Discussion

KBD's avatar
KBD
Helper III
1 year ago
Solved

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

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","val...
Need Help
  • v-venuppu's avatar
    v-venuppu
    1 year ago

    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.

v-venuppu's avatar
v-venuppu
Community Support
1 year ago

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's avatar
    KBD
    Helper III
    1 year ago

    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's avatar
      lbendlin
      Super User
      1 year ago

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