BSON_GET function

The BSON_GET function is a built-in SQL function that retrieves field-value pairs for the specified field in a BSON column.

Syntax

BSON_GET function

>>-BSON_GET--(--bson_column--,--"--field--"--+--------------------+--)-><
                                             '-,--"--new_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
`new_field`	A string that represents the name to assign to the returned field.		Quoted String

Usage

Use the BSON_GET function to retrieve the field-value pairs for a specific field, which you can then manipulate with SQL statements. You can specify to rename the returned field.

Example: Return field-value pairs for a field

The following example returns the field-value pairs for the name field:

CREATE DATABASE testdb WITH LOG;
CREATE TABLE 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_get(bson_col,"name")::json FROM bson_table;


(expression)  {"name":"Joe"} 

(expression)  {"name":"Mike"} 

(expression)  {"name":"Nick"} 

3 row(s) retrieved.

Example: Return field-value pairs for a field with a new field name

The following statement returns the field-value pairs with a field name of first_name instead of name:

SELECT bson_get(bson_col,"name","first_name")::json FROM bson_table;


(expression)  {"first_name":"Joe"} 

(expression)  {"first_name":"Mike"} 

(expression)  {"first_name":"Nick"} 

3 row(s) retrieved.

Example: Update values in multiple fields

The following example updates the value of the id field and the name field where the value of the name field is Joe:

UPDATE bson_table 
              SET bson_col = '{"id":300,"name":"Sr Joe"}'::JSON::BSON  
              WHERE BSON_GET(bson_col,"name") = '{"name":"Joe"}'::JSON::BSON;

1 row(s) updated.

>  SELECT bson_col::JSON FROM bson_table;


(expression)  {"id":300,"name":"Sr Joe"} 

(expression)  {"id":101,"name":"Mike"} 

(expression)  {"id":102,"name":"Nick"} 

3 row(s) retrieved.

Example: Create an index on a field

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

CREATE INDEX idx1 ON bson_table(BSON_GET(bson_col, "name")) USING BSON;