Postgres环境下JSON对象列表是否支持SQL查询及结构优化问询

现有结构的查询实现 #

假设你的表名为device_data，存储JSON的字段为port_info，且字段类型为推荐的jsonb类型，以下是对应5个需求的查询语句：

• 需求1：查询所有端口名称

SELECT jsonb_object_keys(port_element) AS port_name
FROM device_data,
     jsonb_array_elements(port_info -> 'ports') AS port_element;

• 需求2：查询端口总数量

SELECT jsonb_array_length(port_info -> 'ports') AS port_total_count
FROM device_data;

• 需求3：查询端口p2的全部interactions_dynamics信息

SELECT port_element -> 'p2' -> 'interactions_dynamics' AS p2_dynamics
FROM device_data,
     jsonb_array_elements(port_info -> 'ports') AS port_element
WHERE port_element ? 'p2';

• 需求4：查询interactions_type下num_rds≥7的端口

SELECT port_name
FROM device_data,
     jsonb_array_elements(port_info -> 'ports') AS port_element,
     jsonb_object_keys(port_element) AS port_name
WHERE (port_element -> port_name -> 'interactions_type' ->> 'num_rds')::INT >=7;

• 需求5：查询满足interactions_dynamics下rds_max>20的对应信息

SELECT 
  port_name,
  (port_element -> port_name -> 'interactions_type' ->> 'num_wrts')::INT AS num_wrts,
  (port_element -> port_name -> 'interactions_dynamics' ->> 'rds_min')::INT AS rds_min
FROM device_data,
     jsonb_array_elements(port_info -> 'ports') AS port_element,
     jsonb_object_keys(port_element) AS port_name
WHERE (port_element -> port_name -> 'interactions_dynamics' ->> 'rds_max')::INT >20;

JSON结构优化建议 #

当前结构可以支持所有需求，但每次查询都需要遍历数组元素并拆分对象键，查询效率和可读性都有优化空间，建议调整为如下结构：

{
  "ports": [
    {"port_name": "p1", "interactions_type":{"num_rds":4,"num_wrts":8},"interactions_dynamics":{"rds_min":1,"rds_max":10}},
    {"port_name": "p2", "interactions_type":{"num_rds":7,"num_wrts":2},"interactions_dynamics":{"rds_min":6,"rds_max":8}},
    {"port_name": "p3", "interactions_type":{"num_rds":14,"num_wrts":6},"interactions_dynamics":{"rds_min":5,"rds_max":50}}
  ]
}

调整后无需每次拆分对象键，查询逻辑更简洁，也可以针对ports字段创建GIN索引大幅提升查询速度。以需求4为例，优化后查询语句可以简化为：

SELECT port_name
FROM device_data,
     jsonb_array_elements(port_info -> 'ports') AS port_element
WHERE (port_element -> 'interactions_type' ->> 'num_rds')::INT >=7;

内容的提问来源于stack exchange，提问作者daveg