8

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.

10

jsonb in Postgres 9.4 or later

You will be interested in the jsonb data type in Postgres 9.4. 'b' is for 'binary'. Among other things, there is an equality operator = for jsonb. Most people will want switch to jsonb.

Depesz blog about jsonb.

json

There is currently no = operator defined for the data type json, because there is no well defined method to establish equality of the whole json values. But see below.

You could cast to text and use the = operator then. This is short, but only works if your text representation matches and is unreliable.

Or you can unnest the array and use the ->> operator to .. get JSON object field as text and compare individual fields.

I cannot provide an SQL Fiddle, for some methods for arrays of json are "not yet implemented" in JDBC. Try this at home (pg 9.3+):

Test table

2 rows: first one like in the question, second one with simple values.

CREATE TABLE t (
   t_id int PRIMARY KEY
 , jar  json[]
);

INSERT INTO t VALUES
   (1, '{"{\"value\" : \"03334/254146\", \"typeId\" : \"ea4e7d7e-7b87-4628-ba50-f5\"}"
        ,"{\"value\" : \"03334/254147\", \"typeId\" : \"ea4e7d7e-7b87-4628-ba50-f6\"}"
        ,"{\"value\" : \"03334/254148\", \"typeId\" : \"ea4e7d7e-7b87-4628-ba50-f7\"}"}')

 , (2, '{"{\"value\" : \"a\", \"typeId\" : \"x\"}"
        ,"{\"value\" : \"b\", \"typeId\" : \"y\"}"
        ,"{\"value\" : \"c\", \"typeId\" : \"z\"}"}');

Demos

Demo 1: you could use array_remove() with text representations (unreliable).

SELECT t_id
     , jar, array_length(jar, 1) AS jar_len
     , jar::text[] AS t, array_length(jar::text[], 1) AS t_len
     ,array_remove(jar::text[], '{"value" : "03334/254147", "typeId" : "ea4e7d7e-7b87-4628-ba50-f6"}'::text) AS t_result
     ,array_remove(jar::text[], '{"value" : "03334/254147", "typeId" : "ea4e7d7e-7b87-4628-ba50-f6"}'::text)::json[] AS j_result
FROM t;

Demo 2: unnest the array and test fields of individual elements.

SELECT t_id, array_agg(j) AS j_new
FROM   t
     , unnest(jar) AS j   -- LATERAL JOIN
WHERE  j->>'value' <> '03334/254146'
AND    j->>'typeId' <> 'ea4e7d7e-7b87-4628-ba50-6a5f6e63dbf5'
GROUP  BY 1;

Demo 3: alternative test with row type.

SELECT t_id, array_agg(j) AS j_new
FROM   t
     , unnest(jar) AS j   -- LATERAL JOIN
WHERE  (j->>'value', j->>'typeId') NOT IN (
         ('03334/254146', 'ea4e7d7e-7b87-4628-ba50-6a5f6e63dbf5')
        ,('a', 'x')
       )
GROUP  BY 1;

UPDATE as requested

Finally, this is how you could implement your UPDATE:

UPDATE t
SET    jar = sub.jar
FROM  (
   SELECT t_id, array_agg(j) AS jar
   FROM   (SELECT t_id, jar FROM t WHERE t_id = 2) t  -- reduce to relevant rows
        , unnest(jar) AS j   -- LATERAL JOIN
   WHERE  j->>'value' <> 'a'
   AND    j->>'typeId' <> 'x'
   GROUP  BY 1
   ) sub
WHERE  t.t_id = sub.t_id;

More about the implicit LATERAL JOIN:

And 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.

  • It works like a charm. Yes, it would be easier with normalized data, but I'm in a 98% read, 2% write scenario. So I wanted to experiment with denormalization :-) Is there anything releated planned for Postgres 9.4 which might help with the original question? – spa May 9 '14 at 9:01
  • @spa: Actually, Postgres 9.4 will bring jsonb. I expect you'll love it. Added a chapter with links. – Erwin Brandstetter May 9 '14 at 17:17
  • That's really cool. Thanks for the heads up. – spa May 16 '14 at 12:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.