PostgreSQL中jsonb转多行求助：按科目拆分并处理日期字段

首先纠正你之前的查询错误：jsonb_array_elements(r.details->'createdOn')无法返回结果，因为r.details本身是JSON数组，直接用->'createdOn'会从数组对象中取不存在的键，得到null，展开null自然没有结果。正确的做法是先展开数组元素，再从每个元素中提取字段。

针对你的需求（每行对应一个用户科目，处理证书日期字段，兼容单/多科目JSON结构），可以使用以下查询：

SELECT
  r.id,
  r.userid,
  sub.subject,
  sub.certificate_date
FROM rewards r
-- 先展开details数组中的每个对象
CROSS JOIN LATERAL jsonb_array_elements(r.details) AS obj
-- 对每个对象生成1-2行记录，对应两个可能的科目
CROSS JOIN LATERAL (
  VALUES
    (obj->>'subject', COALESCE(obj->>'certidate', obj->>'createdOn')),
    (obj->>'subject2', COALESCE(obj->>'certidate2', obj->>'createdOn'))
) AS sub(subject, certificate_date)
-- 过滤掉没有科目信息的行
WHERE sub.subject IS NOT NULL;

查询逻辑说明： #

1. jsonb_array_elements(r.details)：将details列的JSON数组拆分为单个对象行，处理JSON1、JSON2、JSON3的数组结构。
2. CROSS JOIN LATERAL VALUES(...)：对每个展开的对象生成两条记录，分别对应subject/certidate和subject2/certidate2字段组合，兼容JSON3的双科目结构。
3. COALESCE(obj->>'certidate', obj->>'createdOn')：优先使用certidate字段作为证书日期，若不存在则 fallback 到createdOn。
4. WHERE sub.subject IS NOT NULL：过滤掉无科目信息的行（比如JSON1、JSON2中不存在的subject2对应的记录）。

测试结果： #

针对你提供的3条数据，查询会返回：

id | userid | subject | certificate_date 
----|--------|---------|-------------------
1  | Nik    | Math    | 1664494754585
2  | SAM    | Science | 1664494754515
3  | xxx    | Science | 1664494754250
3  | xxx    | Math    | 1664494754250

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