postgresql

Using jsonb_array_elements with for update "ERROR: FOR UPDATE is not allowed with set-returning functions in the target list"


I have a JSONB column which contains JSON data like this

[
  {
    "url": "https://example.com",
    "vendor": "County"
  }
]

When I try to query it with "for update"

select
    id,
    updated_at,
    jsonb_array_elements(data_src)->>'url' as vendor,
    in_use
from
    myschema.table a
where
    data_src @> '[{"vendor": "County"}]' limit 1 for update;

I get the following error

ERROR: FOR UPDATE is not allowed with set-returning functions in the target list

Is there anyway around this? Or would I need to modify my JSON format so jsonb_array_elements is not used?


Solution

  • Rewrite your query:

    SELECT a.id,
           a.updated_at,
           e.j->>'url' as vendor,
           a.in_use
    FROM myschema.table a
       CROSS JOIN LATERAL jsonb_array_elements(a.data_src) AS e(j)
    WHERE a.data_src @> '[{"vendor": "County"}]'
    LIMIT 1
    FOR NO KEY UPDATE OF a;
    

    Now the set returning function jsonb_array_elements() is in the FROM clause, where it should be.

    Unless you intend to delete the row or modify a unique key or primary key column, FOR NO KEY UPDATE is the correct lock level.