BSON_SIZE function

The BSON_SIZE function is a built-in SQL function that returns the storage size of a field in a BSON column. The size is returned as an integer, in units of bytes. If the field is not specified, the function returns the size of the entire BSON column.

Syntax

BSON_SIZE function

>>-BSON_SIZE--(--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 length of the specific field-value name from the BSON column.

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::BSON);
INSERT INTO bson_table VALUES('{id:101,name:"Mike"}'::JSON::BSON);
INSERT INTO bson_table VALUES('{id:102,name:"Nick"}'::JSON::BSON);

SELECT BSON_SIZE(bson_col,"name") FROM bson_table;

(expression)   
             8
             9
             9

3 row(s) retrieved.

In the following query of the same table, the field value is specified as an empty string, which returns the total size of all the BSON objects that are in the BSON column.

SELECT BSON_SIZE(bson_col,"") FROM bson_table;

     (expression)   
             27
             28
             28

3 row(s) retrieved.

The previous examples show simple field-name value pairs, but the field parameter in BSON_SIZE expressions for BSON columns with more complex structures can be specified as a multilevel identifier in dot notation.