postgreSQL中的JSON
前言
一提到json类型的数据库,肯定第一时间想到mongoDB,但是mangoDB是非关系型的数据库,目前我还没有了解过有什么项目是纯用非关系型数据库的,都是关系型数据库和非关系型数据库结合起来。这样有一个弊端,就是需要在服务器额外安装一个mongodb,服务器的内存造成了不小的负担(当然可以在不同的服务器上部署mongoDB)但是部署时肯定不如只用一个postgreSQL来得方便,所以我打算今天研究一下postgreSQL的json类型,以后有时间我当前博客上用到的MongoDB全部用postgreSQL代替掉。
json与jsonb
postgreSQL提供了两种储存json的类型,分别为json和jsonb,下面是我百度到的结果:
JSON
存储方式:JSON 类型将输入的数据作为文本进行存储,保留了所有的空格、重复键和顺序。
读取速度:由于每次读取时都需要解析数据,因此读取速度较慢。
写入速度:写入速度较快,因为不需要进行额外的处理。
JSONB
存储方式:JSONB 类型将输入的数据解析后以二进制格式存储,去除了不必要的空格和重复键,顺序可能与输入不同。
读取速度:由于数据已经解析,读取速度较快。
写入速度:写入速度较慢,因为需要在存储前进行解析。
综上所述,jsonb在查询时候的效率更高,但写入效率较慢,json则与之相反。
json路径变量
下面案例中用到的测试数据
{
"guid": "9c36adc1-7fb5-4d5b-83b4-90356a46061a",
"name": "Angela Barton",
"tags": [
"enim",
"aliquip",
"qui"
],
"address": "178 Howard Place, Gulf, Washington, 702",
"company": "Magnafone",
"latitude": 19.793713,
"is_active": true,
"longitude": 86.513373,
"registered": "2009-11-07T08:53:22 +08:00"
}
| 运算符 | 描述 | 例子 |
|---|---|---|
| $ | 表示正在查询的 JSON 值(上下文项)的变量。 | select data from table where data @? '$.guid' |
| $varname | 命名变量。它的值可以通过几个 JSON 处理函数的参数变量来设置;有关详细信息,请参见表 9.51 | 就是一个变量 |
| @ | 表示过滤器表达式中路径计算结果的变量。 | select data from table where data @? '$.tags[*] ? (@ == "enim")'; |
查询运算符
| 运算符 | 描述 | 例子 |
|---|---|---|
| -> | 提取 JSON 数组的第 n 个元素(数组元素从零开始索引,但负整数从末尾开始计数) | select data->'tags'->0 from table; |
| #> | 在指定路径处提取 JSON 子对象,其中路径元素可以是字段键或数组索引 | select data #> '{tags, 0}' from table; |
条件判断运算符
| 运算符 | 返回类型 | 描述 | 例子 |
|---|---|---|---|
| @> | boolean | 第一个 JSON 值是否包含第二个值?(更多信息参照官方文档) | select data from table where data @> '{"guid": "9c36adc1-7fb5-4d5b-83b4-90356a46061a", "name": "Angela Barton"}' |
| <@ | boolean | 第一个 JSON 值是否包含在第二个 JSON 值中? | 和第一个相反,右值必须包含左值的所有value |
| ? | boolean | 文本字符串是否作为 JSON 值中的顶级键或数组元素存在? | select data from table where data ? 'tags' |
| ?\ | boolean | 文本数组中是否有任何字符串作为顶级键或数组元素存在 ? | |
| ?& | boolean | 文本数组中的所有字符串是否都作为顶级键或数组元素存在? | select data from table where data ?& array['guid', 'tags'] |
| \ | \ | jsonb | |
| - | jsonb | 从 JSON 对象中删除键(及其值),或从 JSON 数组中删除匹配的字符串值。 | select data - 'guid' from table |
| - text[] | jsonb | 从左作数中删除所有匹配的键或数组元素。 | select data - '{guid,name}'::text[] from table |
| - integer | jsonb | 删除具有指定索引的数组元素(负整数从末尾开始计数)。如果 JSON 值不是数组,则引发错误。 | select '["a","b"]'::jsonb - 1 |
| #- | jsonb | 删除指定路径上的字段或数组元素,其中路径元素可以是字段键或数组索引。 | select data#-'{tags, 0}'::text[] from table |
| @? | boolean | JSON 路径是否返回指定 JSON 值的任何项目?(这仅对 SQL 标准 JSON 路径表达式有用,而不是谓词检查表达式,因为它们总是返回一个值) | select data from table WHERE data @? '$.latitude ? (@ > 19)' |
| @@ | boolean | 返回指定 JSON 值的 JSON 路径谓词检查结果。(这仅对谓词检查表达式有用,而不是 SQL 标准 JSON 路径表达式,因为如果路径结果不是单个布尔值,它将返回NULL) | select data from table where data @@ '$.tags[*] == "enim"' |
GIN
在posgreqsql中如果不添加索引查询貌似比mongoDB慢很多,正是因为有GIN索引,才能让Postgresql的查询和mongoDB不相上下,这是postgreSQL官方对GIN索引的解释:GIN 索引可用于有效搜索大量文档(基准)中出现的键或键/值对。提供了两个 GIN“操作员类”,提供不同的性能和灵活性权衡。
-- 为json类型的数据创建GIN索引
CREATE INDEX idxgin ON table_name USING GIN (data);
-- 为tags字段创建GIN索引
CREATE INDEX idxgin_tags ON jj USING GIN ((data->'tags'));
postgreSQL中的json类型数据的基础操作就到这里完结了。等我有空一定用postgreSQL重构掉我博客上用的mongoDB
