— 创建JSON测试表
CREATE TABLE fgedu_json_test (
id SERIAL PRIMARY KEY,
json_data JSON,
jsonb_data JSONB
);

— 插入相同的JSON数据
INSERT INTO fgedu_json_test (json_data, jsonb_data) VALUES
(‘{“name”: “风哥1号”, “age”: 30, “city”: “北京”}’, ‘{“name”: “风哥1号”, “age”: 30, “city”: “北京”}’);

— 比较存储差异
SELECT
json_data,
jsonb_data,
pg_column_size(json_data) AS json_size,
pg_column_size(jsonb_data) AS jsonb_size
FROM fgedu_json_test;

执行结果：

json_data | jsonb_data | json_size | jsonb_size
———————————+————————-+———–+————
{“name”: “风哥1号”, “age”: 30, “city”: “北京”} | {“age”: 30, “name”: “风哥1号”, “city”: “北京”} | 46 | 76
(1 row)

JSONB会重新排序键：

— JSONB会重新排序键（按字母顺序）
INSERT INTO fgedu_json_test (json_data, jsonb_data) VALUES
(‘{“z”: 1, “a”: 2, “m”: 3}’, ‘{“z”: 1, “a”: 2, “m”: 3}’);

SELECT json_data, jsonb_data FROM fgedu_json_test WHERE id = 2;

执行结果：

json_data | jsonb_data
—————-+—————-
{“z”: 1, “a”: 2, “m”: 3} | {“a”: 2, “m”: 3, “z”: 1}
(1 row)

