我来帮你搞定这个查询需求！下面一步步拆解实现方法：

1. 获取JSON数组的最后一个元素 #

因为数组长度不固定，我们可以用jsonb_array_length()函数获取数组长度，再通过索引（数组从0开始，所以长度减1就是最后一个元素的索引）定位到最后一个元素：

ip -> (jsonb_array_length(ip) - 1)

如果你的ip列是json类型而非jsonb，可以先转成jsonb：ip::jsonb -> (jsonb_array_length(ip::jsonb) - 1)

2. 提取role字段并截取GUID #

从最后一个元素中取出role字段的字符串值，再用split_part()函数分割冒号后的GUID部分：

split_part(
  (ip::jsonb -> (jsonb_array_length(ip::jsonb) - 1)) ->> 'role',
  ':',
  2
) AS extracted_guid

• ->> 用来把JSON字段转成文本类型
• split_part(字符串, 分隔符, 取第N段) 这里取冒号分割后的第2段，正好是我们要的GUID

3. 关联子查询筛选记录 #

假设你的子查询已经能返回需要匹配的GUID集合，比如(SELECT valid_guid FROM your_valid_guids)，我们可以用两种方式关联筛选：

方法一：用IN子句（写法简洁） #

SELECT 
  id, -- 替换成你需要的其他字段
  split_part(
    (ip::jsonb -> (jsonb_array_length(ip::jsonb) - 1)) ->> 'role',
    ':',
    2
  ) AS extracted_guid
FROM employeeRole
WHERE split_part(
    (ip::jsonb -> (jsonb_array_length(ip::jsonb) - 1)) ->> 'role',
    ':',
    2
  ) IN (
    -- 这里替换成你的子查询
    SELECT valid_guid FROM your_valid_guids
  );

方法二：用JOIN（性能更优，适合子查询结果量大的场景） #

SELECT 
  er.id, -- 替换成你需要的其他字段
  split_part(
    (er.ip::jsonb -> (jsonb_array_length(er.ip::jsonb) - 1)) ->> 'role',
    ':',
    2
  ) AS extracted_guid
FROM employeeRole er
JOIN (
    -- 这里替换成你的子查询
    SELECT valid_guid FROM your_valid_guids
) vg ON split_part(
    (er.ip::jsonb -> (jsonb_array_length(er.ip::jsonb) - 1)) ->> 'role',
    ':',
    2
) = vg.valid_guid;

额外优化小技巧 #

如果不想重复写提取GUID的代码，可以用LATERAL子查询简化逻辑：

SELECT 
  er.id,
  guid_extractor.extracted_guid
FROM employeeRole er
LATERAL (
  SELECT split_part(
    (er.ip::jsonb -> (jsonb_array_length(er.ip::jsonb) - 1)) ->> 'role',
    ':',
    2
  ) AS extracted_guid
) guid_extractor
WHERE guid_extractor.extracted_guid IN (
    SELECT valid_guid FROM your_valid_guids
);

这样代码更整洁，后续修改提取逻辑也更方便~

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