2 Replies Latest reply on Dec 2, 2021 11:45 AM by Nico Heinze

    How to store JSON array data into one column in SQL table?

    Aniruddha Gayake New Member

      I am using IICS to do ETL. My source is a JSON file. I am using Hierarchy parser to parse that JSON and load the output into database. Everything is going fine except one thing. Within that source JSON file there is one field which is array like this: label:[Red, Green, Blue, Yellow]. Now in table which I am loading data into, there is a label column of varchar2(510 byte). When data is loaded into target by running the job, label column in table gets only one value which is at last index in source array, in above case it gets only 'Yellow' value while other 3 values are lost.

      Can anybody tell me how to store entire array into the table column?

      PS: We have no flexibility to use any programming language in IICS, all that we need to do is using IICS transformations.

      Please let me know if additional information is needed.

       

       

      Thanks in advance!

        • 1. Re: How to store JSON array data into one column in SQL table?
          user126898 Guru

          Hi Aniruddha,

          CDI does not support arrays or structs in complex data formats (JSON, AVRO, PARQUET).  It only supports primitive data structures, basically comma separated data in those formats.

           

          Informatica does have the service to do this natively in CDI-E (elastic).  CDI-E can read/write complex datatypes with no code required.

           

          CDI can only do this with calling some sort of code.

           

           

          Thanks,

          Scott

          • 2. Re: How to store JSON array data into one column in SQL table?
            Nico Heinze Guru

            What about the following idea? It definitely works in PowerCenter, so it should do in IICS as well, but I can't guarantee it works without any additional tricks I don't know.

             

            Forward the contents of the array "label" to an Expression transformation.

            In this EXP, construct one long string out of these (in your case, four) values using a variable port of appropriate length.

            Forward the contents of this variable port to an Aggregator transformation, alongside with all "ID" ports of your input.

            The AGG will deliver only the last record per "ID" ports (meaning this is the group-by port, resp. the "ID" ports are the group-by ports, depending on whether the "ID" consists of one port or several ports).

            Now re-join the output of the AGG with the "original" data stream (without the "label" array), based on the "ID" port(s).

            This way you get the cumulated string constructed from all "label" values per "ID" attached to your actual data stream.

             

            Regards,

            Nico