PostgreSQL如何基于另一列值提取JSON中匹配国家左侧的animals数组

核心思路 #

因为你的Metadata是普通JSON类型且存在重复键，PostgreSQL原生JSON解析逻辑会默认保留重复键的最后一个值，直接解析会丢失前置重复键的数据，因此采用字符串正则匹配的方式实现需求：

1. 先定位到region.country值和表Country字段匹配的JSON片段位置
2. 截取该位置左侧的所有JSON文本，提取距离最近的animals键对应的数组值

实现查询 #

SELECT 
    Country,
    Metadata,
    -- 提取左侧最近的animals数组
    (regexp_match(
        -- 截取匹配到的目标region左侧的所有文本
        LEFT(Metadata::text, 
            -- 定位目标region片段的起始位置
            REGEXP_INSTR(Metadata::text, 
                '"region"\s*:\s*\{\s*"country"\s*:\s*"' || LOWER(Country) || '"\s*\}', 
                1, 1, 0, 'i'
            ) - 1
        ),
        '"animals"\s*:\s*(\[[^\]]*\])\s*,$', 'g'
    ))[1]::json AS animals_in_country
FROM myanimals;

逻辑说明 #

• LOWER(Country) 是为了适配示例中JSON内country值的小写写法，可根据实际数据的大小写规则调整
• 正则中的\s*兼容JSON中任意数量的空格、换行符，只要是合法JSON格式均可匹配
• 若某行没有匹配的region.country，返回的animals_in_country值为NULL

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