search

grails / grails-data-mapping

GORM - Groovy Object Mapping
http://gorm.grails.org/
218 stars 198 forks source link

Support for JSON columns #1031

Open c256985 opened 6 years ago

c256985 commented 6 years ago

Oracle, Postgres and MySQL all have support for storing JSON in columns, and querying/indexing json data stored in those columns. I was wondering how JSON columns are supported in GORM? How do we map a column appropriately to deal with JSON data?

sdelamo commented 6 years ago

For Postgresql you could use the grails posgresql extension plugin

https://github.com/kaleidos/grails-postgresql-extensions

c256985 commented 6 years ago

That looks interesting. I also noticed that Hibernate seems to have support for JSON and JSONB column types across Oracle, Postgres and MySQL. https://vladmihalcea.com/2016/06/20/how-to-map-json-objects-using-generic-hibernate-types/

Support at the GORM-level would also be useful if it doesn't already exist.

jeffscottbrown commented 6 years ago

FYI... https://twitter.com/objectcomputing/status/856556974636453888

c256985 commented 6 years ago

@jeffbrown does that mean you're considering adding support for JSON columns, or that you've added it?

jeffscottbrown commented 6 years ago

does that mean you're considering adding support for JSON columns, or that you've added it?

@c256985 The former.

c256985 commented 6 years ago

@jeffbrown Do you have a particular release in mind yet?

jeffscottbrown commented 6 years ago

Do you have a particular release in mind yet?

No.

We are gauging interest to find out if there is enough demand to justify investing in building support.

c256985 commented 6 years ago

@jeffbrown From what little I've read, it looks like Hibernate already has support for it. (Not that that helps you decide whether or not you have the user-demand for it).

It would mean that the developer would have to bypass GORM and go directly with Hibernate.

The only alternative that I see is to have some combination of an RDB and Mongo where the JSON-like data is stored in Mongo. The developer would have to munge the data together at the Service-level. And figure out some criteria-based approach to querying.

Either way, it's messy for developers.

jasenj1 commented 6 years ago

+1 for JSON column support in GORM & Grails. (Oracle DB here.)

c256985 commented 6 years ago

@jasenj1 what kind of alternative approaches have you considered?

jasenj1 commented 6 years ago

@c256985 Currently we are using a varchar/string column and doing JSON-type actions in a Service (some of this could be moved to the Domain object definition, but I prefer to keep the domain objects lean).

rajat333malhotra commented 6 years ago

+1 for JSON column support in GORM & Grails.

oliver-was-here commented 6 years ago

+1 for JSON column support in GORM & Grails.

dburyak commented 6 years ago

+1 for JSON column support in GORM & Grails.

curd0 commented 6 years ago

+1 for JSON column support in GORM & Grails.

AleAnonMallo commented 6 years ago

+1 for JSON column support in GORM & Grails.

dustindclark commented 6 years ago

+1, y'all.

rkimaoui commented 6 years ago

+1 for JSON column support in GORM & Grails.

graemerocher commented 6 years ago

Scheduled for GORM 7.0.. thanks for the feedback

madmas commented 5 years ago

Hi @graemerocher, as there is RC2 availabe and I cannot find any docs in https://gorm.grails.org/7.0.x/hibernate/manual/ - is it still planned for 7.0.0 ? thanks a lot!

Edit: received a response via twitter: https://twitter.com/jeffscottbrown/status/1113853667994734599 👍

ppazos commented 1 year ago

Hi all, I come to this requirement many times, mainly to avoid creating new tables when I have hasMany of simple types like String, Long, etc. specially when I know those Lists of simple values will be small, but GORM generates a new table, joins are needed, etc. It would be great to just have an array or map of JSON type that can be automatically serialized and parsed on insert/query.

danieljoeblack commented 1 year ago

Hit this issue looking for a way to search a JSON column in a domain object. I was able to use HQL with JSON_EXTRACT to achieve this:

Domain.executeQuery("""
  select 
      d
  from 
      Domain d 
  where
      and FUNCTION('JSON_EXTRACT',d.jsonColumn,'\$.jsonField') = :val
""",[val:1])
jaunt commented 8 months ago

Hi @graemerocher, as there is RC2 availabe and I cannot find any docs in https://gorm.grails.org/7.0.x/hibernate/manual/ - is it still planned for 7.0.0 ? thanks a lot!

Edit: received a response via twitter: https://twitter.com/jeffscottbrown/status/1113853667994734599 👍

That twitter link doesn't seem to work anymore. May I know the gist of the response?

jdaugherty commented 2 weeks ago

I'm hoping to add support for this in an upcoming gorm version. Likely it will be the version where we switch to JPA / not the one released with Grails 7.