I'm facing an issue regarding using the JSON data type in PostgreSQL. I try to achieve storing a Java model denormalized in the DB. The model features lists of complex objects. Thus, I decided to model those as JSON in native PostgreSQL arrays.
This is a stripped down snippet of my table creation statement:
CREATE TABLE test.persons
(
id UUID,
firstName TEXT,
lastName TEXT,
communicationData JSON[],
CONSTRAINT pk_person PRIMARY KEY (id)
);
As you can see it is a person featuring a list of communication data objects in JSON. One of such objects might look like this:
{"value" : "03334/254147", "typeId" : "ea4e7d7e-7b87-4628-ba50-6a5f6e63dbf6"}
I can easily append such a JSON object to an array using PostgreSQL's array_append. However, I fail at removing a known value from the array. Consider f.e. this SQL statement:
UPDATE test.persons
SET communicationData = array_remove(
communicationData,
'{"value" : "03334/254147", "typeId" : "ea4e7d7e-7b87-4628-ba50-6a5f6e63dbf6"}'::JSON
)
WHERE id = 'f671eb6a-d603-11e3-bf6f-07ba007d953d';
This fails with ERROR: could not identify an equality operator for type json
. Do you have a hint how I could remove a known value from the JSON array? It would also be possible to remove by position in the array, as I know that one also…
PostgreSQL version is 9.3.4.
Best Answer
jsonb
in Postgres 9.4 or laterConsider the
jsonb
data type in Postgres 9.4 or later. The 'b' at the end stands for 'binary'. Among other things, there is an equality operator (=
) forjsonb
. Most people will want to switch.Depesz blog about jsonb.
json
There is no
=
operator defined for the data typejson
, because there is no well defined method to establish equality for wholejson
values. But see below.You could cast to
text
and then use the=
operator. This is short, but only works if your text representation happens to match. Inherently unreliable, except for corner cases. See:Or you can
unnest
the array and use the->>
operator to ..get JSON object field as text
and compare individual fields.Test table
2 rows: first one like in the question, second one with simple values.
Demos
Demo 1You could use
Demo 2array_remove()
withtext
representations (unreliable).Unnest the array and test fields of individual elements.
Demo 3Alternative test with row type.
UPDATE
as requestedFinally, this is how you could implement your
UPDATE
:db<>fiddle here
About the implicit
LATERAL JOIN
:About unnesting arrays:
DB design
To simplify your situation consider an normalized schema: a separate table for the
json
values (instead of the array column), joined in a n:1 relationship to the main table.