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?
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.