current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite

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.

share|improve this question
add comment

1 Answer 1

up vote 2 down vote accepted

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

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.

Unfortunately, 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-6a5f6e63dbf5\"}"
        ,"{\"value\" : \"03334/254147\", \"typeId\" : \"ea4e7d7e-7b87-4628-ba50-6a5f6e63dbf6\"}"
        ,"{\"value\" : \"03334/254148\", \"typeId\" : \"ea4e7d7e-7b87-4628-ba50-6a5f6e63dbf7\"}"}')

 , (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-6a5f6e63dbf6"}'::text) AS t_result
     ,array_remove(jar::text[], '{"value" : "03334/254147", "typeId" : "ea4e7d7e-7b87-4628-ba50-6a5f6e63dbf6"}'::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:
How to turn json array into postgres array?
Query for element of array in JSON column (SO)

And about unnesting arrays:
How to preserve the original order of elements in an unnested array?

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.

jsonb in Postgres 9.4

You will be interested in the jsonb data type in the upcoming Postgres 9.4 (link to devel manual). 'b' is for 'binary'. Among other things, there will be an equality operator = for jsonb. I expect most people will switch to jsonb as soon as it's available.

Depesz blogged on jsonb.

share|improve this answer
    
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 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 at 17:17
    
That's really cool. Thanks for the heads up. –  spa May 16 at 12:21
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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