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

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.