postgreSQL中的JSON

创建时间: 2025-10-26 19:47:38修改时间: 2025-10-28 14:58:39

前言

一提到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