I need to remove some value from a jsonb type column.
The jsonb value
{"services": [59, 61], "locations": ["34"]}
This query is not working.
select jsonb_set(data, '{services}', (data->'services') - 59) from employees
WHERE data @> '{"services":[59]}';
However, this works:
select jsonb_set(data, '{locations}', (data->'locations') - '34') from
employees WHERE data @> '{"services":[59]}';
How can I remove 59 from services without key index and without single quotes?
(data->'services') - 59
Removes an item by index
Example
/* - 3 is the index to be deleted in a NUMERIC array 0 14 1 15 2 16 3 17 * to be deleted 4 18 */ select '[14, 15, 16, 17, 18]'::jsonb - 3| ?column? | | :--------------- | | [14, 15, 16, 18] |
An array of elements to text
CREATE TABLE employees ( id serial primary key, data jsonb );✓
insert into employees(data) values ('{"services": [50, 57, 59, 61], "locations": ["34"]}'::jsonb), ('{"services": [19, 21], "locations": ["34"]}'::jsonb) ;2 rows affected
-- The link was used (https://dba.stackexchange.com/questions/54283/how-to-turn-json-array-into-postgres-array) select employees.id, jsonb_set(data, '{services}', ('[' || string_agg('"' || elem::text || '"' ,' ,') || ']')::jsonb - '59') from employees, json_array_elements((data->'services')::json) elem WHERE data @> '{"services":[59]}' group by employees.id ;id | jsonb_set -: | :---------------------------------------------------- 1 | {"services": ["50", "57", "61"], "locations": ["34"]}
db<>fiddle here
