Converting an array to a denormalized table

[maahutch]

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?

[AleksandarAngel]

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]

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"
    
                    }
                ]
}

[AleksandarAngel]

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.