grails-data-mapping
grails-data-mapping copied to clipboard
grails
Support for JSON columns
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?
For Postgresql you could use the grails posgresql extension plugin
https://github.com/kaleidos/grails-postgresql-extensions
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.
FYI... https://twitter.com/objectcomputing/status/856556974636453888
@jeffbrown does that mean you're considering adding support for JSON columns, or that you've added it?
does that mean you're considering adding support for JSON columns, or that you've added it?
@c256985 The former.
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.
@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.
@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).
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 👍
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.
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])
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?
