BSON_VALUE_DATE function

The BSON_VALUE_DATE function is a built-in SQL function that converts values that correspond to a date in a BSON field to the DATE data type. The function returns integer string values.

Use the BSON_VALUE_DATE function to return or operate on date and time data in a field in a BSON column with SQL statements.

Syntax

BSON_VALUE_DATE function

>>-BSON_VALUE_DATE--(--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 and, insert three documents. 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:1234,join_date:ISODate("2013-05-18T00:33:14.000Z")}'::JSON);
INSERT INTO bson_table 
VALUES('{id:1235,join_date:ISODate("2012-09-12T00:13:44.000Z")}'::JSON);
INSERT INTO bson_table 
VALUES('{id:1236,join_date:ISODate("2014-08-10T00:11:44.000Z")}'::JSON);

The following statement returns the value of the id field for all dates earlier than October 5, 2012:

SELECT bson_col.id::json FROM bson_table 
WHERE bson_col.join_data::date >DATE("10/05/2012");

(expression){"id":1234}
(expression){"id":1236}

2 row(s) retrieved.

The following statements return the value of the join_date field:

SELECT bson_col.id::json, bson_value_date(bson_col, "join_date") FROM bson_table
WHERE bson_value_date(bson_col, "join_date")::date ='05/18/2013';

(expression)  {"id":1234}
(expression)  2013-05-18 00:33:14.00000

1 row(s) retrieved.

The following statement creates an index on the join_date field in the BSON column:

CREATE INDEX idx_joindt ON bson_table(bson_value_date(bson_col,"join_date"))
USING bson;

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