BSON_VALUE_INT function

The BSON_VALUE_INT function is a built-in SQL function that converts a whole number, up to a maximum absolute size of 2³² -1 bytes, in a BSON field to an INT data type. The function returns integers in the range -2 147 483 647 to 2 147 483 647 bytes.

Use the BSON_VALUE_INT function to return or operate on numeric data in a field in a BSON column with SQL statements.

Syntax

BSON_VALUE_INT function

>>-BSON_VALUE_INT--(--bson_column--,--"--field--"--)-----------><


Element	Description	Restrictions	Syntax
`bson_column`	Name of a BSON column	Must exist	Identifier
`field`	A string that represents a field name to search for in the BSON column. Can be a multilevel identifier.		Quoted String, Column Expressions

Usage

The field parameter can be a simple SQL identifier. The statements in the following example create a table with a BSON column, insert three documents, and then return the value of the id field. The documents are explicitly cast to JSON and then implicitly cast to BSON.

CREATE DATABASE testdb WITH LOG;
CREATE TABLE IF NOT EXISTS bson_table(bson_col BSON);

INSERT INTO bson_table VALUES('{id:100,name:"Joe"}'::JSON);
INSERT INTO bson_table VALUES('{id:101,name:"Mike"}'::JSON);
INSERT INTO bson_table VALUES('{id:102,name:"Nick"}'::JSON);

SELECT BSON_VALUE_INT(bson_col,"id") FROM bson_table;

     (expression)   
             100
             101
             102

3 row(s) retrieved.

This example shows simple field-name value pairs, but the field parameter in BSON_VALUE_INT expressions for BSON columns with more complex structures can be specified as a multilevel identifier in dot notation.