如何不使用WHERE子句获取JSON数组中的指定元素？

场景说明 #

数据库表db.info的data列存储JSON数组，格式示例如下：

// row 1
[
  {"id": 1, "data": "foo"},
  {"id": 2, "data": "bar"},
  {"id": 3, "data": "baz"}
]

// row 2
[
  {"id": 1, "data": "fus"},
  {"id": 2, "data": "ro"},
  {"id": 3, "data": "dah"}
]

需求 #

从每行的JSON数组中提取id=2的元素，预期结果：

// row 1
{"id": 2, "data": "bar"}

// row 2
{"id": 2, "data": "ro"}

当前实现及问题 #

已通过CASE语句实现，但该方案依赖数组索引定位元素，若数组内元素数量增加或顺序变化会失效：

SELECT
  CASE when (t.data::json->0->'id')::varchar::int = 2 then (t.data::json->0)::varchar
       when (t.data::json->1->'id')::varchar::int = 2 then (t.data::json->1)::varchar
       when (t.data::json->2->'id')::varchar::int = 2 then (t.data::json->2)::varchar
       else null::varchar
  end as "result"
FROM db.info as t;

提问 #

能否仅在SELECT子句中实现，不依赖索引且不使用WHERE或HAVING子句？

解决方案 #

可以利用PostgreSQL的JSON函数在SELECT子句内完成，完全不依赖数组索引：

SELECT
  (SELECT elem
   FROM json_array_elements(t.data::json) AS elem
   WHERE (elem ->> 'id')::int = 2) AS result
FROM db.info AS t;

说明 #

1. json_array_elements(t.data::json)会将每行的JSON数组拆分成单个JSON对象行；
2. 子查询中通过(elem ->> 'id')::int = 2精准过滤出目标元素；
3. 若数组中存在多个id=2的元素，该查询会返回第一个匹配项；如果需要返回所有匹配元素，可以将子查询改为SELECT json_agg(elem)，结果会是包含所有匹配元素的JSON数组；
4. 全程仅在SELECT子句内实现，无需使用WHERE或HAVING子句。

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