Postgres Grails - Storing List as JSON vs Map as JSON

[umakot]

Hi,
I want to create a "List of Objects" as JSON in postgres. The 3.2.0 version of grails-postgresql-extensions allows to do the same with "Map of Objects ". Objects are simple POJO

I have created the attached git to draw upon the example on what I want to do https://github.com/umakot/testpost.git

In this example Person Domain object has list of addresses and I want to store those addresses in JSON on postgres. In the Domain object, I want tot define it as "List". However I cannot have the list map to JSON in Static Mapping of domain class. Where as If I define the addresses as Map, it allows me to store as JSON in postgres.

The problem with storing addresses as "Map" instead of "List" is that querying it is not easy in hibernate and psql command prompt.

Please let me know if I am missing something or if there is a different way to think/do about it.

here is the difference in storage on json

Storing Map as JSON

testpost=# select * from person ;
 id | version |                                                                                                                         addresses                                                                                                                         | age | f_name | l_name 
----+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----+--------+--------
  3 |       0 | {"2":{"label":"Work","houseNum":"1234","streetName":"Washington Street","city":"Princeton","zipcode":"06990","country":"USA"},"1":{"label":"Home","houseNum":"1234","streetName":"New Port Pavonia","city":"New York","zipcode":"07310","country":"USA"}} |  32 | George | Smith
  4 |       0 | {"2":{"label":"Work","houseNum":"1234","streetName":"Reynold st","city":"Princeton","zipcode":"06990","country":"USA"},"1":{"label":"Home","houseNum":"1234","streetName":"New Port Pavonia","city":"New York","zipcode":"07310","country":"USA"}}        |  28 | Tracy  | Smith

Storing List as JSON (here the map key is not there and easier to query.)

testpost=# select * from person ;
 id | version |                                                                                                                         addresses                                                                                                                         | age | f_name | l_name 
----+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----+--------+--------
  3 |       0 | {"label":"Work","houseNum":"1234","streetName":"Washington Street","city":"Princeton","zipcode":"06990","country":"USA"},{"label":"Home","houseNum":"1234","streetName":"New Port Pavonia","city":"New York","zipcode":"07310","country":"USA"} |  32 | George | Smith
  4 |       0 | {"label":"Work","houseNum":"1234","streetName":"Reynold st","city":"Princeton","zipcode":"06990","country":"USA"},{"label":"Home","houseNum":"1234","streetName":"New Port Pavonia","city":"New York","zipcode":"07310","country":"USA"}       |  28 | Tracy  | Smith

[ilopmar]

I think it makes sense to store the objects as a list. I'm going to try and see what I can do.

[ilopmar]

@umakot please note that your expected result storing as a list is not a valid json. The [ and ] are missing:

Instead of this:

{"label":"Work",....},{"label":"Home",....}

The values that is stored is:

[{"label":"Work",....},{"label":"Home",....}]

Is that ok for you?

[ilopmar]

I mean, I'm not really sure if it's possible to query this list of jsons to get, for example, all the fieds with label = "Work".

Please see the actual json operators: http://www.postgresql.org/docs/9.3/static/functions-json.html

[ilopmar]

Have you consider using hstore instead json for this?

[umakot]

ivan, Entire postgres functions are reasonably well developed for "arrays of objects" ( I prefer this to be list in Java/Grails code) .

Also ,you are right abt "[{"label":"Work",....},{"label":"Home",....}]" (square brackets) for the array to be inserted.

About your question on how to query for label="Work". I have created the following sql. You can run thru and see how it works.
Step1:

   create table person (id int, version int, age int,f_name character varying(255), l_name character varying (255), addresses json) ;

Step2: (insert 3 records)

     insert into person (id, version, age, f_name,l_name, addresses) values (1,0,30,'uma','kot','[{"label":"Work","houseNum":"1234","streetName":"Washington Street","city":"Princeton","zipcode":"06990","country":"USA"},{"label":"Home","houseNum":"1234","streetName":"New Port Pavonia","city":"New York","zipcode":"07310","country":"USA"}]');
           insert into person (id, version, age, f_name,l_name, addresses) values (2,0,30,'adi','kot','[{"label":"Work","houseNum":"123456","streetName":"Adams","city":"Princeton","zipcode":"06990","country":"USA"},{"label":"Home","houseNum":"12323","streetName":"New Port Pavonia","city":"New York","zipcode":"07311","country":"USA"}]');
          insert into person (id, version, age, f_name,l_name, addresses) values (3,0,30,'james','kot','[{"label":"office","houseNum":"Z234","streetName":"Adams","city":"Princeton","zipcode":"09850","country":"USA"},{"label":"Home","houseNum":"Z567","streetName":"Hoboken","city":"New York","zipcode":"07411","country":"USA"}]');

step 3: Query for records with 'label' = 'Work' ( 2 records, bcos one record has label= 'Office')

  select *  from (select f_name, json_array_elements(addresses) as Addr from person) t where t.Addr->>'label' = 'Work';

step 4: Query for records with 'label' = 'Home'
select * from (select f_name, json_array_elements(addresses) as Addr from person) t where t.Addr->>'label' = 'Home';

[ilopmar]

At the moment is not possible to define a List and handle it with a hibernate user type. I've digging and I think it's a Grails bug. I'm going to try to reproduce it and maybe submit a PR to grails-core.

I'll keep you posted.

[umakot]

Thanks. Not sure, but even an Array of "objects" is also workable I guess.

[ilopmar]

I sent a PR to grails yesterday and it was accepted. In the next 2.3.x and 2.4.x versions the List can be used in the domain class and handled in my hibernate user type.

grails/grails-core#533

[ilopmar]

I've finally found some time to work on this feature. I have a working version compatible with Grails 3. I'll try to cleanup the code an publish a new version with the feature in the next days.

[gaut4m]

Hello ilopmar,

I am having a similar issue. wondering if this feature is released yet?

Thank you,
Gautham

[theiosdevguy]

Hi @ilopmar

You mentioned you were working on this feature. Did you get a chance to finalise it?

[ilopmar]

Sorry, I didn't finish the feature and I'm not working on this anymore. If you send a PR with the feature I'll review it and release a new version.