PostgreSQL Usage Example
Usage
The PostgreSQL component is an open-source component for working with a PostgreSQL object-relational database management system on the platform it also works well with AWS Redshift.
Client Use Case
PostgreSQL databases are a popular choice for many organizations to store their critical data, including customer information in CRM systems. However, when switching to a new CRM system, it’s important to ensure that access to customer data is not lost. This is where the PostgreSQL component comes in handy. By copying and updating the data that comes from the CRM system, the component enables the data to be stored in the database, ensuring continued access to customer information. Overall, the PostgreSQL component provides an effective solution for organizations to maintain data continuity when upgrading their CRM systems. Let’s start with our use case.
Flow view
We have the 1st step PostgreSQL component with a SELECT as a trigger. In our flow, it will emulate the output of the CRM system. Bundle results in batches are enabled to get an array of values.
CRM System output
{
"values": [
{
"code": 1,
"firstname": "Josh",
"lastname": "Howe",
"birthdate": "1975-08-12T00:00:00.000Z",
"country": "USA"
},
{
"code": 2,
"firstname": "Melisia",
"lastname": "Gogiani",
"birthdate": "1997-01-06T00:00:00.000Z",
"country": "Italy"
},
{
"code": 3,
"firstname": "Bob",
"lastname": "Groll",
"birthdate": "1991-10-15T00:00:00.000Z",
"country": "UK"
},
{
"code": 4,
"firstname": "Fiona",
"lastname": "Nowak",
"birthdate": "2000-01-29T00:00:00.000Z",
"country": "Poland"
},
{
"code": 5,
"firstname": "Oleg",
"lastname": "Honko",
"birthdate": "1984-03-30T00:00:00.000Z",
"country": "Ukraine"
},
{
"code": 6,
"firstname": "Casey",
"lastname": "Short",
"birthdate": "1987-05-23T00:00:00.000Z",
"country": "Australia"
}
]
}
The second step involves using the PostgreSQL component again, this time to SELECT the “clients” table previously written to the database. The key idea here is that the table may contain clients that are different from those in the CRM system. To handle this situation, we need to first save the existing data in the table, and then clear it before inserting the combined data from the database and CRM. To accomplish this, we will use an SQL Query action.
Database output
{
"result": [
[
{
"firstname": "Bob",
"lastname": "Groll",
"birthdate": "1991-10-15T00:00:00.000Z",
"country": "UK"
},
{
"firstname": "Casey",
"lastname": "Short",
"birthdate": "1987-05-23T00:00:00.000Z",
"country": "Australia"
},
{
"firstname": "Melisia",
"lastname": "Gogiani",
"birthdate": "1997-01-06T00:00:00.000Z",
"country": "Italy"
},
{
"firstname": "Oleg",
"lastname": "Honko",
"birthdate": "1984-03-30T00:00:00.000Z",
"country": "Ukraine"
}
],
[]
]
}
The 3rd step involves using the JSONata component. Since our CRM output includes a column for client code ID, we need to remove it. Additionally, since the CRM and database IDs may not necessarily match, we must use other parameters to compare old and new clients. For our purposes, we will use first name, last name, birth date, and country.
CRM transformation expression
{
"crm": $crm := $getPassthrough()."step_1".body.values ~> |$|{}, ['code']|
}
JSONata transformation output with first name, last name, birth date, and country information
{
"crm": [
{
"firstname": "Josh",
"lastname": "Howe",
"birthdate": "1975-08-12T00:00:00.000Z",
"country": "USA"
},
{
"firstname": "Melisia",
"lastname": "Gogiani",
"birthdate": "1997-01-06T00:00:00.000Z",
"country": "Italy"
},
{
"firstname": "Bob",
"lastname": "Groll",
"birthdate": "1991-10-15T00:00:00.000Z",
"country": "UK"
},
{
"firstname": "Fiona",
"lastname": "Nowak",
"birthdate": "2000-01-29T00:00:00.000Z",
"country": "Poland"
},
{
"firstname": "Oleg",
"lastname": "Honko",
"birthdate": "1984-03-30T00:00:00.000Z",
"country": "Ukraine"
},
{
"firstname": "Casey",
"lastname": "Short",
"birthdate": "1987-05-23T00:00:00.000Z",
"country": "Australia"
}
]
}
The 4th step involves using the JSONata component again, which will combine the transformed CRM output with the database output and then distinguish between the two.
Clients transformation expression
{
"result": $distinct($append($getPassthrough()."step_2".body.clients[], crm[]))
}
JSONata transformation output with clients from CRM and database
{
"result": [
{
"firstname": "Josh",
"lastname": "Howe",
"birthdate": "1975-08-12T00:00:00.000Z",
"country": "USA"
},
{
"firstname": "Melisia",
"lastname": "Gogiani",
"birthdate": "1997-01-06T00:00:00.000Z",
"country": "Italy"
},
{
"firstname": "Bob",
"lastname": "Groll",
"birthdate": "1991-10-15T00:00:00.000Z",
"country": "UK"
},
{
"firstname": "Fiona",
"lastname": "Nowak",
"birthdate": "2000-01-29T00:00:00.000Z",
"country": "Poland"
},
{
"firstname": "Oleg",
"lastname": "Honko",
"birthdate": "1984-03-30T00:00:00.000Z",
"country": "Ukraine"
},
{
"firstname": "Casey",
"lastname": "Short",
"birthdate": "1987-05-23T00:00:00.000Z",
"country": "Australia"
}
]
}
The fifth and final step involves using the PostgreSQL component with the INSERT Bulk action, which will write a new table with the combined and distinguished clients from the previous step’s output.
The output will be written in the “clients” table stored in the database:
Click here to learn more about the elastic.io iPaaS