MySQL JSON Support

[Malcolm Edgar <ma...@gmail.com>]

Hi All,

I was hoping to use the JSON features in MySQL 5.7 and wondered if anyone had used with with Cayenne.  I was hoping to use Named Queries to perform JSON specific queries, but treat it as a String otherwise with the Cayenne classes.

Any comments or recommendations would be appreciated.

regards Malcolm

Re: MySQL JSON Support

[Malcolm Edgar <ma...@gmail.com>]

Thanks Andrus and John for the feedback.

It looks like Postgres has better JSON support than MySQL.

regards Malcolm

On Wed, May 6, 2020 at 12:32 AM John Huss <jo...@gmail.com> wrote:

> I have been using the Postgres JSONB type as an entity attribute (mapped as
> jdbc type OTHER) for some time, both with Lists and Maps. You can define an
> ExtendedType subclass to read the value as a string and parse into a
> map/list using Jackson's ObjectMapper.
>
> On Mon, May 4, 2020 at 11:23 PM Malcolm Edgar <ma...@gmail.com>
> wrote:
>
> > Hi All,
> >
> > I was hoping to use the JSON features in MySQL 5.7 and wondered if anyone
> > had used with with Cayenne.  I was hoping to use Named Queries to perform
> > JSON specific queries, but treat it as a String otherwise with the
> Cayenne
> > classes.
> >
> > Any comments or recommendations would be appreciated.
> >
> > regards Malcolm
> >
>

Re: MySQL JSON Support

[John Huss <jo...@gmail.com>]

I have been using the Postgres JSONB type as an entity attribute (mapped as
jdbc type OTHER) for some time, both with Lists and Maps. You can define an
ExtendedType subclass to read the value as a string and parse into a
map/list using Jackson's ObjectMapper.

On Mon, May 4, 2020 at 11:23 PM Malcolm Edgar <ma...@gmail.com>
wrote:

> Hi All,
>
> I was hoping to use the JSON features in MySQL 5.7 and wondered if anyone
> had used with with Cayenne.  I was hoping to use Named Queries to perform
> JSON specific queries, but treat it as a String otherwise with the Cayenne
> classes.
>
> Any comments or recommendations would be appreciated.
>
> regards Malcolm
>

Re: MySQL JSON Support

[Andrus Adamchik <an...@objectstyle.org>]

Hi Malcolm,

I haven't done it, but I suspect it should just work in the scenario you described. If it doesn't, we can look at details. 

On that note, I've been working with geospatial data structures lately (mostly on PostgreSQL). Parts of them map to JSON, and some - to geometries which are also hierarchical objects stored in a single DB column. We were trying to devise a way to do more advanced stuff within Cayenne (such as spatial joins that contain complex expressions with functions, querying on the internals of JSON/geometry columns, etc.). But this is still some time away from being ready.

Andrus


> On May 5, 2020, at 7:23 AM, Malcolm Edgar <ma...@gmail.com> wrote:
> 
> Hi All,
> 
> I was hoping to use the JSON features in MySQL 5.7 and wondered if anyone had used with with Cayenne.  I was hoping to use Named Queries to perform JSON specific queries, but treat it as a String otherwise with the Cayenne classes.
> 
> Any comments or recommendations would be appreciated.
> 
> regards Malcolm