Skip to main content Skip to complementary content

Extracting a JSON document using advanced filters

Configure tDynamoDBInput to use an advanced filter to read a JSON document from DynamoDB and use tLogRow to output this document in the console of the Studio.

About this task

Procedure

  1. Double-click tDynamoDBInput to open its Component view.

    Example

  2. Click the ... button next to Edit schema to open the schema editor.

    Example

  3. Click the + button twice to add two rows, each representing a column of the sample data, and in the Column column, name these columns to DeliveryId and EventPayload, respectively.
  4. On the row for the DeliveryId column, select the check box in the Key column to use this DeliveryID column as the partition key column of the DynamoDB table to be used. A DynamoDB table requires a partition key column.
  5. In the DB Type column, select JSON for the EventPayload column, as this is the column in which the JSON documents are stored.
  6. In the Access key and Secret key fields, enter the credentials of the AWS account to be used to access your DynamoDB database.
  7. From the Region drop-down list, select the same region as you selected in the previous steps for tDynamoDBOutput.
  8. From the Action drop-down list, select Scan.
  9. In the Table Name field, enter the name of the DynamoDB table to be created by tDynamoDBOutput.
  10. Select the Use filter expression check box and then the Use advanced filter expression check box.
  11. In the Advanced filter expression field, enter the filter to be used to select JSON documents.

    Example

    "EventPayload.customerOrderNumber.deliveryCode = :value"

    The part on the left of the equals sign reflects the structure within a JSON document of the sample data, in the EventPayload column. The purpose is to use the value of deliveryCode element to filter the document to be read.

    You need to define the :value placeholder in the Value mapping table.

  12. Under the Value mapping table, click the + button to add one row and do the following:
    1. In the value column, enter the value of the JSON element to be used as a filter.

      Example

      In this example, this element is deliveryCode and you need to extract the JSON document in which the value of the deliveryCode element is 261. As this value is a string, enter 261 within double quotation marks.

      If this value is an integer, do not use any quotation marks.

    2. In the Placeholder column, enter the name of the placeholder to be defined, within double quotation marks. In this example, it is ":value", as you have put in the Advanced filter expression.
      A placeholder name must start with a colon (:).
  13. Double-click tLogRow to open its Component view and select the Table radio box to display the extracted data in a table in the console of the Studio.
  14. Press Ctrl+S to save the Job and press F6 to run it.

Results

Once done, the retrieved JSON document is displayed in the console of the Run view of the Studio.
In the created DynamoDB table, you can see the both of the sample JSON documents.

Did this page help you?

If you find any issues with this page or its content – a typo, a missing step, or a technical error – please let us know!

Leave your feedback here