Question
Ernst & Young LLP
IN
Last activity: 28 Nov 2025 11:07 EST
Unexposed property in Obj-Browse method where clause
Hi,
Just a quick question on accessing unexposed properties via Obj-Browse method and Report Definition. When trying to access unexposed properties via Report Definition in both select statement and where clause we are getting severity warning and using Pega UDF pr_read_from_stream for both select and where statement in Pega 24.2. When trying to access unexposed properties via Obj-Browse, its opening the entire blob to fetch the unexposed properties in select statement but not able to add unexposed properties via where clause. Looking to understand the reason for this on why unexposed properties are working in where clause via Report definition but not via Obj-Browse method. Also, not able to access unexposed properties via Report definition in Infinity 25 version.
Thanks in Advance,
C.Shivakumaaran
Pegasystems Inc.
PL
Hello @ShivakumaaranC
As I understand it when you use:
1. obj-browse :
Pega has to read the entire Binary Large Object (BLOB) or pzPVStream column for each row and decompress it to find the value of that property. This is a very resource-intensive operation and can lead to severe performance degradation.
The Obj-Browse method does not allow you to specify an unexposed (unoptimized) property in the selection criteria. The WHERE clause of the generated SQL can only operate on columns that are exposed in the database table
2. Report definition with "Allow filtering on unexposed properties" works a bit differently:
When you run a report that filters on an unexposed property, here’s what happens:
-
Pega cannot use the property in the SQL
WHEREclause because there is no corresponding column in the database table. -
Instead, the database returns all rows from the table to the Pega application server.
-
For each and every row, the Pega server must load the entire BLOB, decompress it, find the value of your unexposed property, and then check if it matches your filter criteria.
Hello @ShivakumaaranC
As I understand it when you use:
1. obj-browse :
Pega has to read the entire Binary Large Object (BLOB) or pzPVStream column for each row and decompress it to find the value of that property. This is a very resource-intensive operation and can lead to severe performance degradation.
The Obj-Browse method does not allow you to specify an unexposed (unoptimized) property in the selection criteria. The WHERE clause of the generated SQL can only operate on columns that are exposed in the database table
2. Report definition with "Allow filtering on unexposed properties" works a bit differently:
When you run a report that filters on an unexposed property, here’s what happens:
-
Pega cannot use the property in the SQL
WHEREclause because there is no corresponding column in the database table. -
Instead, the database returns all rows from the table to the Pega application server.
-
For each and every row, the Pega server must load the entire BLOB, decompress it, find the value of your unexposed property, and then check if it matches your filter criteria.
This is not recommended because it bypasses the power of database indexing entirely and shifts the workload from the database to the Pega application server, causing high CPU and memory usage and leading to extremely slow reports.
Recommended Best Practices
Instead of reading from the BLOB, the Pega-recommended approach is to expose the properties you need for reporting or filtering.
-
Expose Single Properties: For simple, single-value properties, use the Property Optimization wizard to create a dedicated column in the database table.
- Use Declare Index: For aggregate properties like those in a Page List or Page Group, the best practice is to use a
Rule-Declare-Index. This creates a separate index table with the properties you need exposed as columns, which can then be efficiently joined and queried for reporting.
By exposing properties, the database can use its native indexing and query capabilities, leading to much faster and more efficient data retrieval.
Ernst & Young LLP
IN
Hi @toboa ,
Thanks for the explanation and it was very helpful. Just follow up questions of loading up the entire blob for each and every row. In the tracer, I was able to see the SQL statement getting executed while running the report definition. It was using the Pega UDF function pr_read_from_stream in both the select statement and also in the where clause. Does using Pega UDF function load the entire blob even if only one property is specified in the function?
Similarly while tracing the execution of obj-browse method, the SQL statement was selecting the entire blob pzPVStream column in the select statement and was not using Pega UDF function pr_read_from_stream.
Any specific reason for difference in execution in both the ways of selecting only the pzPVStream column and using Pega UDF pr_read_from_stream.
Regarding exposing the single value properties, I believe rich text editor is a single value property and we can expose it. But my question would be, we have to put the restriction in the column size(e.g., 1024 characters, can increase if needed) if we expose a property and a rich text editor can have characters greater than 1024 which will lead to truncation of the text stored in rich text editor which will cause issues in fetching the column value. Is there any solution to avoid this issue for a rich text editor?
Hi @toboa ,
Thanks for the explanation and it was very helpful. Just follow up questions of loading up the entire blob for each and every row. In the tracer, I was able to see the SQL statement getting executed while running the report definition. It was using the Pega UDF function pr_read_from_stream in both the select statement and also in the where clause. Does using Pega UDF function load the entire blob even if only one property is specified in the function?
Similarly while tracing the execution of obj-browse method, the SQL statement was selecting the entire blob pzPVStream column in the select statement and was not using Pega UDF function pr_read_from_stream.
Any specific reason for difference in execution in both the ways of selecting only the pzPVStream column and using Pega UDF pr_read_from_stream.
Regarding exposing the single value properties, I believe rich text editor is a single value property and we can expose it. But my question would be, we have to put the restriction in the column size(e.g., 1024 characters, can increase if needed) if we expose a property and a rich text editor can have characters greater than 1024 which will lead to truncation of the text stored in rich text editor which will cause issues in fetching the column value. Is there any solution to avoid this issue for a rich text editor?
And also a question on why I am getting error while trying to run a report definition with unexposed properties in Infinity 25 version, even though it was working fine in 24.2 version?
Thanks in advance,
C.Shivakumaaran
Pegasystems Inc.
PL
@ShivakumaaranC
I am not an expert in this topic but as far as I know:
Does using Pega UDF function load the entire blob even if only one property is specified in the function?
-Pega UDF function pr_read_from_stream operates ON the pzPVStream column, so you cannot use pr_read_from_stream without pzPVStream. The function requires the BLOB column as one of its inputs to do its job.It reaches into the pzPVStream, decompressing it on the fly, finding the specific property you asked for, and returning its value. When you try to filter in pr_read_from_stream it basically performs a full table scan which is exactly why it is not recommended. I am not aware why is there a difference between obj-browse and report definitions.
@ShivakumaaranC
I am not an expert in this topic but as far as I know:
Does using Pega UDF function load the entire blob even if only one property is specified in the function?
-Pega UDF function pr_read_from_stream operates ON the pzPVStream column, so you cannot use pr_read_from_stream without pzPVStream. The function requires the BLOB column as one of its inputs to do its job.It reaches into the pzPVStream, decompressing it on the fly, finding the specific property you asked for, and returning its value. When you try to filter in pr_read_from_stream it basically performs a full table scan which is exactly why it is not recommended. I am not aware why is there a difference between obj-browse and report definitions.
If your property is an RTE field, it is also a bit tricky with exposing it. It is possible but uncontrolled may lead to a serious performance impact. Maybe in your case you do not need RTE field but text area field would suffice? Or you can negotiate to put some limitations on the RTE fields by limiting options on the RTE control with only few like: block elements, lists, text formats and do not include for example images. Also it is very important that the property that RTE field is based on should have max length set up in the application. That will help you with controlling what should be the length of the property in the database (when exposing Pega should automatically set that length to be database column length) so no truncation will happen.
Ernst & Young LLP
IN
Hi @toboa,
Thanks for your explanation. We have restricted the RTE field to allow only characters, but we have given restriction for word count(500) only and not character count in the RTE field. So it takes us back to the issue I had mentioned about values getting truncated. For 500 word count property the user can enter it within 2000 characters or can exceed 4000 characters also if needed. What should be the best approach to expose the column without getting the data truncated as we know increasing the column size will just bloat the table and performance will be impacted again.
Is the pr_read_from_stream Pega UDF available in Infinity 25 version? I tried report definition in community edition with unexposed properties and I was getting error.
Is it possible for someone from Pega who has better knowledge on this topic provide more answers to this question which will help my understanding better?
Thanks in advance,
C.Shivakumaaran