Forum Discussion

jordanc's avatar
jordanc
New Contributor II
3 years ago
Solved

Using Mapper to flatten my JSON with Array

I’m trying to flatten my object using a Mapper Snap before I export it to CSV. But I have been unable to map the fields coming in from the child records. Attached is a sample JSON file that represents my input.

Script_output0.json (2.8 KB)

I have tried using things like: $data[*][‘Email’] in the mapping table, but this and other attempts are falling flat. I can get data from a specific child record like this: $data[‘1’][‘Email’]. But of course I need to map the data from all of the records, not just a single one. I have read through the documentation on the Mapper Snap a few times, and I feel like I’m just missing something easy here.

Thanks in advance for taking a look!

5 Replies

  • tarena's avatar
    tarena
    New Contributor III
    3 years ago

    Usually you would use a JSON Splitter, which takes a list in a JSON document and returns multiple documents, one for each item in the list. You take the output of the JSON Splitter and feed that into a CSV Formatter to create your CSV.

    Your example JSON doesn’t have a list. ‘Data’ is an object that contains two other objects ‘1’ and ‘2’. Each of those objects have a matching set of keys. Is that how your data is coming through or does it look like the attached file?

    Script_output0.json (2.8 KB)

  • jordanc's avatar
    jordanc
    New Contributor II
    3 years ago

    Thanks @tarena.

    The data is coming in from a REST Get. I then run it through a Script Snap in order to do some desired data matching and manipulation. I’m pretty sure I can’t use a Splitter first, due to the way the API returns what I need. So I’m essentially dealing with one document here. Sounds like maybe I need a splitter after the fact instead? I played around with that a tad, but perhaps it’s time to revisit this.

    And yes, the data looks just like the attached file, although I removed some things for the sake of brevity and privacy.

    • tarena's avatar
      tarena
      New Contributor III
      3 years ago

      You can use the following expression to split the Data object into a JSON List of objects. Even though Data is an object, its keys are a list. You can leverage that fact to create a new list using the Map expression. Each row will be the object at a key in the original object.

      $data.keys().map(k => $data.get(k))

      Now that you have a list, you can use the JSON Split snap to create your document stream and your CSV.

      Map JSON Object to List_2022_12_02.slp (5.9 KB)

      • alchemiz's avatar
        alchemiz
        Contributor III
        3 years ago

        Hi guys, you can also use $data.values()