本文主要介绍 PostgreSQL特性 JSON 和 JSONB 数据类型
JSON 数据类型让我们可以在 PostgreSQL 中存储 JSON 格式的数据，使得 PostgreSQL 不仅能处理结构化数据，还能处理半结构化或非结构化数据。
两者主要的实际区别之一是效率。json数据类型存储输入文本的精准拷贝，处理函数必须在每 次执行时必须重新解析该数据。而jsonb数据被存储在一种分解好的 二进制格式中，它在输入时要稍慢一些，因为需要做附加的转换。但是 jsonb在处理时要快很多，因为不需要解析。jsonb也支 持索引，这也是一个令人瞩目的优势。
通常，除非有特别特殊的需要（例如遗留的对象键顺序假设），大多数应用应该 更愿意把 JSON 数据存储为jsonb。
创建表结构
接着通过一个例子来学习使用Json数据类型。 例如，假设用户表 users ，其中data 字段设置为 JSONB 类型。
CREATE TABLE users (
id SERIAL PRIMARY KEY,
data jsonb
);
插入JSON 数据
插入一个JSON 数据：
INSERT INTO users (data) VALUES ('{"name": "John", "email": "john@example.com", "roles": ["User", "Admin"]}');
查询JSON 数据
-> ->> 运算符
- -> 运算符用于获取 JSON 对象的字段（返回的还是 JSON），
- ->> 运算符用于获取 JSON 对象的字段（返回的是文本）。
查询data中是否存在key: email, value : john@example.com的值的sql分别是：
SELECT data -> 'name' AS name FROM users WHERE data ->> 'email' = 'john@example.com';
SELECT data ->> 'name' AS name FROM users WHERE data ->> 'email' = 'john@example.com';
@> 运算符： 是否包含和存在
那么上面的查询sql等同于:
SELECT * FROM users WHERE data @> '{"email": "john@example.com"}'::jsonb;
该运算符的更多用法如下:
-- 简单的标量/基本值只包含相同的值：
SELECT '"foo"'::jsonb @> '"foo"'::jsonb;
-- 右边的数字被包含在左边的数组中：
SELECT '[1, 2, 3]'::jsonb @> '[1, 3]'::jsonb;
-- 数组元素的顺序没有意义，因此这个例子也返回真：
SELECT '[1, 2, 3]'::jsonb @> '[3, 1]'::jsonb;
-- 重复的数组元素也没有关系：
SELECT '[1, 2, 3]'::jsonb @> '[1, 2, 2]'::jsonb;
-- 右边具有一个单一键值对的对象被包含在左边的对象中：
SELECT '{"product": "PostgreSQL", "version": 9.4, "jsonb": true}'::jsonb @> '{"version": 9.4}'::jsonb;
-- 右边的数组不会被认为包含在左边的数组中，
-- 即使其中嵌入了一个相似的数组：
SELECT '[1, 2, [1, 3]]'::jsonb @> '[1, 3]'::jsonb; -- 得到假
-- 但是如果同样也有嵌套，包含就成立：
SELECT '[1, 2, [1, 3]]'::jsonb @> '[[1, 3]]'::jsonb;
-- 类似的，这个例子也不会被认为是包含：
SELECT '{"foo": {"bar": "baz"}}'::jsonb @> '{"bar": "baz"}'::jsonb; -- 得到假
-- 包含一个顶层键和一个空对象：
SELECT '{"foo": {"bar": "baz"}}'::jsonb @> '{"foo": {}}'::jsonb;
更新JSON 数据
使用 jsonb_set 函数。这个函数的基本语法是
jsonb_set(target jsonb, path text[], new_value jsonb, [create_missing boolean])
假设我们要更新上面 users 表中 data 字段的 email
UPDATE users
SET data = jsonb_set(data, '{email}', '"new-email@example.com"', true)
WHERE data ->> 'name' = 'John';
更多 JSON 函数和运算符
- json_array_length(json)：返回 JSON 数组的长度。
- json_extract_path(json, text[])：给定一个路径（以数组形式），提取 JSON 对象中的数据。
更多参考 www.postgres.cn/docs/14/fun…
小结
PostgreSQL的JSON数据类型特性，可以在关系型数据库中存储和查询非结构化数据，拥有了类似MongoDB处理复杂、多变的数据模型能力。
