PostgreSql中的JSON数据类型
PostgreSQL 提供了两种 JSON 数据类型：JSON 以及 JSONB。这两种类型主要的区别在于数据存储格式，JSONB 使用二进制格式存储数据，更易于处理。
PostgreSQL 推荐优先选择 JSONB 数据类型。
两种数据类型之间的区别：
| 功能 | JSON | JSONB |
|---|---|---|
| 存储格式 | 字符串原文存储 | 解析后的二进制 |
| 全文索引 | 不支持 | 支持 |
| 保留空白符 | 保留 | 不保留 |
| 保留键的顺序 | 保留 | 不保留 |
| 保留重复键 | 保留 | 不保留 |
由于存储格式的不同，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类型，创建索引会报错，这个差异对应了上边表格
以上基本是暂时了解到的内容，欢迎交流斧正！！！
原文地址：https://blog.csdn.net/sinat_23931991/article/details/140350965
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！