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?
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.
I know this has been a desired feature for a long time, though today it might be a deal breaker not to have support to JSON columns, since it's a common practice for today's complex requirements to have some kind of denormalization and document orientation inside a relational DB.
Though I don't know what's the workload required for the GORM to have this on its DSL and of course in the query APIs (dynamic filters and criteria builders), and also on the constraints. We also need to consider today's core team is 100% community based and there are not many contributors.
I would like to know if this isn't planned yet so I can look for alternatives, like @danieljoeblack shared, there is a workaround at the HQL level, though JSON_EXTRACT seems to be a MySQL function and might not work on other databases.
The other option is to actually have two databases, one relational and one JSON (Couch, Mongo, etc), and it seems that Grails can connect to different data sources and tell domain classes to use one or the other (https://stackoverflow.com/a/14894944/1644320), so this affect domain model design, but it's doable.
I think the second option might be the more reliable way right now, though it adds a new architectural component that needs to be managed, has extra costs, etc. Not ideal, specially for SQL guys :)