PostgreSQL查询jsonb数组中匹配指定recordId的支付记录

针对你的表结构和JSON格式，以下几种SQL查询方式可以实现需求：

方法1：使用JSON包含操作符@> #

这是最简洁的方式，利用PostgreSQL的jsonb包含特性，检查record列是否包含指定的recordId结构：

SELECT *
FROM payment_records
WHERE record @> '{"records": [{"recordId": "abf35109-7d5f-4ba3-9f6e-2c6d1a305134"}]}';

方法2：使用JSON路径函数jsonb_path_exists #

通过JSON路径表达式精准匹配数组中的元素，适合复杂条件场景：

SELECT *
FROM payment_records
WHERE jsonb_path_exists(
  record,
  '$.records[*] ? (@.recordId == "abf35109-7d5f-4ba3-9f6e-2c6d1a305134")'
);

方法3：展开数组后过滤 #

如果需要同时获取匹配的数组元素，可先展开records数组再过滤，最后去重返回原表条目：

SELECT DISTINCT pr.*
FROM payment_records pr
JOIN jsonb_array_elements(pr.record->'records') AS rec(elem)
ON elem->>'recordId' = 'abf35109-7d5f-4ba3-9f6e-2c6d1a305134';

性能优化（可选） #

如果查询频繁，建议创建GIN索引加速jsonb操作：

CREATE INDEX idx_payment_records_records ON payment_records USING GIN (record jsonb_path_ops);

内容的提问来源于stack exchange，提问作者stray.mcc