Uniformize document schema

[Fabien]

Hello!

I am trying to uniformize document schema, I was not able to achieve it using a mapper or a JSon formatter snap.

For instance, if I have this input (using JSonGenerator)

[
    {
        "num" : 1

    },
    {
        "msg" : "Hello, World", "num" : 2
    }
]

I want this output:

[
    {
        "msg" : null, "num" : 1
    },
    {
        "msg" : "Hello, World", "num" : 2
    }
]

I need to use this feature in a generic pipeline, thus I cannot use explicitly the keys (‘num’ or ‘msg’)

I need to have all the keys in the first document because I am using Snowflake Bulk Insert downsteam and the generated ‘Merge Into’ command that is executed in Snowflake only take the first document keys unfortunately (same behavior with a csv parser for instance, but this is not documented for Snowflake Bulk Insert)

Has anyone an idea to achieve this ?

thank you

[alchemiz · Accepted solution]

Hi @Fabien,

Good day, see attached PoC pipeline

canvas_01_2023_03_12.slp (12.6 KB)

Hope this helps

Thanks,
EmEm

[Fabien]

Wow! thank you a lot @alchemiz ! That’s brillant !

I am using a ‘Gate’ Snap to gather the document in one and thus I can use the logic you built in the mappers and it works A1 !

Have a nice week !