使用Socket.io写即使通信服务，消息需要存储，本来打算使用Mongodb，但是以往公司使用的存储是PostgreSQL，想来也可以用来存储JSON类型的数据，了解了一番发现挺好用的，jsonb类型正好适合

初始化数据

创建person表并插入几条数据

-- ----------------------------
-- Table structure for person
-- ----------------------------
DROP TABLE IF EXISTS "public"."person";
CREATE TABLE "public"."person" (
  "jdoc" jsonb
)
;

-- ----------------------------
-- Records of person
-- ----------------------------
INSERT INTO "public"."person" VALUES ('{"age": 23, "tag": ["炎帝", "天府联盟", "火莲", "大斗师"], "name": "萧炎", "birth": "1996-03-06", "online": true, "saying": "斗宗强者，恐怖如斯", "capability": {"luck": 100, "power": 100, "character": 90}}');
INSERT INTO "public"."person" VALUES ('{"age": 26, "tag": ["天选之人", "位面之子", "光武大帝"], "name": "刘秀", "birth": "1970-01-01", "online": false, "saying": "土遁-天降陨石", "capability": {"luck": 100, "power": 90, "character": 90}}');
INSERT INTO "public"."person" VALUES ('{"age": 55, "tag": ["周树人", "文学家", "思想家", "革命家"], "name": "鲁迅", "birth": "1881-09-25", "online": false, "saying": "横眉冷对千夫指，俯首甘为孺子牛", "capability": {"luck": 70, "power": 70, "character": 90}}');

这里创建了person的数据库，并且创建了jdoc字段用来保存json字符串

数据类型如下：

{
  "name": "路人甲",
  "age": 20,
  "birth": "2008-01-01",
  "tag": ["无重点", "佚名", "无存在感"],
  "saying": "百分百空手接白刃",
  "capability": {
    "luck": 10,
    "character": 20,
    "power": 30
  },
  "online": true
}

查询数据

根据姓名查询，这里有两种方式，可以使用->>也可以使用@>

->>操作符支持json类型与jsonb类型，@>操作符只有jsonb类型可以使用

在postgresql中，jsonb相比于json类型，它将输入的json进行处理后存储，比json类型多一点点存储，写入速度也会下降一点，不过好处很多，支持更多的操作符和函数操作，并且支持索引

-- 根据姓名
SELECT * FROM person WHERE jdoc->>'name' = '刘秀';
SELECT * FROM person WHERE jdoc @> '{"name": "刘秀"}'

数值比较

-- 年纪小于30岁
SELECT * FROM person WHERE (jdoc->>'age')::int < 30;

-- 运气值大于80
SELECT * FROM person WHERE (jdoc#>>'{capability, luck}')::int > 80;
SELECT * FROM person WHERE (jdoc->'capability'->>'luck')::int > 80;

布尔类型数据，三种写法都是等价的

-- 布尔类型
SELECT * FROM person WHERE jdoc @> '{"online": false}';
SELECT * FROM person WHERE (jdoc->>'online') = 'false'; 
SELECT * FROM person WHERE not (jdoc->>'online')::boolean; 

将时间转换为timestamp格式然后进行比较

-- 时间筛选
SELECT * FROM person WHERE (jdoc->>'birth')::timestamp < '1970-11-11 00:00:00'::timestamp

之前数据存储在ElasticSearch中，有功能是全文检索，我想到的比较简单方式是直接把jsonb转换为text形式，然后用LIKE进行模糊匹配，也能实现全文检索，性能一般但蛮方便

-- JSON串模糊搜索
SELECT * FROM person WHERE jdoc::text LIKE '%搜索内容%';

这里有一篇文章说把每个字段做成索引的，我没试验过：Trigram index on all values of a JSONB column

更新数据

jsonb_set 的第四个参数是说如果原数据中没有这个字段是否新建

-- 更新-运气值
UPDATE person SET jdoc=jsonb_set(jdoc, '{capability, luck}', '88'::jsonb, false) WHERE (jdoc->>'name') = '鲁迅';

-- 删除字段
UPDATE person SET jdoc = jdoc - 'attrB'

创建索引

-- 创建索引
CREATE INDEX idxname ON person ((jdoc->>'name'));

CREATE INDEX idxginp ON person USING gin (jdoc jsonb_path_ops);
CREATE INDEX idxgin ON person USING gin (jdoc);

上边三条创建索引的命令，第一条作用于jdoc->>'name'取值，第二条作用于@>操作符，不作用于? ?& ?| @>，或者第三条不指定操作符，那么jsonb特有的操作符就都会支持

PS：数据没几条的时候，查询是不会使用索引，如果数据量很大的时候索引就很有用了

在一个测试库测试，100W条数据，没有索引的时候查询出十多条数据需要花费1461 ms，创建索引后30 ms，所以用PostgreSQL存储JSON数据，选择使用jsonb还是很值得的

写入测试数据

Node.js 生态下的pg包挺好用的

const { Pool, Client } = require('pg')
const connectionString = 'postgresql://postgres:postgres@IP:5432/dbname'

const pool = new Pool({
  connectionString: connectionString,
})

const fs = require('fs')


function insert_data() {

  jd = '{"name":"路人甲","age":20,"birth":"2008-01-01","tag":["无重点","佚名","无存在感"],"saying":"百分百空手接白刃","capability":{"luck":10,"character":20,"power":30},"online":true}'

  const query = {
    text: 'INSERT INTO "person"(jdoc) VALUES ($1)',
    values: [jd]
  }

  pool.query(query)
    .then(res => {
      if (res.rowCount === 1) {
        console.log("insert data success")
      }
    })
    .catch(e => {
      console.log("insert data failed")
      console.error(e.stack)
    })
}

for ( let i = 0; i < 10000; i++ ) {
  insert_data()
}

其它json操作，参考文档很全且会很有帮助

参考

• PostgreSQL 9.5.15 Documentation