PostgreSQL: Remove attribute from JSON column
Update: for 9.5+, there are explicit operators you can use with jsonb
(if you have a json
typed column, you can use casts to apply a modification):
Deleting a key (or an index) from a JSON object (or, from an array) can be done with the -
operator:
SELECT jsonb '{"a":1,"b":2}' - 'a', -- will yield jsonb '{"b":2}'
jsonb '["a",1,"b",2]' - 1 -- will yield jsonb '["a","b",2]'
Deleting, from deep in a JSON hierarchy can be done with the #-
operator:
SELECT '{"a":[null,{"b":[3.14]}]}' #- '{a,1,b,0}'
-- will yield jsonb '{"a":[null,{"b":[]}]}'
For 9.4, you can use a modified version of the original answer (below), but instead of aggregating a JSON string, you can aggregate into a json
object directly with json_object_agg()
.
Related: other JSON manipulations whithin PostgreSQL:
- How do I modify fields inside the new PostgreSQL JSON datatype?
Original answer (applies to PostgreSQL 9.3):
If you have at least PostgreSQL 9.3, you can split your object into pairs with json_each()
and filter your unwanted fields, then build up the json again manually. Something like:
SELECT data::text::json AS before,
('{' || array_to_string(array_agg(to_json(l.key) || ':' || l.value), ',') || '}')::json AS after
FROM (VALUES ('{"attrA":1,"attrB":true,"attrC":["a","b","c"]}'::json)) AS v(data),
LATERAL (SELECT * FROM json_each(data) WHERE "key" <> 'attrB') AS l
GROUP BY data::text
With 9.2 (or lower) it is not possible.
Edit:
A more convenient form is to create a function, which can remove any number of attributes in a json
field:
Edit 2: string_agg()
is less expensive than array_to_string(array_agg())
CREATE OR REPLACE FUNCTION "json_object_delete_keys"("json" json, VARIADIC "keys_to_delete" TEXT[])
RETURNS json
LANGUAGE sql
IMMUTABLE
STRICT
AS $function$
SELECT COALESCE(
(SELECT ('{' || string_agg(to_json("key") || ':' || "value", ',') || '}')
FROM json_each("json")
WHERE "key" <> ALL ("keys_to_delete")),
'{}'
)::json
$function$;
With this function, all you need to do is to run the query below:
UPDATE my_table
SET data = json_object_delete_keys(data, 'attrB');
PostgreSQL - JSON column remove attribute inside an array
I've figure out what I needed to do.
SELECT jsonb_array_elements(jsonb '[{"a":1, "b":2}, {"a":3, "b":4}]') - 'b';
Output
{"a": 1}
{"a": 3}
Postgres - Deleting attributes inside json object of json column
Just replace vendor with an empty value by appending it.
update table_name
set data = data || '{"vendor": {}}'
This requires data
to be defined as jsonb
(which it should be). If it's not, you need to cast it: data::jsonb || ....
If you don't need the vendor key at all, you can also do:
update table_name
set data = data - 'vendor'
which completely removes the key from the value (so it results in {"footerText": "some_text", "headerText": "header_text"}
)
How to remove field from each elements json array postgres?
There is no operator or built-in function to do that. Unnest the array and aggregate modified elements in the way like this:
update items t
set data = (
select jsonb_agg(elem- 'count')
from items
cross join lateral jsonb_array_elements(data) as arr(elem)
where id = t.id)
Test it in db<>fiddle.
How to remove all JSON attributes with certain value in PostgreSQL
There is no built-inf function for this, but you can write your own:
create function remove_keys_by_value(p_input jsonb, p_value jsonb)
returns jsonb
as
$$
select jsonb_object_agg(t.key, t.value)
from jsonb_each(p_input) as t(key, value)
where value <> p_value;
$$
language sql
immutable;
Then you can do:
update records
set payload = remove_key_by_value(payload, to_jsonb(3))
where parent = 1;
This assumes that payload
is defined as jsonb
(which it should be). If it's not, you have to cast it: payload::jsonb
Remove multiple keys from jsonb column in one statement
That should be as simple as applying the #-
operator multiple times:
SELECT '{ "a": 1, "b": 2, "c": 3 }'::jsonb #- '{a}' #- '{b}';
?column?
----------
{"c": 3}
(1 row)
postgres remove json object from json array
demo:db<>fiddle
Why do you store JSON objects in text
columns? In that case you need to cast it before using it as JSON:
SELECT
id,
json_agg(elems) -- 3
FROM mytable,
json_array_elements(jsondata::json) as elems -- 1
WHERE elems ->> 'id' <> 'app2' -- 2
GROUP BY id
- Cast text into JSON; Extract the JSON array into a record per element
- Filter the required objects
- Reaggregate remaining elements into a new JSON array
Related Topics
Group Query Results by Month and Year in Postgresql
List All Sequences in a Postgres Db 8.1 with SQL
Alter Database Failed Because a Lock Could Not Be Placed on Database
SQL Server Script to Create a New User
Is a One Column Table Good Design
How to Execute a .SQL Script on Heroku
Sql-Server: Error - Exclusive Access Could Not Be Obtained Because the Database Is in Use
Identity_Insert Is Set to Off - How to Turn It On
Select Newest Records That Have Distinct Name Column
SQL Server: How to Get All Child Records Given a Parent Id in a Self Referencing Table
The Best Way to Use a Db Table as a Job Queue (A.K.A Batch Queue or Message Queue)
How to List Records with Date from the Last 10 Days