PostgreSQL中jsonb数组元素的插入或更新实现问题

我有一个名为listings的表，其中包含jsonb类型的data列，列结构示例如下：

{
  "attributes": {
    "listings": [
      {
        "vin": "a",
        ...
      },
      {
        "vin": "b",
        ...
      }
    ]
  }
}

需求是编写SQL查询，实现：匹配listings数组中vin值为指定内容的元素并更新；如果目标元素不存在，则插入新元素到数组中。

尝试了以下查询：

WITH search AS (SELECT result.*
              FROM public.listings,
                   jsonb_array_elements(public.listings.data -> 'attributes' -> 'listings') WITH ORDINALITY result(value, idx)
              WHERE result.value->>'vin' = 'a')
UPDATE listings
SET data =
        CASE
            WHEN EXISTS(SELECT search.idx FROM search) THEN jsonb_set(listings.data, '{attributes,listings,' || search.idx ||'}', '{"vin": "overriding a"}')
            ELSE jsonb_set(listings.data, '{attributes,listings}', '{"vin": "new c"}')
            END
WHERE listings.id = '123';

执行时出现错误：

[42P01] ERROR: missing FROM-clause entry for table "search" Position: 398

问题原因 #

错误核心是UPDATE语句未与CTE search建立关联，直接在CASE分支中引用search.idx会导致PostgreSQL找不到该表的引用。另外原查询的ELSE分支会直接覆盖整个listings数组，而非追加新元素，不符合需求。

修正方案 #

方案一：子查询获取索引 #

WITH search AS (
    SELECT result.idx
    FROM public.listings
    CROSS JOIN jsonb_array_elements(public.listings.data -> 'attributes' -> 'listings') WITH ORDINALITY result(value, idx)
    WHERE result.value->>'vin' = 'a'
      AND listings.id = '123' -- 仅过滤目标行，提升效率
)
UPDATE listings
SET data = CASE
    WHEN EXISTS(SELECT 1 FROM search) THEN
        jsonb_set(
            listings.data,
            '{attributes,listings,' || (SELECT idx FROM search) || '}',
            '{"vin": "overriding a"}'
        )
    ELSE
        jsonb_set(
            listings.data,
            '{attributes,listings}',
            -- 用||追加新元素到原数组末尾
            listings.data -> 'attributes' -> 'listings' || '{"vin": "new c"}'
        )
    END
WHERE listings.id = '123';

方案二：通过JOIN关联CTE #

这种写法更直观，通过LEFT JOIN将目标行与CTE关联：

WITH search AS (
    SELECT listings.id, result.idx
    FROM public.listings
    CROSS JOIN jsonb_array_elements(public.listings.data -> 'attributes' -> 'listings') WITH ORDINALITY result(value, idx)
    WHERE result.value->>'vin' = 'a'
)
UPDATE listings l
SET data = CASE
    WHEN s.idx IS NOT NULL THEN
        jsonb_set(
            l.data,
            '{attributes,listings,' || s.idx || '}',
            '{"vin": "overriding a"}'
        )
    ELSE
        jsonb_set(
            l.data,
            '{attributes,listings}',
            l.data -> 'attributes' -> 'listings' || '{"vin": "new c"}'
        )
    END
LEFT JOIN search s ON l.id = s.id
WHERE l.id = '123';

关键说明 #

1. 两种方案都先在CTE中定位目标元素的索引，仅针对id='123'的行处理，避免无关数据干扰
2. ELSE分支使用jsonb的||运算符追加新元素，而非覆盖整个数组，保证原有数据不丢失
3. 方案二通过LEFT JOIN关联后，可直接引用CTE中的索引值，逻辑更清晰

内容的提问来源于stack exchange，提问作者Nicolás González