Tweeted twitter.com/#!/StackDBAs/status/464427165514477568
2 edited tags, trim noise: no need for thank yous in the q
source | link

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.

Any hints a greatly welcome... thanks in advance!

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.

Any hints a greatly welcome... thanks in advance!

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.

1
source | link

How to remove known elements from a JSON[] array in PostgreSQL?

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.

Any hints a greatly welcome... thanks in advance!