Transforming Comma-Delimited Text to One-To-Many Records in a Creatio Postgresql Database

A client recently had an Excel file that was imported into their Creatio system. The Excel file had a text column of tags that were comma-delimited text values. This values in the Excel file looked something like this:

To import this Excel file, a text field named UsrTags was created and the comma-delimited text from the Tags column was imported there. That got the data into Creatio, but it’s not very usable in that format. Ideally, the comma-delimited text would be created as actual Tags and then added to the contacts created by the import.

Most recent Creatio systems have a Postgresql database. Postgres has some functions that make transforming comma-delimited text to multiple 1:many rows an easy task.

First, Postgres has an array data type and also a function called string_to_array which takes a comma-delimited string and creates it as an array.

Then, you can use the Postgres unnest function to turn the array into multiple rows, one for each array item. You can include other fields with it that will repeat for each new row from the array.

Example

select unnest(string_to_array('text1,text2,text3', ',')) as "MyField"

Becomes:

And combined with other fields like this:

select unnest(string_to_array('text1,text2,text3', ',')) as "MyField1", 'Something' as "MyField2"

Becomes:

View more about Postgresql’s array functions
Array Functions and Operators

Back to the point. For this customer, to transform this comma-delimited text in the UsrTags field of the Contact, we’ll do two just statements in Postgres. One that creates the tags (for any tags that don’t yet exist), and a second query to add the tags to the contacts based on the comma-delimited text each contact record has.

First, the query below inserts into the Tag table the distinct text values stored in the UsrTags field across all contacts. Note, the join to Tag plus t.”Id” is null at the end ensures the tag doesn’t already exist.

insert into "Tag" ("Name", "TagAccessId", "EntitySchemaName") 
select
    tagtext."Tag",
    '5126592f-5e93-42b4-b3a1-10752e6d413e',
    'Contact'
from 
    (
        select 
            distinct 
            unnest(string_to_array("UsrTags", ',')) as "Tag"
        from 
            "Contact" 
        where 
            "UsrTags" <> ''
    ) tagtext 
    
    left join "Tag" t on t."Name" = tagtext."Tag"
where 
    t."Id" is null

The tags themselves are created at this point. Next we need to add the tags to the contacts (adding a record to TagInRecord for each tag for the contact).

insert into "TagInRecord" ("RecordSchemaName", "TagId", "RecordId", "TagRecordId") 
select
    'Contact',
    t."Id",
    tagtext."ContactId",
    t."Id"
from 
    (
        select 
            distinct 
            unnest(string_to_array("UsrTags", ',')) as "Tag",
            "Id" as "ContactId"
        from 
            "Contact" 
        where 
            "UsrTags" <> ''
    ) tagtext 
    
    left join "Tag" t on t."Name" = tagtext."Tag"
where 
    t."Id" is not null

Note, you could also add a join to TagInRecord to make sure the contact doesn’t already have the tag, if needed. The end result is just as expected and a far better experience.

A quick task thanks to Postgresql’s built in functions. As a side benefit, the data is far easier to work with as actual tag records as well

As a side note, you can take the 1:many tags back to comma-delimited text pretty easy too:

select "RecordId" as "ContactId", array_to_string(array_agg(t."Name"), ',') as "TagsText” 
from "TagInRecord" tr inner join "Tag" t on tr."TagId" = t."Id” 
group by "RecordId"

Or you can do it like this, without the need for using a group by:

select 
    "Id" as "ContactId”, 
    array_to_string(array(
        select t."Name” from "TagInRecord" tr inner join "Tag" t on tr.”TagId" = t."Id” where tr."RecordId" = c.”Id"
    ), ',') as TagsText 
from "Contact" c

Just in case you ever need to have a comma-delimited list of tags for a contact again.