PostgreSql中的JSON数据类型

PostgreSQL 提供了两种 JSON 数据类型：JSON 以及 JSONB。这两种类型主要的区别在于数据存储格式，JSONB 使用二进制格式存储数据，更易于处理。

PostgreSQL 推荐优先选择 JSONB 数据类型。

两种数据类型之间的区别：
功能JSONJSONB存储格式字符串原文存储解析后的二进制全文索引不支持支持保留空白符保留不保留保留键的顺序保留不保留保留重复键保留不保留
由于存储格式的不同，JSONB 输入时稍微慢一些（需要转换），但是查询时快很多。

接下来的内容主要使用 JSONB 数据类型，但是大部分功能也可以使用 JSON 数据类型。

一、创建并插入数据

CREATE TABLE product (
  id INTEGER NOT NULL PRIMARY KEY,
  name VARCHAR(100),
  attr1 JSONB,
  attr2 JSON
);

1. 插入数据：

INSERT INTO product
VALUES
    ( 1, '阿莫西林', 
    '{
        "code": "100011",
        "price": 18,
        "uom": "盒",
        "specification": "10粒/盒",
        "price": 18.00
    }',
    '{
        "code": "100011",
        "price": 18,
        "uom": "盒",
        "specification": "10粒/盒",
        "price": 18.00
    }'
    );
    
INSERT INTO product
VALUES
    ( 2, '阿司匹林', 
    '{
        "code": "100013",
        "price": 28,
        "uom": "盒",
        "specification": "20粒/盒",
        "price": 28.00
    }',
    '{
        "code": "100013",
        "price": 28,
        "uom": "盒",
        "specification": "20粒/盒",
        "price": 28.00
    }'
    );

2. 查询数据：

从红框中内容可以看出来两种类型的区别：JSON保留原格式，JSONB不做保留，具体见上边的表格。

3. 格式化方法插入：

jsonb_build_object、json_build_object 函数可以通过一系列输入创建JSON 对象

    
INSERT INTO product
VALUES
    ( 3, '农夫山泉', jsonb_build_object ( 'code', '100015', 'price', 3 ), 
                    json_build_object ( 'code', '100015', 'price', 3 ) );

其他常用的构建 JSON 数据的函数如下：

• to_json 以及 to_jsonb
• array_to_json
• row_to_json
• json_build_array 以及 jsonb_build_array
• json_object 以及 jsonb_object

具体操作可以参考：PostgreSQL操作json/jsonb方法详解_PostgreSQL_脚本之家

这些方法看完还是觉得直接输入来的简单方便，也可能我没有领略到精髓。

二、查询JSON字段及属性（下边都以JSONB的格式）

1. 查询JSON字段：

product=# select * from product where id =3 ;
 id |   name   |             attr1              |              attr2
----+----------+--------------------------------+----------------------------------
  3 | 农夫山泉 | {"code": "100015", "price": 3} | {"code" : "100015", "price" : 3}
(1 row)

product=# select name, attr1, attr2 from product where id =3 ;
   name   |             attr1              |              attr2
----------+--------------------------------+----------------------------------
 农夫山泉 | {"code": "100015", "price": 3} | {"code" : "100015", "price" : 3}
(1 row)

2. 获取JSON单个属性

查询attr1下的code属性，

运算符 -> 可以通过指定节点的键获取相应的数据。这种方法返回的数据仍然是 JSON 类型，使用双引号包含。

如果想要以字符串形式返回节点中的数据值，可以使用运算符 ->>。如：

如果查询的 JSON 节点不存在，将会返回空值：

3. 获取JSON数组属性

第二个 -> 运算符返回了该属性中的第 1 个数组元素（数组下标从 0 开始）。

注意这里第一个不能使用“ ->>”，转成字符串之后就没法再往下获取元素了。

运算符 #> 以及 #>> 可以通过指定 JSON 节点的路径获取嵌套属性，路径可以包含键的名称或者数组元素下标，返回类型分别为 JSON 和字符串。

4. 基于JSON数据的过滤

直接以属性作为条件进行查询：这种无法根据属性进行匹配，

如果要匹配就需要用以下方式：

需要注意的是->是json格式，-->是字符，需要注意匹配，如元素属性为数字，则需要转换后对比：

5. JSON 转换为数据行

PostgreSQL 支持将 JSON 字段转换为数据行格式。例如，jsonb_each 函数可以将每个键值对转换为一个记录：

与此类似的函数还有：

• json_each 以及 json_each_text
• json_array_elements 以及 json_array_elements_text
• jsonb_array_elements 以及 jsonb_array_elements_text

6. 获取所有JSON的key

7. 判断JSON属性是否存在

PostgreSQL 还提供了一些用于判断 JSON 属性是否存在的运算符，例如 ? 运算符。

三、更新 JSON 字段数据

1. || 运算符

使用 UPDATE 语句更新 JSON 字段时，可以通过 || 运算符将新的键值增加到已有 JSON 数据。例如：

JSONB格式没有顺序

2. jsonb_insert 方法

利用 jsonb_insert 方法，例如：

3. jsonb_set 方法

如果想要更新已有键的数值，可以使用 jsonb_set 函数。例如：

如果没有key则新增：

四、删除 JSON 字段数据

1. 删除整个 JSON 字段数据可以简单地将其设置为 NULL，例如：

2. 删除 JSON 字段中的某个属性可以使用 - 运算符，例如：

当没有元素属性时，不会删除报错。

五、全文索引

PostgreSQL 提供了支持 JSON 字段的全文索引，可以优化查询的性能。这种索引的类型为 GIN（通用倒排索引），通常用于搜索引擎。

 CREATE INDEX index_product_att1 ON product USING GIN(attr1);

attr1时JSONB类型，而atttr2是JSON类型，创建索引会报错，这个差异对应了上边表格

以上基本是暂时了解到的内容，欢迎交流斧正！！！