Why doesn't Moqui support the JSON datatype in MySQL or any other datasources?

Community
1

Hi everyone,

I’ve been working with Moqui and MySQL, and I wanted to leverage MySQL’s JSON datatype to handle flexible, dynamic data more efficiently. However, Moqui doesn’t seem to support this data type. MySQL offers powerful JSON functions like JSON_EXTRACT() and JSON_ARRAYAGG(), which could be useful in my project.

I’m curious to know why Moqui doesn’t have built-in support for the JSON datatype in MySQL. Are there any technical reasons or design considerations that prevent it?

What If I will add the support for JSON type, and use it in my project?

Thanks!
Purushottam Khedre

2

So I would guess the easiest way to do this is by adding a new <dictionary-type .../> in MoquiDefaultConf.xml (or your own conf) for the JSON structure and map it to all databases that support JSON, and some modification to DB facade is needed. If I’m not mistaken I think only newer versions of MySQL, Postgres and Oracle support this, which means we would implement a feature not supported by all databases. So the abstraction layer would be broken because it doesn’t cover all the databases.

To me I prefer database independence, and utilizing database-specific features might compromise that. I could be mistaken of course but these are my first impression thoughts/

4 Likes
3

Thanks for your response!

I completely agree with maintaining database independence, and I understand the concern about breaking the abstraction layer by utilizing database-specific features.

I think a potential solution could be to treat the JSON datatype (in the database) as a String at the abstraction layer level. Since MySQL, PostgreSQL, Oracle, and H2 all support JSON in some form, we could store and retrieve JSON data as plain strings at the abstraction layer. This would allow us to take advantage of the JSON database features that support it while still maintaining database independence.

Just to clarify, the JSON datatype is already supported in the following databases, which are compatible with Moqui:

  • MySQL (since version 5.7)
  • PostgreSQL
  • Oracle
  • H2

Support for the JSON datatype has been available in these databases for over 5 years. So while not all databases supported by Moqui may offer JSON support.

Looking forward to hearing your thoughts!

4

For now, I have dropped the idea of adding support for JSON.

5

For json handling, I would use opensearch / elastic search in Moqui.

I don’t know your full requirements though, and storing it in the database might be the best option.