Forum Discussion

maahutch's avatar
maahutch
New Contributor
3 years ago

Converting an array to a denormalized table

This seems like it should be simple but I’m still relatively new to snaplogic and just can’t figure it out. The json coming into my Mapper snap is formatted like this:

{"record1": {"id" : "1", 
            "subject":{
                        "subject_id": ["a", "b"]
            }
        },
 "record2": {"id" : "2",
            "subject":{
                        "subject_id": ["b", "c"]
            } 
        }
}

Which I’d like to convert to a table like this:

id  |  subject_id
________________
1   |    a
1   |    b
2   |    b
2   |    c

I’ve been trying something like this based on some other posts I saw here but I don’t know what I’m doing

$.keys().filter(k => k.startsWith("$subject_id")).map(k => k.substring("$subject_id".length)).map(i => {"id": $.get("id"+i), "subject_id": $.get("$subject_id"+i)}

Can someone explain to a beginner, how to perform this type of transformation?

3 Replies

  • Aleksandar_A's avatar
    Aleksandar_A
    Contributor III
    3 years ago

    Hello @maahutch,

    You can try by using the following expression in a Mapper Snap:

    $.mapValues((v,k) =>v.mapValues((v1,k1) => typeof v1 == 'object' ? v1.subject_id.map(x => {"id":v.id,"subject_id":x}): false))

    Then, you just split the array with JSON Splitter with the following JSON Path:

    jsonPath($,"..subject")

    Attached below is the sample pipeline.
    –sl-comm-array-denormalized-table_2023_06_22.slp (5.1 KB)

    Please note that if you have another input structure this will not work.

    Let me know if this helps you.

    Best Regards,
    Aleksandar.

  • maahutch's avatar
    maahutch
    New Contributor
    3 years ago

    Thanks Aleksandar. Is there a resource to learn this notation and how to use it in Snaplogic?

    If my JSON were formatted like this, how would I adapt that expression?

    {
    "id": "1",
    "subjects":
                 [
                    {
                        "subject_id":"a"
    
                    },
                    {
                        "subject_id":"b"
    
                    }
                ]
}
    • Aleksandar_A's avatar
      Aleksandar_A
      Contributor III
      3 years ago

      For more resources and helpful insights please refer to the official SnapLogic documentation SnapLogic Documentation.

      In order to format the new json you can use the following expression in a Mapper Snap:

      $subjects.map(x => x.extend({"id":$id}))

      Then use a JSON Splitter to split the subjects array.

      You can also refer to the sample pipeline attached below:

      –sl-comm-array-denormalized-table_2023_06_23.slp (9.0 KB)

      Regards,
      Aleksandar.