Using context variables in a DSQL map

Use the getContextVariable function to call variables from a Job.

Before you begin

You have created an input and an output structure. You can use the JSON samples below to create your structures.

About this task

In this example, you want to map an input JSON structure with an output that is similar but has two more elements that need to be populated from context variables set in a Job.

The input data looks like this:

{
    "company": "acme",
    "items": [
        {
            "item": "hammer",
            "amount": 5.55
        },
        {
            "item": "screwdriver",
            "amount": 7.23
        }
    ]
}

The output structure looks like this:

{
    "company": "",
    "date": "",
    "items": [
        {
            "item": "",
            "amount": 5.55,
            "taxes": 2.1
        }
    ]
}

Procedure

Create a new DSQL Map and add the input and output structures.
Drag and drop the input root element on the output root to map all input elements with a matching output element.
Click the date element and enter the following expression:
```
getContextVariable('date')
```
The getContextVariable allows you to call a context variable when using a DSQL map in a Job. In this case, the Job will have a variable named date.
Click the taxes element and enter the following expression:
```
amount * getContextVariable('taxpct') / 100
```
This expression indicates the value of taxes is calculated using the value of the amount element and the value specified in the taxpct context variable.
The map is configured, you can now configure context variables for testing.
In the Data Mapper tab, right-click the Sample Data folder, click New > File and name the file test_run_properties.csv.
Open the file, add the following content, and save it.
```
context.date,2022-12-04
context.taxpct,12
```

Results

The map is configured to use context variables, you can use the Test Run to test it with the sample values you added. In this example, the following result is returned:

{
   "company": "acme",
   "items": [
      {
         "item": "hammer",
         "amount": 5.55,
         "taxes": 0.6659999999999999
      },
      {
         "item": "screwdriver",
         "amount": 7.23,
         "taxes": 0.8676
      }
   ]
}

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 – let us know how we can improve!

Leave your feedback here