KES 数据类型与JSONB高级应用：灵活存储与高效查询

KES 数据类型与JSONB高级应用：灵活存储与高效查询

前言

现代应用系统对数据存储的需求越来越多样化，除了传统的结构化数据，还需要处理半结构化数据、文档数据甚至地理位置数据。KES提供了丰富的数据类型支持，特别是JSONB类型，为灵活存储和高效查询提供了强大能力。

本篇内容深入讲解KES的数据类型体系，重点剖析JSONB的高级应用，包括存储优化、索引策略、查询技巧以及与其他数据类型的结合使用。全文以实际操作为主，结合大量真实案例。如果你需要处理复杂的数据结构，或者希望提升JSON数据的查询性能，相信这篇内容对你会有帮助。

一、数据类型体系概览

KES提供了完整的数据类型体系，满足各种数据存储需求。

基础数据类型

-- 数值类型
CREATE TABLE numeric_demo (
    id SERIAL,
    int_col INTEGER,
    bigint_col BIGINT,
    numeric_col NUMERIC(10,2),
    float_col REAL,
    double_col DOUBLE PRECISION
);

-- 字符串类型
CREATE TABLE string_demo (
    id SERIAL,
    char_col CHAR(10),
    varchar_col VARCHAR(100),
    text_col TEXT
);

-- 日期时间类型
CREATE TABLE datetime_demo (
    id SERIAL,
    date_col DATE,
    time_col TIME,
    timestamp_col TIMESTAMP,
    timestamptz_col TIMESTAMPTZ,
    interval_col INTERVAL
);

-- 布尔类型
CREATE TABLE boolean_demo (
    id SERIAL,
    is_active BOOLEAN,
    is_deleted BOOLEAN DEFAULT FALSE
);

特殊数据类型

-- UUID类型
CREATE TABLE uuid_demo (
    id UUID DEFAULT gen_random_uuid(),
    name VARCHAR(100)
);

INSERT INTO uuid_demo (name) VALUES ('test');

-- 数组类型
CREATE TABLE array_demo (
    id SERIAL,
    tags TEXT[],
    scores INTEGER[]
);

INSERT INTO array_demo (tags, scores) VALUES
(ARRAY['数据库', 'KES', '性能'], ARRAY[85, 90, 88]);

-- 范围类型
CREATE TABLE range_demo (
    id SERIAL,
    date_range DATERANGE,
    num_range INT4RANGE
);

INSERT INTO range_demo (date_range, num_range) VALUES
('[2026-01-01, 2026-12-31]', '[1, 100]');

二、JSONB数据类型详解

JSONB是KES对JSON数据的高级支持，相比JSON类型，JSONB在存储时进行解析，并支持索引，查询性能更优。

JSONB基础操作

-- 创建包含JSONB字段的表
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200),
    attributes JSONB,
    created_at TIMESTAMP DEFAULT now()
);

-- 插入JSONB数据
INSERT INTO products (name, attributes) VALUES
('iPhone 15', '{"brand": "Apple", "price": 7999, "storage": 256, "color": "black"}'),
('MacBook Pro', '{"brand": "Apple", "price": 14999, "memory": 16, "storage": 512}'),
('Galaxy S24', '{"brand": "Samsung", "price": 6999, "storage": 128, "color": "white"}');

JSONB查询操作符

-- -> 操作符：获取JSON对象字段
SELECT name, attributes->'brand' AS brand
FROM products;

-- ->> 操作符：获取JSON对象字段的文本值
SELECT name, attributes->>'brand' AS brand
FROM products;

-- #> 操作符：获取嵌套JSON对象
SELECT name, attributes#>'{brand}' AS brand
FROM products;

-- #>> 操作符：获取嵌套JSON对象的文本值
SELECT name, attributes#>>'{brand}' AS brand
FROM products;

JSONB包含与存在操作符

-- @> 操作符：JSONB包含
SELECT * FROM products
WHERE attributes @> '{"brand": "Apple"}';

