GBase 8t支持JSON/BSON

GBase 8t内置支持JSON/BSON 

drop table if exists t_sql;

create table t_sql (

        device_id integer,

name varchar(20),

addr varchar(20)

);

create index idx_t_sql_device_id on t_sql(device_id);

create index idx_t_sql_name on t_sql(name);

--insert data

insert into t_sql values(1,'dev_name1', 'addr1');

insert into t_sql values(2,'dev_name2', 'addr2');

insert into t_sql values(3,'dev_name3', 'addr3');

--create a nosql table t_json

drop table if exists t_json;

create table t_json(

        device_id integer,

data BSON

);

create unique index idx_id on t_json(device_id);

Create index ix_t_json_name on t_json(bson_extract(data, "name"));

--insert data

insert into t_json values(1,'{name:"dev_name1",addr:"addr1"}'::JSON);

insert into t_json values(2,'{name:"dev_name2",addr:"addr2"}'::JSON);

insert into t_json values(3,'{name:"dev_name3",addr:"addr3",type:"t1"}'::JSON);

select * from t_sql where device_id=1;

select device_id, bson_value_lvarchar(data,'name') from t_json where device_id=1;

select device_id, bson_value_lvarchar(data,'name') from t_json where bson_value_lvarchar(data,'name')='dev_name1';

select *  from t_sql, t_json

where t_sql.device_id=t_json.device_id;

select *  from t_sql, t_json

where t_sql.name=bson_value_lvarchar(t_json.data,'name')

and t_sql.device_id>1;