PostgreSQL中如何通过JSON内的ID关联events与orders表？

你的问题核心是JSON提取出的profile.id是字符串类型，和orders.profile_id的数值类型不匹配，同时要确保嵌套JSON的解析逻辑正确。

正确的JOIN条件写法如下：

orders.profile_id = ((event->>'response_body')::jsonb->'result'...还有like annuallanguageottoSGit，这部分要去掉，重新来：

orders.profile_id = ((event->>'response_body')::jsonb->'result'->0->'profile'->>'id')::int

拆解说明： #

• event->>'response_body'：从event JSON对象中取出response_body的字符串值（它本身是JSON格式的字符串）
• ::jsonb：将该字符串转为JSONB类型，才能解析内部嵌套结构
• ->'result'->0：访问result数组的第一个元素（数组索引从0开始）
• ->'profile'->>'id'：从profile对象中取出id的字符串值
• ::int：把字符串类型的id转为整数，和orders.profile_id的数值类型匹配

完整SQL语句： #

SELECT
  event->>'url_path',
  SUM(size) AS "ORDERS SIZE (SUM)"
FROM
  events
INNER JOIN
  orders ON orders.profile_id = ((event->>'response_body')::jsonb->'result'->0->'profile'->>'id')::int
GROUP BY
  1
LIMIT 10;

额外提示： #

如果response_body存在非JSON格式内容，或result数组为空，可加COALESCE避免报错，比如：

orders.profile_id = COALESCE(((event->>'response_body')::jsonb->'result'->0->'profile'->>'id')::int, -1)

将无效数据映射到不存在的profile_id（如-1），保证查询正常执行。

内容的提问来源于stack exchange，提问作者Eu Román