从 Elastic Search 向 postgreSQL 迁移的探索

背景

原来的数据储存在 es，最近出现了连表查询的需求，因此考虑换成关系型数据库存储原来的数据. 之前使用的技术 golang + elastic, 存储其他数据时也引入过 mysql, 但为了兼容原来的使用需求考虑使用 postgreSQL 替换掉 elastic, 这样有以下几个问题需要考虑

主要问题：

1. 数组能否实现 terms 查询

2. 能否对 json 数据局部更新

3. gorm 的支持

Array

创建

create table ware(spu text primary key, sku text[]);

看看 ware 表结构

postgres=# \d ware
               Table "public.ware"
 Column |  Type  | Collation | Nullable | Default 
--------+--------+-----------+----------+---------
 spu    | text   |           | not null | 
 sku    | text[] |           |          | 
Indexes:
    "ware_pkey" PRIMARY KEY, btree (spu)

存储

postgres=# insert into ware values ('123', array ['111','222','333']);
...
postgres=# insert into ware values ('cd', array ['cc','dd']),('ef', array ['ee','ff']);

查看

postgres=# select * from ware
postgres-# ;
 spu |      sku      
-----+---------------
 123 | {111,222,333}
 456 | {444,555,666}
 789 | {777,888,999}
 cd  | {cc,dd}
 ef  | {ee,ff}
(5 rows)

检索

terms

postgres=# select * from ware where sku && array['111', 'cc'];

更新

一个数组值可以被整个替换：

UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}'
    WHERE name = 'Carol';

或者使用ARRAY表达式语法：

UPDATE sal_emp SET pay_by_quarter = ARRAY[25000,25000,27000,27000]
    WHERE name = 'Carol';

一个数组也可以在一个元素上被更新：

UPDATE sal_emp SET pay_by_quarter[4] = 15000
    WHERE name = 'Bill';

或者在一个切片上被更新：

UPDATE sal_emp SET pay_by_quarter[1:2] = '{27000,27000}'
    WHERE name = 'Carol';

也可以使用省略*lower-bound*或者 *upper-bound*的切片语法，但是只能用于更新一个不是 NULL 或者零维的数组值（否则无法替换现有的下标界线）。

JSON

创建

含有 json 类型的表

postgres=# create table jsonTest (spu text primary key, "check" jsonb);

查看

postgres=# \d jsontest
             Table "public.jsontest"
 Column | Type  | Collation | Nullable | Default 
--------+-------+-----------+----------+---------
 spu    | text  |           | not null | 
 check  | jsonb |           |          | 
Indexes:
    "jsontest_pkey" PRIMARY KEY, btree (spu)

存储

⚠️注意

-- 简单标量/基本值
-- 基本值可以是数字、带引号的字符串、true、false或者null
SELECT '5'::json;

-- 有零个或者更多元素的数组（元素不需要为同一类型）
SELECT '[1, 2, "foo", null]'::json;

-- 包含键值对的对象
-- 注意对象键必须总是带引号的字符串
SELECT '{"bar": "baz", "balance": 7.77, "active": false}'::json;

-- 数组和对象可以被任意嵌套
SELECT '{"foo": [true, "bar"], "tags": {"a": 1, "b": null}}'::json;

插入一行数据

postgres=# insert into jsontest values ('123', '{"a": true, "b": false, "c": [1, "d", false, null]}'::jsonb),('234', '{"a": false, "b": true, "c": [2, "d", true, null]}'::jsonb);

postgres=#  select * from jsontest;
 spu |                        check                        
-----+-----------------------------------------------------
 123 | {"a": true, "b": false, "c": [1, "d", false, null]}
 234 | {"a": false, "b": true, "c": [2, "d", true, null]}
(2 rows)

更新

update jsontest set "check" = (jsonb_set("check"::jsonb,'{b}','false'::jsonb)) where spu = '234';

查看结果，已经改变 check 中 key = b 的 value

postgres=# select * from jsontest where spu = '234';
 spu |                        check                        
-----+-----------------------------------------------------
 234 | {"a": false, "b": false, "c": [2, "d", true, null]}
(1 row)

检索

GIN 索引 JSON-INDEXING, jsonb的默认 GIN 操作符类支持使用@>、 ?、?&以及?|操作符的查询

postgres=# select * from jsontest where "check" @> '{"a": true}'::jsonb;
 spu |                        check                        
-----+-----------------------------------------------------
 123 | {"a": true, "b": false, "c": [1, "d", false, null]}
(1 row)

postgres=# select * from jsontest where "check" @> '{"a": false}'::jsonb;
 spu |                        check                        
-----+-----------------------------------------------------
 234 | {"a": false, "b": false, "c": [2, "d", true, null]}
(1 row)

postgres=# select * from jsontest where "check" -> 'c' @> '[1]'::jsonb;
 spu |                        check                        
-----+-----------------------------------------------------
 123 | {"a": true, "b": false, "c": [1, "d", false, null]}
(1 row)

GORM

初始化

定义 model

type ware struct {
	Spu string   `json:"spu" gorm:"column:spu;primaryKey;type:text;"`
	Sku []string `json:"sku" gorm:"column:sku;type:text[];"`
}

