Redis Add Field Example

By default, RDI adds fields to [hash]({{< relref "/develop/data-types/hashes" >}}) or [JSON]({{< relref "/develop/data-types/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]({{< relref "/integrate/redis-data-integration/reference/data-transformation/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]({{< relref "/integrate/redis-data-integration/data-pipelines/pipeline-config" >}})).

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]({{< relref "/integrate/redis-data-integration/reference/jmespath-custom-functions" >}}) 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:

name: Add local phone field to customer
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]({{< relref "/develop/tools/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.

name: Add multiple fields to track
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]({{< relref "/integrate/redis-data-integration/data-pipelines/transform-examples/redis-remove-field-example" >}}) 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:

name: Add fullname and remove separate name fields
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