4 Replies Latest reply on Jun 14, 2016 4:50 PM by Casey Clayton

    Database rows to JSON

    Brock Muench New Member

      I've been working on an issue for some time, and am having a hard time wrapping my head around the proper solution.  Any tips would be appreciated.

       

      My use case is to create an end point that exposes multiple database rows as a JSON API.  We have the JDBC_IC connector set up to pull in data from a database and I have managed to successfully pull back a single row and tie the columns to output fields like so

       

      The data from the database is queried to bring back a single row:

       

      employees_table

      id     name

      1      Joe Smith

       

      And the URI call for {rootURI}/employees?id=1 outputs the following JSON

      {

      "id": 1,

      "name": "Joe Smith"

      }

       

      But how would I get similar results into a JSON array for multiple rows?

       

      What I want to do is this:

      Get all rows from the database

       

      employees_table

      id     name

      1      Joe Smith

      2      Sally James

      3      Mel Williams

       

      And output this

      {rootURI}/employeeList

       

      {

      "employeeList": [

           {

            "id": 1,

            "name": "Joe Smith"

           }

           {

            "id": 2,

            "name": "Sally James"

           }

           {

            "id": 3,

            "name": "Mel Williams"

           }

      ]

      }

       

      I have the XML from the JDBC connection object and created a process object with the same field names, but everything I try just comes back as an iteration of the IDs like this:

       

      {

      "employeeList": [

          1, 2, 3

         ]

      }

       

      Any help would be appreciated!

        • 1. Re: Database rows to JSON
          Uma Ashok Guru

          Hi Brock,

           

          have you tried output field is whole payload

           

           

          output_field.png

          • 2. Re: Database rows to JSON
            Brock Muench New Member

            Hi Uma,


            I did try this but the resulting array was the same only without the property field.

             

            We found a solution to this by getting an example from one of Informatica's professional services folks.

             

            What is needed is:

            Create a process object with your desired fields defined

             

            Create a process

            Inbound fields are optional

            Outbound field should be a list of the process object

             

            Temp fields should be

            Object list of the database object

            Object reference to the database object

            Object reference to the process object

             

            Step 1

            Assign the temp object list from the db as a query with any filters

            Step 2

            Assign the temp object as a formula with list:head of the temp object list

            Assign the temp object list as a formula with list:tail of itself

            Step 3

            Evaluate if the temp object is set

            if set go to step 4

            else go to step 7

             

            Step 4

            Assign the temp process object fields to the temp database object

            This additional step is required for database objects else you will return the list container with only the IDs.
            The list process example in KB article
            409488 does not cover this step because the process objects are the same and do not need the additional assignment.

             

            Step 5

            Assign the outbound list field by adding temp process object

            Step 6

            Jump back to Step 2

            Step 7

            Any other processing you may want to do

            Step 8

            End

            • 3. Re: Database rows to JSON
              Uma Ashok Guru

              Thanks for your reply and the solution you followed, Brock.

              • 4. Re: Database rows to JSON
                Casey Clayton Seasoned Veteran

                As a follow up to this thread.  I've created a Discussion Post about Writing and Reading Files of all types (XML, JSON, CSV) I have gathered a few links and videos, there's also a collection of Sample Processes (with demo data) you can import into your org to see different patterns.

                 

                Reading and Writing - XML to Delimited CSV or JSON and Vice Versa.

                 

                Hope you find it helpful.

                 

                Thanks.

                 

                Casey.