— 创建产品表
CREATE TABLE fgedu_products_json (
product_id SERIAL PRIMARY KEY,
product_name VARCHAR(100),
attributes JSONB,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

— 插入简单JSON数据
INSERT INTO fgedu_products_json (product_name, attributes) VALUES
(‘笔记本电脑’, ‘{“brand”: “ThinkPad”, “cpu”: “i7-12700H”, “ram”: “16GB”, “storage”: “512GB SSD”}’),
(‘无线鼠标’, ‘{“brand”: “Logitech”, “dpi”: 16000, “wireless”: true, “battery”: “AA”}’),
(‘机械键盘’, ‘{“brand”: “Cherry”, “switches”: “MX Brown”, “layout”: “TKL”, “rgb”: true}’);

— 插入嵌套JSON数据
INSERT INTO fgedu_products_json (product_name, attributes) VALUES
(‘智能手机’, ‘{
“brand”: “iPhone”,
“model”: “15 Pro”,
“specs”: {
“cpu”: “A17 Pro”,
“ram”: “8GB”,
“storage”: “256GB”
},
“colors”: [“黑色”, “白色”, “蓝色”],
“features”: [“5G”, “Face ID”, “MagSafe”]
}’);

— 插入数组形式的JSON数据
INSERT INTO fgedu_products_json (product_name, attributes) VALUES
(‘显示器套装’, ‘{
“monitor”: {“brand”: “Dell”, “size”: “27 inch”, “resolution”: “4K”},
“accessories”: [
{“type”: “支架”, “adjustable”: true},
{“type”: “HDMI线”, “length”: “2m”}
]
}’);

— 查询数据
SELECT product_id, product_name, attributes FROM fgedu_products_json;

执行结果：

product_id | product_name | attributes
————-+————–+—————————————————————————————————-
1 | 笔记本电脑 | {“cpu”: “i7-12700H”, “ram”: “16GB”, “brand”: “ThinkPad”, “storage”: “512GB SSD”}
2 | 无线鼠标 | {“dpi”: 16000, “brand”: “Logitech”, “battery”: “AA”, “wireless”: true}
3 | 机械键盘 | {“rgb”: true, “layout”: “TKL”, “brand”: “Cherry”, “switches”: “MX Brown”}
4 | 智能手机 | {“colors”: [“黑色”, “白色”, “蓝色”], “model”: “15 Pro”, “brand”: “iPhone”, “features”: [“5G”, “Face
ID”, “MagSafe”], “specs”: {“cpu”: “A17 Pro”, “ram”: “8GB”, “storage”: “256GB”}}
5 | 显示器套装 | {“monitor”: {“size”: “27 inch”, “brand”: “Dell”, “resolution”: “4K”}, “accessories”: [{“type”:
“支架”, “adjustable”: true}, {“type”: “HDMI线”, “length”: “2m”}]}
(5 rows)

— 提取JSON字段
SELECT
product_name,
attributes -> ‘brand’ AS brand_json,
attributes ->> ‘brand’ AS brand_text,
attributes -> ‘cpu’ AS cpu
FROM fgedu_products_json
WHERE attributes ? ‘brand’;

执行结果：

product_name | brand_json | brand_text | cpu
————–+————–+————+—————-
笔记本电脑 | “ThinkPad” | ThinkPad | “i7-12700H”
无线鼠标 | “Logitech” | Logitech |
机械键盘 | “Cherry” | Cherry |
智能手机 | “iPhone” | iPhone |
显示器套装 | | |
(5 rows)

查询嵌套JSON：

— 查询嵌套JSON
SELECT
product_name,
attributes -> ‘specs’ -> ‘cpu’ AS cpu,
attributes -> ‘specs’ ->> ‘ram’ AS ram,
attributes #> ‘{specs,storage}’ AS storage,
attributes #>> ‘{monitor,size}’ AS monitor_size
FROM fgedu_products_json
WHERE attributes -> ‘specs’ IS NOT NULL OR attributes -> ‘monitor’ IS NOT NULL;

执行结果：

product_name | cpu | ram | storage | monitor_size
————–+————+——-+————-+————–
智能手机 | “A17 Pro” | 8GB | “256GB” |
显示器套装 | | | | 27 inch
(2 rows)

查询JSON数组：

— 查询JSON数组
SELECT
product_name,
attributes -> ‘colors’ AS colors,
attributes -> ‘colors’ -> 0 AS first_color,
attributes -> ‘colors’ ->> 1 AS second_color,
jsonb_array_length(attributes -> ‘colors’) AS colors_count
FROM fgedu_products_json
WHERE attributes ? ‘colors’;

执行结果：

product_name | colors | first_color | second_color | colors_count
————–+—————————+————-+————–+————–
智能手机 | [“黑色”, “白色”, “蓝色”] | “黑色” | 白色 | 3
(1 row)

JSON条件查询：

— JSON条件查询
SELECT product_name, attributes ->> ‘brand’ AS brand
FROM fgedu_products_json
WHERE attributes @> ‘{“brand”: “ThinkPad”}’;

执行结果：

product_name | brand
————–+———-
笔记本电脑 | ThinkPad
(1 row)

— 检查是否包含某个键
SELECT product_name, attributes
FROM fgedu_products_json
WHERE attributes ? ‘wireless’;

— 检查是否包含任意一个键
SELECT product_name, attributes
FROM fgedu_products_json
WHERE attributes ?| ARRAY[‘wireless’, ‘rgb’];

— 检查是否包含所有键
SELECT product_name, attributes
FROM fgedu_products_json
WHERE attributes ?& ARRAY[‘brand’, ‘model’];

执行结果：

product_name | attributes
————–+————————————————————————–
无线鼠标 | {“dpi”: 16000, “brand”: “Logitech”, “battery”: “AA”, “wireless”: true}
(1 row)

product_name | attributes
————–+———————————————————————————
无线鼠标 | {“dpi”: 16000, “brand”: “Logitech”, “battery”: “AA”, “wireless”: true}
机械键盘 | {“rgb”: true, “layout”: “TKL”, “brand”: “Cherry”, “switches”: “MX Brown”}
(2 rows)

product_name | attributes
————–+—————————————————————————————————-
智能手机 | {“colors”: [“黑色”, “白色”, “蓝色”], “model”: “15 Pro”, “brand”: “iPhone”, “features”: [“5G”, “Face ID”,
“MagSafe”], “specs”: {“cpu”: “A17 Pro”, “ram”: “8GB”, “storage”: “256GB”}}
(1 row)

— 更新JSON字段
UPDATE fgedu_products_json
SET attributes = jsonb_set(attributes, ‘{ram}’, ‘”32GB”‘)
WHERE product_name = ‘笔记本电脑’;

SELECT product_name, attributes FROM fgedu_products_json WHERE product_name = ‘笔记本电脑’;

执行结果：

product_name | attributes
————–+————————————————————————————
笔记本电脑 | {“cpu”: “i7-12700H”, “ram”: “32GB”, “brand”: “ThinkPad”, “storage”: “512GB SSD”}
(1 row)

添加新字段：

— 添加新字段
UPDATE fgedu_products_json
SET attributes = attributes || ‘{“price”: 8999.00, “warranty”: “3年”}’
WHERE product_name = ‘笔记本电脑’;

SELECT product_name, attributes FROM fgedu_products_json WHERE product_name = ‘笔记本电脑’;

执行结果：

product_name | attributes
————–+———————————————————————————————————————
笔记本电脑 | {“cpu”: “i7-12700H”, “ram”: “32GB”, “price”: 8999.00, “brand”: “ThinkPad”, “warranty”: “3年”,
“storage”: “512GB SSD”}
(1 row)

删除字段：

— 删除字段
UPDATE fgedu_products_json
SET attributes = attributes – ‘warranty’
WHERE product_name = ‘笔记本电脑’;

— 删除嵌套字段
UPDATE fgedu_products_json
SET attributes = attributes #- ‘{specs,ram}’
WHERE product_name = ‘智能手机’;

SELECT product_name, attributes FROM fgedu_products_json WHERE product_name IN (‘笔记本电脑’, ‘智能手机’);

执行结果：

product_name | attributes
————–+—————————————————————————————————-
笔记本电脑 | {“cpu”: “i7-12700H”, “ram”: “32GB”, “price”: 8999.00, “brand”: “ThinkPad”, “storage”: “512GB SSD”}
智能手机 | {“colors”: [“黑色”, “白色”, “蓝色”], “model”: “15 Pro”, “brand”: “iPhone”, “features”: [“5G”, “Face ID”,
“MagSafe”], “specs”: {“cpu”: “A17 Pro”, “storage”: “256GB”}}
(2 rows)

— 创建GIN索引（支持@>、?、?|、?&操作符）
CREATE INDEX idx_fgedu_products_attributes ON fgedu_products_json USING GIN (attributes);

— 创建特定字段的索引
CREATE INDEX idx_fgedu_products_brand ON fgedu_products_json ((attributes ->> ‘brand’));

— 查看索引
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = ‘fgedu_products_json’;

执行结果：

indexname | indexdef
———————————-+———————————————————————————————
idx_fgedu_products_attributes | CREATE INDEX idx_fgedu_products_attributes ON fgedu_products_json USING gin
(attributes)
idx_fgedu_products_brand | CREATE INDEX idx_fgedu_products_brand ON fgedu_products_json USING btree
(((attributes ->> ‘brand’::text)))
(2 rows)

索引使用测试：

— 测试索引使用
EXPLAIN ANALYZE
SELECT * FROM fgedu_products_json WHERE attributes @> ‘{“brand”: “ThinkPad”}’;

执行结果：

Bitmap Heap Scan on fgedu_products_json (cost=12.05..16.06 rows=1 width=100) (actual time=0.015..0.016
rows=1 loops=1)
Recheck Cond: (attributes @> ‘{“brand”: “ThinkPad”}’::jsonb)
-> Bitmap Index Scan on idx_fgedu_products_attributes (cost=0.00..12.05 rows=1 width=0) (actual
time=0.010..0.010 rows=1 loops=1)
Index Cond: (attributes @> ‘{“brand”: “ThinkPad”}’::jsonb)
Planning Time: 0.089 ms
Execution Time: 0.035 ms
(6 rows)

— jsonb_each: 展开JSON对象
SELECT
product_name,
key,
value
FROM fgedu_products_json,
jsonb_each(attributes)
WHERE product_name = ‘无线鼠标’;

执行结果：

product_name | key | value
————–+———-+————
无线鼠标 | dpi | 16000
无线鼠标 | brand | “Logitech”
无线鼠标 | battery | “AA”
无线鼠标 | wireless | true
(4 rows)

JSON数组函数：

— jsonb_array_elements: 展开JSON数组
SELECT
product_name,
jsonb_array_elements(attributes -> ‘colors’) AS color
FROM fgedu_products_json
WHERE attributes ? ‘colors’;

执行结果：

product_name | color
————–+———-
智能手机 | “黑色”
智能手机 | “白色”
智能手机 | “蓝色”
(3 rows)

JSON构建函数：

— jsonb_build_object: 构建JSON对象
SELECT jsonb_build_object(
‘name’, ‘风哥1号’,
‘age’, 30,
‘skills’, ARRAY[‘PostgreSQL’, ‘Python’, ‘Linux’]
) AS person_info;

执行结果：

person_info
—————————————————————–
{“age”: 30, “name”: “风哥1号”, “skills”: [“PostgreSQL”, “Python”, “Linux”]}
(1 row)

— jsonb_agg: 聚合为JSON数组
SELECT
product_name,
jsonb_agg(key) AS all_keys
FROM fgedu_products_json,
jsonb_object_keys(attributes) AS key
GROUP BY product_name
LIMIT 3;

执行结果：

风哥提示：更多视频教程www.fgedu.net.cn

product_name | all_keys
————–+————————————-
笔记本电脑 | [“cpu”, “ram”, “price”, “brand”, “storage”]
无线鼠标 | [“dpi”, “brand”, “battery”, “wireless”]
机械键盘 | [“rgb”, “layout”, “brand”, “switches”]
(3 rows)

JSON类型转换：

— JSON类型转换
SELECT
‘{“a”: 1, “b”: 2}’::JSONB AS jsonb_val,
‘{“a”: 1, “b”: 2}’::JSONB -> ‘a’ AS a_json,
(‘{“a”: 1, “b”: 2}’::JSONB ->> ‘a’)::INTEGER AS a_int;

执行结果：

jsonb_val | a_json | a_int
—————-+——–+——-
{“a”: 1, “b”: 2} | 1 | 1
(1 row)

— jsonb_path_query: 使用JSONPath查询
SELECT
product_name,
jsonb_path_query(attributes, ‘$.specs.*’) AS spec_values
FROM fgedu_products_json
WHERE attributes -> ‘specs’ IS NOT NULL;

执行结果：

学习交流加群风哥QQ113257174

product_name | spec_values
————–+—————
智能手机 | “A17 Pro”
智能手机 | “256GB”
(2 rows)

— jsonb_path_query_array: 返回数组
SELECT
product_name,
jsonb_path_query_array(attributes, ‘$.colors[*]’) AS all_colors
FROM fgedu_products_json
WHERE attributes ? ‘colors’;

执行结果：

product_name | all_colors
————–+————————-
智能手机 | [“黑色”, “白色”, “蓝色”]
(1 row)

— jsonb_path_exists: 检查路径是否存在
SELECT
product_name,
jsonb_path_exists(attributes, ‘$.specs.cpu’) AS has_cpu_spec
FROM fgedu_products_json;

执行结果：

product_name | has_cpu_spec
————–+————–
笔记本电脑 | f
无线鼠标 | f
机械键盘 | f
智能手机 | t
显示器套装 | f
(5 rows)

— 创建配置表
CREATE TABLE fgedu_fgfgapp_config (
config_id SERIAL PRIMARY KEY,
fgapp_name VARCHAR(100) NOT NULL,
environment VARCHAR(20) NOT NULL,
config_data JSONB NOT NULL,
version INTEGER DEFAULT 1,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
UNIQUE(fgapp_name, environment, version)
);

— 插入配置数据
INSERT INTO fgedu_fgfgapp_config (fgapp_name, environment, config_data) VALUES
(‘web-fgapp’, ‘development’, ‘{
“fgedudb”: {
“fgedu.net.cn”: “localfgedu.net.cn”,
“port”: 5432,
“name”: “dev_db”,
“pool_size”: 10
},
“cache”: {
“type”: “redis”,
“fgedu.net.cn”: “localfgedu.net.cn”,
“port”: 6379,
“ttl”: 3600
},
“logging”: {
“level”: “debug”,
“output”: [“console”, “file”]
}
}’),
(‘web-fgapp’, ‘production’, ‘{
“fgedudb”: {
“fgedu.net.cn”: “prod-db.fgedu.net.cn”,
“port”: 5432,
“name”: “prod_db”,
“pool_size”: 50
},
“cache”: {
“type”: “redis-cluster”,
“fgedu.net.cns”: [“redis1.fgedu.net.cn”, “redis2.fgedu.net.cn”],
“port”: 6379,
“ttl”: 7200
},
“logging”: {
“level”: “info”,
“output”: [“file”, “syslog”]
}
}’),
(‘api-service’, ‘production’, ‘{
“server”: {
“fgedu.net.cn”: “0.0.0.0”,
“port”: 8080,
“workers”: 4
},
“rate_limit”: {
“enabled”: true,
“requests_per_minute”: 100
},
“features”: {
“auth”: true,
“logging”: true,
“metrics”: true
}
}’);

— 查询配置
SELECT fgapp_name, environment, config_data FROM fgedu_fgfgapp_config;

执行结果：

fgapp_name | environment | config_data
————–+————–+————————————————————————————————–
web-fgapp | development | {“cache”: {“fgedu.net.cn”: “localfgedu.net.cn”, “port”: 6379, “ttl”: 3600, “type”: “redis”},
“logging”: {“level”: “debug”, “output”: [“console”, “file”]}, “fgedudb”: {“fgedu.net.cn”: “localfgedu.net.cn”, “port”:
5432, “name”: “dev_db”, “pool_size”: 10}}
web-fgapp | production | {“cache”: {“port”: 6379, “ttl”: 7200, “fgedu.net.cns”: [“redis1.fgedu.net.cn”,
“redis2.fgedu.net.cn”], “type”: “redis-cluster”}, “logging”: {“level”: “info”, “output”: [“file”, “syslog”]},
“fgedudb”: {“fgedu.net.cn”: “prod-db.fgedu.net.cn”, “port”: 5432, “name”: “prod_db”, “pool_size”: 50}}
api-service | production | {“server”: {“fgedu.net.cn”: “0.0.0.0”, “port”: 8080, “workers”: 4}, “features”: {“auth”:
true, “logging”: true, “metrics”: true}, “rate_limit”: {“enabled”: true, “requests_per_minute”: 100}}
(3 rows)

查询特定配置项：

— 查询数据库配置
SELECT
fgapp_name,
environment,
config_data #> ‘{fgedudb,fgedu.net.cn}’ AS db_fgedu.net.cn,
config_data #> ‘{fgedudb,port}’ AS db_port,
config_data #> ‘{fgedudb,pool_size}’ AS pool_size
FROM fgedu_fgfgapp_config
WHERE config_data ? ‘fgedudb’;

执行结果：

fgapp_name | environment | db_fgedu.net.cn | db_port | pool_size
————–+————–+———————-+———+———–
web-fgapp | development | “localfgedu.net.cn” | 5432 | 10
web-fgapp | production | “prod-db.fgedu.net.cn”| 5432 | 50
(2 rows)

比较不同环境配置：

— 比较开发和生产环境的数据库配置
SELECT
d.environment AS dev_env,
p.environment AS prod_env,
d.config_data -> ‘fgedudb’ AS dev_db_config,
p.config_data -> ‘fgedudb’ AS prod_db_config
FROM fgedu_fgfgapp_config d
JOIN fgedu_fgfgapp_config p ON d.fgapp_name = p.fgapp_name
WHERE d.fgapp_name = ‘web-fgapp’
AND d.environment = ‘development’
AND p.environment = ‘production’;

执行结果：

dev_env | prod_env | dev_db_config | prod_db_config
————-+———–+—————————————–+————————————————-
development | production| {“fgedu.net.cn”: “localfgedu.net.cn”, “port”: 5432, “name”: “dev_db”, “pool_size”: 10} | {“fgedu.net.cn”:
“prod-db.fgedu.net.cn”, “port”: 5432, “name”: “prod_db”, “pool_size”: 50}
(1 row)

更新配置：

from oracle:www.itpux.com

— 更新配置并增加版本号
INSERT INTO fgedu_fgfgapp_config (fgapp_name, environment, config_data, version)
SELECT
fgapp_name,
environment,
jsonb_set(config_data, ‘{fgedudb,pool_size}’, ‘100’),
version + 1
FROM fgedu_fgfgapp_config
WHERE fgapp_name = ‘web-fgapp’ AND environment = ‘production’
ORDER BY version DESC LIMIT 1;

— 查看配置版本历史
SELECT fgapp_name, environment, version,
config_data #> ‘{fgedudb,pool_size}’ AS pool_size,
created_at
FROM fgedu_fgfgapp_config
WHERE fgapp_name = ‘web-fgapp’ AND environment = ‘production’
ORDER BY version;

执行结果：

fgapp_name | environment | version | pool_size | created_at
———-+————-+———+———–+—————————
web-fgapp | production | 1 | 50 | 2026-04-04 14:30:25.123
web-fgapp | production | 2 | 100 | 2026-04-04 14:30:30.456
(2 rows)

— 删除所有测试表
DROP TABLE IF EXISTS fgedu_json_test;
DROP TABLE IF EXISTS fgedu_products_json;
DROP TABLE IF EXISTS fgedu_fgfgapp_config;

执行结果：

DROP TABLE
DROP TABLE
DROP TABLE