func (ware) TableName() string {
	return "ware_arr"
}

type json struct {
	Spu   string `json:"spu" gorm:"column:spu;primaryKey;type:text;"`
	Check check  `json:"check" gorm:"column:check;type:jsonb"`
}

func (json) TableName() string {
	return "ware_json"
}

type check struct {
	A bool          `json:"a"`
	B bool          `json:"b"`
	C []interface{} `json:"c"`
}

make PostgreSQL db

func newDB(t *testing.T) *gorm.DB {
	dsn := "host=localhost user=postgres password=1786556 dbname=postgres port=5432 sslmode=disable TimeZone=Asia/Shanghai"
	db, err := gorm.Open(postgres.Open(dsn), &gorm.Config{})
	if err != nil {
		t.Fatal(err)
	}
	if err = db.AutoMigrate(ware{}, json{}); err != nil {
		t.Fatal(err)
	}
	return db
}

Array

存入

data := make([]wareArr, 0)
	data = append(data, wareArr{
		Spu: "12",
		Sku: []string{"111","222"},
	},wareArr{
		Spu: "34",
		Sku: []string{"333","444"}})

	if err := db.Model(&wareArr{}).Save(&data).Error; err != nil {
		t.Fatal(err)
	}

wareArr

检索

if err := db.Model(&wareArr{}).Where("sku && ?", pq.StringArray{"222"}).Find(&arr).Error; err != nil {
		t.Fatal(err)
	}
	spew.Dump(arr)

=== RUN   Test_WareArr
([]test.wareArr) (len=1 cap=20) {
 (test.wareArr) {
  Spu: (string) (len=2) "12",
  Sku: (pq.StringArray) (len=2 cap=2) {
   (string) (len=3) "111",
   (string) (len=3) "222"
  }
 }
}
--- PASS: Test_WareArr (0.10s)
PASS

Json(b)

存入

data := make([]wareJson, 0)
	data = append(data, wareJson{
		Spu: "111",
		Check: map[string]interface{}{
			"a": 1,
			"b": true,
			"c": []interface{}{1, true, "c"},
			"d": true,
		},
	},wareJson{
		Spu: "222",
		Check: map[string]interface{}{
			"a": 2,
			"b": false,
			"c": []interface{}{2, false, "cc"},
			"d": false,
		},
	},wareJson{
		Spu: "333",
		Check: map[string]interface{}{
			"a": 2,
			"b": false,
			"c": []interface{}{2, false, "cc"},
		},
	})
	if err := pg.Model(&wareJson{}).Save(&data).Error; err != nil {
		t.Fatal(err)
	}

wareJSONB

检索

Equals

pg.Model(&wareJson{}).Find(&jsonArr, datatypes.JSONQuery("check").Equals(2,"a"))
spew.Dump(jsonArr)

=== RUN   Test_WareJsonb
([]test.wareJson) (len=2 cap=20) {
 (test.wareJson) {
  Spu: (string) (len=3) "222",
  Check: (datatypes.JSONMap) (len=4) {
   (string) (len=1) "c": ([]interface {}) (len=3 cap=4) {
    (float64) 2,
    (bool) false,
    (string) (len=2) "cc"
   },
   (string) (len=1) "d": (bool) false,
   (string) (len=1) "a": (float64) 2,
   (string) (len=1) "b": (bool) false
  }
 },
 (test.wareJson) {
  Spu: (string) (len=3) "333",
  Check: (datatypes.JSONMap) (len=3) {
   (string) (len=1) "a": (float64) 2,
   (string) (len=1) "b": (bool) false,
   (string) (len=1) "c": ([]interface {}) (len=3 cap=4) {
    (float64) 2,
    (bool) false,
    (string) (len=2) "cc"
   }
  }
 }
}
--- PASS: Test_WareJsonb (0.16s)
PASS

HasKey

pg.Model(&wareJson{}).Find(&jsonArr, datatypes.JSONQuery("check").HasKey("d"))

=== RUN   Test_WareJsonb
([]test.wareJson) (len=2 cap=20) {
 (test.wareJson) {
  Spu: (string) (len=3) "111",
  Check: (datatypes.JSONMap) (len=4) {
   (string) (len=1) "a": (float64) 1,
   (string) (len=1) "b": (bool) true,
   (string) (len=1) "c": ([]interface {}) (len=3 cap=4) {
    (float64) 1,
    (bool) true,
    (string) (len=1) "c"
   },
   (string) (len=1) "d": (bool) true
  }
 },
 (test.wareJson) {
  Spu: (string) (len=3) "222",
  Check: (datatypes.JSONMap) (len=4) {
   (string) (len=1) "a": (float64) 2,
   (string) (len=1) "b": (bool) false,
   (string) (len=1) "c": ([]interface {}) (len=3 cap=4) {
    (float64) 2,
    (bool) false,
    (string) (len=2) "cc"
   },
   (string) (len=1) "d": (bool) false
  }
 }
}
--- PASS: Test_WareJsonb (0.12s)
PASS