PostgreSQL中如何统计JSON嵌套结构内特定爱好的唯一年份数？

当然可以实现！针对你提到的两种hobby_years JSON结构，我整理了几种实用的处理方案，不管是用数据库SQL直接统计，还是拉到程序里处理都能搞定：

你的核心需求是：从两种格式的hobby_years JSON字段中，统计指定爱好（如soccer、basketball）对应的所有唯一年份总数（示例结果为5），下面分场景给出具体方法：

一、数据库SQL处理（以PostgreSQL为例） #

PostgreSQL对JSON/JSONB类型有丰富的原生函数支持，其他数据库（如MySQL 8.0+）也有类似能力，思路通用：

1. 处理第一种结构（爱好→{年份: 1}） #

假设你的表名为user_hobbies，hobby_years是JSONB类型，可以通过展开键值对、筛选爱好、去重计数来实现：

-- 针对指定单个/多个爱好的统计
SELECT COUNT(DISTINCT year) AS unique_year_count
FROM user_hobbies,
     -- 展开soccer的年份键
     jsonb_each_text(hobby_years->'soccer') AS soccer_years(year, val),
     -- 展开basketball的年份键
     jsonb_each_text(hobby_years->'basketball') AS basketball_years(year, val);

如果需要更灵活的多爱好支持（比如动态传入爱好列表），可以用CTE优化：

WITH target_hobbies AS (SELECT unnest(ARRAY['soccer', 'basketball']) AS hobby)
SELECT COUNT(DISTINCT year) AS unique_year_count
FROM user_hobbies,
     target_hobbies,
     jsonb_each_text(hobby_years->target_hobbies.hobby) AS hobby_years(year, val);

2. 处理第二种结构（爱好→[年份数组]） #

用数组展开函数提取年份，再去重计数：

SELECT COUNT(DISTINCT year::INT) AS unique_year_count
FROM user_hobbies,
     jsonb_array_elements_text(hobby_years->'soccer') AS soccer_years(year),
     jsonb_array_elements_text(hobby_years->'basketball') AS basketball_years(year);

同样支持动态爱好列表的版本：

WITH target_hobbies AS (SELECT unnest(ARRAY['soccer', 'basketball']) AS hobby)
SELECT COUNT(DISTINCT year::INT) AS unique_year_count
FROM user_hobbies,
     target_hobbies,
     jsonb_array_elements_text(hobby_years->target_hobbies.hobby) AS hobby_years(year);

3. 兼容两种结构的通用方案 #

如果你的表中同时存在两种结构，可以先判断字段类型，分别处理后合并结果：

WITH target_hobbies AS (SELECT unnest(ARRAY['soccer', 'basketball']) AS hobby),
     hobby_year_data AS (
         SELECT 
             th.hobby,
             CASE 
                 -- 判断当前爱好对应的是对象还是数组
                 WHEN jsonb_typeof(uh.hobby_years->th.hobby) = 'object' THEN 
                     (SELECT array_agg(year) FROM jsonb_each_text(uh.hobby_years->th.hobby) AS y(year, v))
                 WHEN jsonb_typeof(uh.hobby_years->th.hobby) = 'array' THEN 
                     (SELECT array_agg(year::TEXT) FROM jsonb_array_elements_text(uh.hobby_years->th.hobby) AS y(year))
                 ELSE '{}'::TEXT[]
             END AS years
         FROM user_hobbies uh
         CROSS JOIN target_hobbies th
     )
SELECT COUNT(DISTINCT year::INT) AS unique_year_count
FROM hobby_year_data,
     unnest(years) AS year;

二、Python程序处理 #

如果是把数据提取到Python中处理，写个小函数就能自动兼容两种结构，逻辑更直观：

import json

def count_unique_hobby_years(hobby_years_str, target_hobbies):
    # 解析JSON字符串为字典
    hobby_data = json.loads(hobby_years_str)
    # 用集合自动去重年份
    all_unique_years = set()
    
    for hobby in target_hobbies:
        year_info = hobby_data.get(hobby, {})
        if isinstance(year_info, dict):
            # 第一种结构：取字典的键作为年份
            all_unique_years.update(year_info.keys())
        elif isinstance(year_info, list):
            # 第二种结构：取数组元素，统一转为字符串避免类型差异
            all_unique_years.update(map(str, year_info))
    
    return len(all_unique_years)

# 测试示例
sample1 = '''{ "soccer": { "2006": 1, "2007": 1 }, "skiing": {}, "basketball": { "2006": 1, "2016": 1, "2017": 1, "2018": 1 }, "painting": { "2008": 1, "2009": 1, "2014": 1, "2015": 1, "2016": 1 } }'''
sample2 = '''{ "soccer": [2006, 2007], "skiing": [], "basketball": [2006, 2016, 2017, 2018], "painting": [2008, 2009, 2014, 2015, 2016] }'''

print(count_unique_hobby_years(sample1, ['soccer', 'basketball']))  # 输出5
print(count_unique_hobby_years(sample2, ['soccer', 'basketball']))  # 输出5

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