This knowledge-sharing article demonstrates how to use the Pega’s System Management “Expose” API to populate newly added database columns with case data stored in the BLOB.
When schema changes are introduced after an application is in production, existing case data often needs to be exposed for reporting, search, or integration purpose.
The “Expose” Pega API provides a simple, efficient way to persist BLOB data into new columns without custom scripts or manual migrations.
Pega Infinity version used: 26.1.1
Runtime behavior
Step 1 - Say that a sample case type (Application Intake) has a field named Grant amount requested (type: Currency) that is currently not exposed to a table column.
User enters a grant amount requested value ($750,000) at runtime.
The user input is captured in the Clipboard page:
Currently, this field is stored only in the BLOB and is not exposed as a column in the case work table.
Step 2 - Create a new column in the case work table: grantamountrequested (double).
Step 3 - Run a query to verify that the column currently contains a null value.
This is because the field was exposed as a new column, but existing records haven’t been backfilled from the BLOB.
Step 4: Run the Expose API to populate the new column with the values stored in the BLOB.
To see the detailed configurations, refer to the How to configure and run the “Expose” API section below.
Step 5: Re-run the query from Step 3.
The new column is now populated with the existing record in the BLOB.
How to configure and run the “Expose” API
Step 1 - Open the ‘System Management’ service package in Pega Infinity Studio and set the Authentication type to OAuth 2.0.
This authentication mechanism is used to securely invoke the Expose API in Pega Infinity.
Step 2 - Create an OAuth 2.0 client registration rule.
Select ‘Client credentials’ checkbox and provide the access group needed to access the case type which we plan to expose via the Expose API.
Click ‘View & download’ button to download and save the client credential file.
(In this example, I used Bruno as an API client tool for invoking the Pega API. Other common tool is Postman.)
Step 3 - In Bruno, invoke the ‘Access token endpoint’ to get the access token.
https://<your-host-address>/prweb/PRRestService/oauth2/v1/token
The following information are copied from the client credential file downloaded above.
- Access token endpoint URL
- Client ID
- Client Secret
Under Headers:
Click ‘Send’ to generate an access token.
Step 4 - Invoke the Expose API.
End-point URL:
https://<your_host_address>/prweb/api/SystemManagement/v2/Expose
Request body:
{
"includedClasses": "OYEP1P-GrantMan-Work-ApplicationIntake",
"async": "false"
}
Configure the Auth details.
- The Access Token URL, client ID, and client secret are copied from the client credential file downloaded in Step 2.
- Enter the Token ID (copied from the access token generated in Step 3).
Click the ‘Get Access Token’ button.
Click ‘Send’ to expose the case work table. When successful, the highlighted result will display.
Step 5 - Verify the result.
The new column is now populated with the existing record in the BLOB.
Additional information
- If we expose more than one column, the Expose API will populate all the newly exposed columns.
- As an alternative to the Expose API, we can also use the OOTB pzBulkOptimization activity. We use this activity periodically in our client project to populate newly exposed column.
For example,
When the activity is run, it will launch the Property Optimization wizard (see a sample below). You can now schedule the column population.
Note: In my client project, our team uses the pzBulkOptimization option. My exposure to the Expose API is limited to demo/proof-of-concept scope at this point.
Other references (Pega API)
- How to create a case using Pega API (POST /cases)
- How to create OAuth 2.0 access token to call Pega API
- How to integrate Constellation and UI-Kit using Pega APIs
===
Please feel free to leave any question or comment.