-- <@ 操作符：JSONB被包含
SELECT * FROM products
WHERE '{"brand": "Apple", "price": 7999}' <@ attributes;

-- ? 操作符：键是否存在
SELECT * FROM products
WHERE attributes ? 'color';

-- ?| 操作符：任意键是否存在
SELECT * FROM products
WHERE attributes ?| ARRAY['color', 'memory'];

-- ?& 操作符：所有键是否都存在
SELECT * FROM products
WHERE attributes ?& ARRAY['brand', 'price', 'storage'];

三、JSONB索引策略

合理的索引策略是提升JSONB查询性能的关键。

GIN索引

-- 创建GIN索引
CREATE INDEX idx_products_attributes ON products USING gin (attributes);

-- 指定操作符类的GIN索引
CREATE INDEX idx_products_attributes_jsonb ON products 
USING gin (attributes jsonb_path_ops);

-- 查看索引使用情况
SELECT 
    indexname,
    idx_scan,
    idx_tup_read
FROM sys_stat_user_indexes
WHERE tablename = 'products';

部分索引

-- 为特定条件创建部分索引
CREATE INDEX idx_products_apple ON products (attributes)
WHERE attributes @> '{"brand": "Apple"}';

-- 查询时自动使用部分索引
SELECT * FROM products
WHERE attributes @> '{"brand": "Apple"}' AND attributes->>'price' = '7999';

表达式索引

-- 为JSONB字段创建表达式索引
CREATE INDEX idx_products_brand ON products ((attributes->>'brand'));
CREATE INDEX idx_products_price ON products (((attributes->>'price')::NUMERIC));

-- 使用索引查询
SELECT * FROM products
WHERE attributes->>'brand' = 'Apple'
  AND (attributes->>'price')::NUMERIC > 7000;

四、JSONB高级查询技巧

聚合查询

-- 统计各品牌产品数量
SELECT 
    attributes->>'brand' AS brand,
    count(*) AS product_count
FROM products
GROUP BY attributes->>'brand'
ORDER BY product_count DESC;

-- 计算平均价格
SELECT 
    attributes->>'brand' AS brand,
    avg((attributes->>'price')::NUMERIC) AS avg_price
FROM products
GROUP BY attributes->>'brand';

-- 统计属性分布
SELECT 
    key,
    count(*) AS occurrence
FROM products, jsonb_each(attributes)
GROUP BY key
ORDER BY occurrence DESC;

嵌套查询

-- 处理嵌套JSONB数据
CREATE TABLE orders (
    id SERIAL,
    order_info JSONB
);

INSERT INTO orders (order_info) VALUES
('{"customer": {"name": "张三", "age": 30}, "items": [{"product": "iPhone", "qty": 1}, {"product": "Case", "qty": 2}]}');

-- 查询嵌套字段
SELECT 
    order_info->'customer'->>'name' AS customer_name,
    order_info->'customer'->>'age' AS customer_age
FROM orders;

-- 展开JSONB数组
SELECT 
    jsonb_array_elements(order_info->'items')->>'product' AS product,
    (jsonb_array_elements(order_info->'items')->>'qty')::INT AS quantity
FROM orders;

JSONB更新操作

-- 更新JSONB字段
UPDATE products
SET attributes = jsonb_set(
    attributes,
    '{price}',
    '8999'
)
WHERE id = 1;

-- 添加新字段
UPDATE products
SET attributes = attributes || '{"warranty": "2年"}'
WHERE id = 1;

-- 删除字段
UPDATE products
SET attributes = attributes - 'color'
WHERE id = 1;

五、实战案例解析

场景一：动态属性存储

某电商系统需要为不同类型的商品存储动态属性。

-- 创建商品表
CREATE TABLE catalog (
    id SERIAL PRIMARY KEY,
    category VARCHAR(50),
    name VARCHAR(200),
    properties JSONB,
    created_at TIMESTAMP DEFAULT now()
);

