I have table audits with jsonb field data and it has content:
"products": [
{"id": 405, "color": null, "price": 850, "title": "test", "value": 52, "real_value": 105 },
{"id": 347, "color": null, "price": 195, "title": "test2", "value": 69, "real_value": 0}
]
For update, one needs to know the key of the element, but I don't know how to find the key of this element(0). Example:
UPDATE audits set data = jsonb_set(data::jsonb,'{"products",0,"real_value"}','125')
where id = 10 and enterprise_id = 1;
I tried using this:
SELECT ( select index from
generate_series(0, jsonb_array_length(data->'products') - 1) as index
where data->'products'->index->>'id' = '347') as index_of_element
FROM audits; but it's a very hard operation for pg.
Also I know how to find the product id for this object:
select id as possiton from audits
where data->'products' @> '[{"id":347}]'; but not key in array(((
Maybe I can use another function to update an item in an array, but I do not know how.
