[PHOENIX-7396] BSON_VALUE function to retrieve BSON field value with given data type

• Type: New Feature
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 5.3.0
• Assignee: Viraj Jasani
• Reporter: Viraj Jasani
• Created: 28/Aug/24 01:00
• Updated: 10/Sep/24 23:39
• Resolved: 10/Sep/24 23:39

Description

The purpose of this Jira is to introduce new function for BSON data type to retrieve the value of the given Bson Document field key. The function should also take data type as an argument to decode the document field value.

Function Grammar:

Name: BSON_VALUE

Arguments:

	Expression	DataType
1	Column Value	BSON
2	Bson Field Key	The field key can represent any top level or nested fields within the document. The caller should use "." notation for accessing nested document elements and "[n]" notation for accessing nested array elements. Unlike nested fields, top level document fields do not require any additional character.
3	SQL Data Type	The data type that the client expects the value of the field to be converted to while returning the value.

 

Definition: The function returns the value of the given field key from the BSON Document. The client is expected to provide the data type that is used for decoding the value of the field key.

Return Type: PDataType (Depending on the third argument of the function, the data type conversion takes place)

Examples:

• BSON_VALUE(COL, 'topfield', 'DOUBLE')
• BSON_VALUE(COL, 'topfield.nestedfield1', 'VARCHAR')
• BSON_VALUE(COL, 'topfield.nestedfield[2]', 'INTEGER')

Here, COL represents the column name of data type BSON.

Issue Links

• is related to: PHOENIX-7330 Introducing Binary JSON (BSON) with Complex Document structures in Phoenix
• links to: GitHub Pull Request #1962