postgresql----JSON类型、函数及全文检索的支持

create table test_ft(id int4,arry VARCHAR[],content1 jsonb,body text);
insert into test_ft values(1,ARRAY [ 'x', 'y' ],'{
"guid": "9c36adc1-7fb5-4d5b-83b4-90356a46balabala",
"name": "james",
"is_active": true,
"company": "Oracle",
"address": "178 Howard Place, Gulf, Washington, 702",
"registered": "2009-11-07T08:53:22 +08:00",
"latitude": 19.793713,
"longitude": 86.513373,
"tags": [
"enim",
"aliquip",
"qui"
]}','postgresql支持两种json数据类型：json和jsonb，而两者唯一的区别在于效率,json是对输入的完整拷贝，使用时再去解析，所以它会保留输入的空格，重复键以及顺序等。而jsonb是解析输入后保存的二进制，它在解析时会删除不必要的空格和重复的键，顺序和输入可能也不相同。使用时不用再次解析。两者对重复键的处理都是保留最后一个键值对。效率的差别：json类型存储快，使用慢，jsonb类型存储稍慢，使用较快。');

查询JSON的某个key，如下：

select id,arry, content1->'name',substr(body,1,100) from test_ft t
where t.arry @>'{"x"}' 
and t.content1 @>'{"name":"james"}'

查询JSON中某个数组类型属性的某个索引等于n的记录：

select id,arry, content1->'name',substr(body,1,100) from test_ft t
where t.arry @>'{"x"}' 
and t.content1 @>'{"name":"james"}' and t.content1->'tags'->>1='aliquip'

如此以来，所有的key都可以查询了。

https://www.postgresql.org/docs/current/functions-json.html

https://www.cnblogs.com/alianbog/p/5658156.html

index on jsonb

　　gin提供了对jsonb的原生支持。 什么时候需要在json字段上建立索引呢？全文检索的时候。如下所示：

　　

 　　每套房子在每个标签上都有一个属性，这些属性既可以单独每列存储，也可以存储在JSON中（mongodb和es的通常做法）。同时，搜索文本框作为全文检索字段，为了高精确率（因为很多术语、地点、企业名并非标准分词，见scws），一般需要维护可配置的词法库。

　　关于GIN的实现以及原理，可以参考postgresql查询优化之GIN（Generalized Inverted Index）索引与全文检索。怎么样在JSONB上创建GIN索引呢？

　　需要先安装uuid插件，LightDB内置集成了uuid。

create table wide_table(id bigserial primary key,val jsonb,c_name varchar(50),c_desc varchar(50),c_en_name varchar(100));

insert into wide_table_5000w select x,('{"d1":' || ceil(random()*(11-1)+0) || ',' ||'"d2":' || ceil(random()*(11-1)+0) || ',' ||'"d3":' || ceil(random()*(9-1)+0) || ',' ||'"d4":' || ceil(random()*(21-1)+0) || ',' ||'"d5":' || ceil(random()*(31-1)+0) || ',' ||'"d6":' || ceil(random()*(4-1)+0) || ',' ||'"d7":' || ceil(random()*(8-1)+0) || ',' ||'"d8":' || ceil(random()*(5-1)+0) || ',' ||'"d9":' || ceil(random()*(11-1)+0) || ',' ||'"d10":' || ceil(random()*(4-1)+0) || ',' ||'"d11":' || ceil(random()*(5-1)+0) || ',' ||'"d12":' || ceil(random()*(7-1)+0) || ',' ||'"d13":' || ceil(random()*(6-1)+0) || ',' ||'"d14":' || ceil(random()*(5-1)+0) || ',' ||'"d15":' || ceil(random()*(21-1)+0) || ',' ||'"d16":' || ceil(random()*(16-1)+0) || ',' ||'"d17":' || ceil(random()*(51-1)+0) || ',' ||'"d18":' || ceil(random()*(33-1)+0) || ',' ||'"d19":' || ceil(random()*(31-1)+0) || '}'::text)::jsonb,
substr(uuid_generate_v4()::text,1,cast(ceil(random()*(16-1)+0) as int)),
substr(uuid_generate_v4()::text,1,cast(ceil(random()*(32-1)+0) as int)),
substr(uuid_generate_v4()::text,1,cast(ceil(random()*(50-1)+0) as int)) from generate_series(1,50000000) x;

CREATE INDEX gin_idx_wide_table ON wide_table USING GIN (jsonb);

走主键索引的执行计划： 

走GIN索引的执行计划：

 

但是如果查询条件里面某个值要符合多个，比如d15 in ('3','2','1')，GIN索引就爱莫能助了。这在即席查询中几乎使得GIN过于局限，进而发挥空间受限。

社区有个btree_gin，仅供参考用，并且存在数据一致性问题，不建议使用，而且执行计划似乎也不生效。