tengo la siguiente tabla
CREATE TABLE country ( id INTEGER NOT NULL PRIMARY KEY , name VARCHAR(50), extra_info JSONB ); INSERT INTO country(id,extra_info) VALUES (1, '{ "name" : "France", "population" : "65000000", "flag_colours": ["red", "blue","white"]}'); INSERT INTO country(id,extra_info) VALUES (2, '{ "name": "Spain", "population" : "47000000", "borders": ["Portugal", "France"] }');y puedo agregar un elemento a la matriz como este
UPDATE country SET extra_info = jsonb_set(extra_info, '{flag_colours,999999999}', '"green"', true);y actualiza asi
UPDATE country SET extra_info = jsonb_set(extra_info, '{flag_colours,0}', '"yellow"');Ahora me gustaría eliminar un elemento de matriz con un índice o nombre conocido.
¿Cómo eliminaría un elemento flag_color por índice o por nombre?
Actualizar
Eliminar por índice
UPDATE country SET extra_info = extra_info #- '{flag_colours,-1}'
¿Cómo puedo eliminar por nombre?
Como las matrices no tienen acceso directo a los elementos de una manera sencilla, podemos tratar de abordar esto de manera diferente mediante el anidamiento -> elementos de filtrado -> unir las cosas nuevamente. He formulado un ejemplo de código con comentarios ordenados para ayudar.
CREATE TABLE new_country AS -- 4. Return a new array (for immutability) that contains the new desired set of colors SELECT id, name, jsonb_set(extra_info, '{flag_colours}', new_colors, FALSE) FROM country -- 3. Use Lateral join to apply this to every row LEFT JOIN LATERAL ( -- 1. First unnest the desired elements from the Json array as text (to enable filtering) WITH prep AS (SELECT jsonb_array_elements_text(extra_info -> 'flag_colours') colors FROM country) SELECT jsonb_agg(colors) new_colors -- 2. Form a new jsonb array after filtering FROM prep WHERE colors <> 'red') lat ON TRUE;En el caso de que desee actualizar solo la columna afectada sin volver a crear la tabla principal, puede:
UPDATE country SET extra_info=new_extra_info FROM new_country WHERE country.id = new_country.id;Lo he dividido en dos consultas para mejorar la legibilidad; sin embargo, también puede usar una subconsulta en lugar de crear una nueva tabla (nuevo_país).
Con la subconsulta, debería verse así:
UPDATE country SET extra_info=new_extra_info FROM (SELECT id, name, jsonb_set(extra_info, '{flag_colours}', new_colors, FALSE) new_extra_info FROM country -- 3. Use Lateral join to scale this across tables LEFT JOIN LATERAL ( -- 1. First unnest the desired elements from the Json array as text (to enable filtering) WITH prep AS (SELECT jsonb_array_elements_text(extra_info -> 'flag_colours') colors FROM country) SELECT jsonb_agg(colors) new_colors -- 2. Form a new jsonb array after filtering FROM prep WHERE colors <> 'red') lat ON TRUE) new_country WHERE country.id = new_country.id;Además, puede filtrar filas mediante (a partir de PostgreSQL 9.4):
SELECT * FROM country WHERE (extra_info -> 'flag_colours') ? 'red'En realidad PG12 permite hacerlo sin JOIN LATERAL:
SELECT jsonb_path_query_array(j #> '{flag_colours}', '$[*] ? (@ != "red")'), jsonb_set(j, '{flag_colours}', jsonb_path_query_array(j #> '{flag_colours}', '$[*] ? (@ != "red")')) FROM (SELECT '{ "name" : "France", "population" : "65000000", "flag_colours": ["red", "blue","white"]}'::jsonb AS j ) AS j WHERE j @? '$.flag_colours[*] ? (@ == "red")'; jsonb_path_query_array | jsonb_set ------------------------+--------------------------------------------------------------------------------- ["blue", "white"] | {"name": "France", "population": "65000000", "flag_colours": ["blue", "white"]} (1 row)