Viraj Jasani created PHOENIX-7650:
-------------------------------------
Summary: Default value support in BSON_VALUE() for all supported
types
Key: PHOENIX-7650
URL: https://issues.apache.org/jira/browse/PHOENIX-7650
Project: Phoenix
Issue Type: New Feature
Reporter: Viraj Jasani
BSON_VALUE() helps retrieve the value of any document field key with all the
corresponding data types. For instance, if the value of the given document
field is of type BsonString and BSON_VALUE() specifies VARCHAR as data type for
parsing the value at the client side, BsonString value will be encoded to
VARCHAR data type and returned back as the function output with PVarchar type.
The purpose of this Jira is to introduce default value support for all of the
supported scalar data types. This can help avoid multiple calls to BSON_VALUE()
function. For instance, let's consider all the rows of the table has
BsonDocument which might contain TTL field. The query that needs to retrieve
the value of TTL can be something similar to
{code:java}
WHERE BSON_VALUE(COL, 'ttlField', 'BIGINT') IS NOT NULL AND BSON_VALUE(COL,
'ttlField', 'BIGINT') < TO_NUMBER(CURRENT_TIME()){code}
For each row, we need to make two function calls to make sure the document
contains ttlField and it's value is less than current timestamp in ms.
With support of default value, this can be simplified to only single function
call:
{code:java}
WHERE BSON_VALUE(COL, 'ttlField', 'BIGINT', '9223372036854775807') <
TO_NUMBER(CURRENT_TIME()){code}
If the field does not exist, return largest value supported by Long.
Similarly, this is also helpful while creating index where if the indexed field
does not exist in the document, the use case prefers creating index on some
default constant value.
Also, consider displaying the value of e-commerce product discount offer in
some % value. If a particular item does not have discount specified, consider
using default discount value.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
