Add new fields to a key

By default, RDI adds fields to hash or JSON objects in the target database that match the columns of the source table. The examples below show how to add extra fields to the target data with the add_field transformation.

Add a single field

The first example adds a single field to the data. The source section selects the customer table of the chinook database (the optional db value here corresponds to the sources.<source-name>.connection.database value defined in config.yaml).

In the transform section, the add_field transformation adds an extra field called localphone to the object, which is created by removing the country and area code from the phone field with the JMESPath function regex_replace(). You can also specify sql as the language if you prefer to create the new field with an SQL expression.

The output section specifies hash as the data_type to write to the target, which overrides the default setting of target_data_type defined in config.yaml. Also, the output.with.key section specifies a custom key format of the form cust:<id> where the id part is generated by the uuid() function.

The full example is shown below:

source:
  db: chinook
  table: customer
transform:
  - uses: add_field
    with:
      expression: regex_replace(phone, '\+[0-9]+ (\([0-9]+\) )?', '')
      field: localphone
      language: jmespath
output:
  - uses: redis.write
    with:
      connection: target
      data_type: hash
      key:
        expression: concat(['cust:', uuid()])
        language: jmespath

If you queried the generated target data from the default transformation using redis-cli, you would see something like the following:

 1) "customerid"
 2) "27"
 3) "firstname"
 4) "Patrick"
 5) "lastname"
 6) "Gray"
.
.
17) "phone"
18) "+1 (520) 622-4200"
.
.

Using the job file above, the data also includes the new localphone field:

 1) "customerid"
 2) "27"
 3) "firstname"
 4) "Patrick"
 5) "lastname"
 6) "Gray"
 .
 .
23) "localphone"
24) "622-4200"

Add multiple fields

The add_field transformation can also add multiple fields at the same time if you specify them under a fields subsection. The example below adds two fields to the track objects. The first new field, seconds, is created using a SQL expression to calculate the duration of the track in seconds from the milliseconds field. The second new field, composerlist, adds a JSON array using the split() function to split the composer string field wherever it contains a comma.

source:
  db: chinook
  table: track
transform:
  - uses: add_field
    with:
      fields:
        - expression: floor(milliseconds / 1000)
          field: seconds
          language: sql
        - expression: split(composer)
          field: composerlist
          language: jmespath
output:
  - uses: redis.write
    with:
      connection: target
      data_type: json
      key:
        expression: concat(['track:', trackid])
        language: jmespath

You can query the target database to see the new fields in the JSON object:

> JSON.GET track:1 $

"[{\"trackid\":1,\"name\":\"For Those About To Rock (We Salute You)\",\"albumid\":1,\"mediatypeid\":1,\"genreid\":1,\"composer\":\"Angus Young, Malcolm Young, Brian Johnson\",\"milliseconds\":343719,\"bytes\":11170334,\"unitprice\":\"0.99\",\"seconds\":343,\"composerlist\":[\"Angus Young\",\" Malcolm Young\",\" Brian Johnson\"]}]"

Using add_field with remove_field

You can use the add_field and remove_field transformations together to completely replace fields from the source. For example, if you add a new fullname field, you might not need the separate firstname and lastname fields. You can remove them with a job file like the following:

source:
  db: chinook
  table: customer
transform:
  - uses: add_field
    with:
      expression: concat(firstname, ' ', lastname)
      field: fullname
      language: sql
  - uses: remove_field
    with:
      fields:
        - field: firstname
        - field: lastname
output:
  - uses: redis.write
    with:
      connection: target
      data_type: hash
      key:
        expression: concat(['cust:', customerid])
        language: jmespath