-- 插入不同类别的商品
INSERT INTO catalog (category, name, properties) VALUES
('手机', 'iPhone 15', '{"brand": "Apple", "screen": "6.1英寸", "camera": "4800万像素", "battery": "3279mAh"}'),
('笔记本', 'MacBook Pro', '{"brand": "Apple", "screen": "14英寸", "cpu": "M3 Pro", "memory": "16GB"}'),
('平板', 'iPad Air', '{"brand": "Apple", "screen": "10.9英寸", "chip": "M1", "storage": "64GB"}');

-- 创建GIN索引
CREATE INDEX idx_catalog_properties ON catalog USING gin (properties);

-- 查询特定属性的商品
SELECT name, properties->>'brand' AS brand
FROM catalog
WHERE properties @> '{"brand": "Apple"}'
  AND (properties->>'screen')::TEXT LIKE '%英寸%';

场景二：配置信息管理

某系统使用JSONB存储应用配置信息。

-- 创建配置表
CREATE TABLE app_config (
    id SERIAL PRIMARY KEY,
    app_name VARCHAR(100),
    config JSONB,
    updated_at TIMESTAMP DEFAULT now()
);

-- 插入配置
INSERT INTO app_config (app_name, config) VALUES
('web_app', '{"database": {"host": "localhost", "port": 54321}, "cache": {"enabled": true, "ttl": 3600}}'),
('mobile_app', '{"api": {"endpoint": "https://api.example.com", "timeout": 30}}');

-- 查询配置
SELECT 
    app_name,
    config->'database'->>'host' AS db_host,
    config->'database'->>'port' AS db_port
FROM app_config
WHERE app_name = 'web_app';

-- 更新配置
UPDATE app_config
SET config = jsonb_set(
    config,
    '{cache,ttl}',
    '7200'
)
WHERE app_name = 'web_app';

场景三：日志数据分析

某系统使用JSONB存储应用日志。

-- 创建日志表
CREATE TABLE app_logs (
    id SERIAL PRIMARY KEY,
    log_level VARCHAR(20),
    message TEXT,
    context JSONB,
    created_at TIMESTAMP DEFAULT now()
);

-- 插入日志
INSERT INTO app_logs (log_level, message, context) VALUES
('ERROR', '数据库连接失败', '{"host": "db1", "port": 54321, "retry": 3}'),
('WARN', '响应时间过长', '{"endpoint": "/api/users", "duration": 2500}'),
('INFO', '用户登录成功', '{"user_id": 1001, "ip": "192.168.1.100"}');

-- 创建索引
CREATE INDEX idx_logs_context ON app_logs USING gin (context);
CREATE INDEX idx_logs_level_time ON app_logs (log_level, created_at DESC);

-- 查询特定条件的日志
SELECT * FROM app_logs
WHERE log_level = 'ERROR'
  AND context @> '{"host": "db1"}'
  AND created_at > now() - INTERVAL '1 day'
ORDER BY created_at DESC;

-- 统计分析
SELECT 
    log_level,
    count(*) AS count,
    jsonb_object_agg(
        context->>'host',
        count(*)
    ) AS host_distribution
FROM app_logs
WHERE log_level = 'ERROR'
GROUP BY log_level;

总结与展望

JSONB是KES处理半结构化数据的利器。通过合理的存储设计和索引策略，可以实现灵活的Schema演进和高效的查询性能。

核心原则：

1. 根据查询模式选择合适的数据类型
2. 为常用查询路径创建GIN索引
3. 避免过深的JSONB嵌套结构
4. 合理使用表达式索引提升查询性能
5. 定期分析查询性能，优化索引策略

KES的JSONB功能强大，既保持了关系型数据库的查询能力，又提供了文档数据库的灵活性。在实际应用中，建议根据业务特点合理设计数据结构，充分发挥JSONB的优势。

期望本篇内容能够帮助你掌握KES数据类型和JSONB的高级应用，为构建灵活高效的数据存储方案提供技术支撑。

转载自 CSDN-专业IT技术社区

原文链接：https://blog.csdn.net/weixin_62765017/article/details/162820232