Adding a database-agnostic JSONField into Django

[Raphael Hertzog]

Hello,

in almost all projects I work on, I end up using a JSONField. Since
I value being able to run with any database, I'm not relying on
django.contrib.postgres.fields.JSONField. So I have been using
pypi's django-jsonfield maintained by Matthew Schinckel:
https://bitbucket.org/schinckel/django-jsonfield
(I have also packaged this for Debian)

I have recently discovered pypi's "jsonfield" maintained by Brad Jasper:
https://github.com/bradjasper/django-jsonfield

Both projects are very similar (and use the same python package name) and
both projects are actually looking for a new maintainer... since I rely on
something like this, I would be willing to try to merge the best of both
modules into a possible django.contrib.jsonfield or directly into the core.

We could use this opportunity to let the newly-integrated field use
DjangoJSONEncoder by default (see recent discussion about this) and
django.contrib.postgres could register its additionals lookups into the
generic field (assuming we use "jsonb" as underlying type for postgresql).

What do you think of this?

If inclusion into Django is not desired, then maybe we could aim to
at least merge both of those projects in a single "blessed" third-party
module that could be maintained in
https://github.com/django/django-jsonfield?

Cheers,
--
Raphaël Hertzog ◈ Writer/Consultant ◈ Debian Developer

Discover the Debian Administrator's Handbook:
→ http://debian-handbook.info/get/

[Yoong Kang Lim]

I would also love to see this in Django. Although I mostly use Postgres, it would be nice not to be locked into a single database vendor if I were to use a JSONField.

--
You received this message because you are subscribed to the Google Groups "Django developers  (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-develop...@googlegroups.com.
To post to this group, send email to django-d...@googlegroups.com.
Visit this group at https://groups.google.com/group/django-developers.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/20160623095656.GB22598%40home.ouaza.com.
For more options, visit https://groups.google.com/d/optout.

[Nick Sarbicki]

Same boat for me.

I constantly need the JSON field but can't always rely on postgres.

[tedjohanss...@gmail.com]

1 up on this one, would be really useful for me as well!

[Tim Graham]

Making JSONField a core field was discussed in https://groups.google.com/d/topic/django-developers/sAgYOqBUvgI/discussion

Adam said, "my recent completion of its JSONField for MySQL 5.7+ is very similar to the contrib.postgres one, copying and adapting large parts of code from Marc Tamlyn's work. We all know how much everyone loves JSON these days. If anything, this could be a core field rather than a contrib one - Oracle and SQLite also have JSON capabilities now. JSON everywhere!"

Marc said, "I'm happy for JSONField to be made a core field on the condition that it's underlying support is more than a text blob on all our main databases. It sounds like this will soon be the case."

[Aymeric Augustin]

Hello,

On 23 Jun 2016, at 15:40, Tim Graham <timog...@gmail.com> wrote:
> Marc said, "I'm happy for JSONField to be made a core field on the condition that it's underlying support is more than a text blob on all our main databases. It sounds like this will soon be the case.”


In order to discourage people from using JSONField on databases where it cannot be implemented efficiently i.e. not with a text blob, the best solution may be to add it to Django and raise a warning when it’s misused.

I’m not sure we can do that with a check, though, as we can’t introspect database routing logic (without adding a disproportionate amount of complexity). It would have to be a runtime warning.

--
Aymeric.

[Raphael Hertzog]

Hi,

Le jeudi 23 juin 2016, Aymeric Augustin a écrit :
> On 23 Jun 2016, at 15:40, Tim Graham <timog...@gmail.com> wrote:
> > Marc said, "I'm happy for JSONField to be made a core field on the
> > condition that it's underlying support is more than a text blob on all
> > our main databases. It sounds like this will soon be the case.”
>
> In order to discourage people from using JSONField on databases where it
> cannot be implemented efficiently i.e. not with a text blob, the best
> solution may be to add it to Django and raise a warning when it’s
> misused.

Why would you want to discourage people? In the recent thread "Extending
JSONField serialization", you said yourself that the use-case for a
JSONField is clearly to store and retrieve data and not to make queries on
it with smart lookups.

So while I would certainly want the generic JSONField to use the most
appropriate field type for each database, I don't think we have to wait
until all major databases have the required support. And we should
certainly not throw warnings if you use it on a database that doesn't have
any special JSON support since that is one of the selling point of this
generic module!

The generic JSONField should define the appropriate interface and have
all the logic to easily customize the behaviour for each database, but
it's also fine if it starts with using plain TEXT fields on everything
except MySQL/Postgres (which are the two databases where there are
pre-existing JSONField implementation apparently).

What would be the next logical step if I want to work on this?

Is there a need for a DEP to discuss the interface provided by this field?

[Aymeric Augustin]

On 24 Jun 2016, at 09:01, Raphael Hertzog <rap...@ouaza.com> wrote:

> Why would you want to discourage people?

Well, this seemed to be the latest decision, but I don’t care much myself.

> What would be the next logical step if I want to work on this?
>

> Is there a need for a DEP to discuss the interface provided by this field?

Given the relatively isolated scope of this feature, I think that reaching
consensus in this thread would suffice. (I don’t think we’re quite there yet.)

A DEP could help, though, so if you’d like to write a short one, please go
ahead!

--
Aymeric.

[Shai Berger]

On Friday 24 June 2016 10:01:36 Raphael Hertzog wrote:
> Hi,
>
> Le jeudi 23 juin 2016, Aymeric Augustin a écrit :
> > On 23 Jun 2016, at 15:40, Tim Graham <timog...@gmail.com> wrote:
> > > Marc said, "I'm happy for JSONField to be made a core field on the
> > > condition that it's underlying support is more than a text blob on all
> > > our main databases. It sounds like this will soon be the case.”
> >
> > In order to discourage people from using JSONField on databases where it
> > cannot be implemented efficiently i.e. not with a text blob, the best
> > solution may be to add it to Django and raise a warning when it’s
> > misused.
>
> Why would you want to discourage people? In the recent thread "Extending
> JSONField serialization", you said yourself that the use-case for a
> JSONField is clearly to store and retrieve data and not to make queries on
> it with smart lookups.
>

So, what you're suggesting is actually a thin wrapper around TextField, which
deals with the JSON encoding?

> Is there a need for a DEP to discuss the interface provided by this field?

I'm not sure the scope justifies a DEP, but some discussion of the API should
happen. Personally, I'm not yet convinced that -- assuming indeed we are
talking about a thinly-wrapped TextField -- such a field needs to have a
canonical implementation (one declared so by the Django project).

Shai.

[Raphael Hertzog]

Le vendredi 24 juin 2016, Aymeric Augustin a écrit :
> On 24 Jun 2016, at 09:01, Raphael Hertzog <rap...@ouaza.com> wrote:
> > Why would you want to discourage people?
> Well, this seemed to be the latest decision, but I don’t care much myself.

AFAIK only Marc Tamlyn insisted on such a requirement to have native JSON
support on all main databases.

While this is nice and should be encouraged, it's also the kind of
requirement which discourages people from working on it.

We have working codebase(s) for a generic JSONField. I would like to pick
one, clean it up, strip it down and make it easy to extend so that native
JSON support for more database can be easily added while still keeping
the same user facing API.

The non-portable parts (lookups mainly?) should ideally be enabled via
hooks that applications like django.contrib.postgres could rely on.

> Given the relatively isolated scope of this feature, I think that reaching
> consensus in this thread would suffice. (I don’t think we’re quite there yet.)

Yes, few core team members shared their opinions so far, but we clearly have
user interest for this feature.

> A DEP could help, though, so if you’d like to write a short one, please go
> ahead!

Well, if there's consensus I'd rather spend time on code to be reviewed, but
if others core team members would rather discuss the design at at more
abstract level before deciding whether it's a good move, then I can draft a DEP.

[Raphael Hertzog]

Hi,

Le vendredi 24 juin 2016, Shai Berger a écrit :
> > Why would you want to discourage people? In the recent thread "Extending
> > JSONField serialization", you said yourself that the use-case for a
> > JSONField is clearly to store and retrieve data and not to make queries on
> > it with smart lookups.
>
> So, what you're suggesting is actually a thin wrapper around TextField, which
> deals with the JSON encoding?

That's the simplest answer that fullfills this requirement but I'm not
suggesting that.

I believe that a "core" JSONField should supersede all the existing
implementations:
- the external implementations which are close to your description
(but got multiple related features over the years, not all of which are
worth keeping)
- the django.contrib.postgres one
- the django-mysql one (that I wasn't aware of until Tim pointed out
the older thread on this topic)

So, in my view, it builds on the simplest implementation while relying
on the native JSON database type when they are available and it should
be easy to extend for the non-portable JSON-related features that each
database might want to make available (special lookups, etc.).

Even with a "thin wrapper", as soon as you rely on the native database types,
you quickly get into non-trivial integrations problems:
- with migrations from TextField to JSONField and vice-versa
ex: https://bitbucket.org/schinckel/django-jsonfield/issues/54/migrating-from-textfield-to-jsonfield-with
- with configuration requirements of the database connector that might have an impact
on other parts of Django
example where pyscopg does under the hood JSON conversion for us:
https://bitbucket.org/schinckel/django-jsonfield/pull-requests/32/make-from_db_value-compatible-with

On top of the model field, there would also be a FormField validating the
JSON input and a widget to present the JSON in a human-readable (indented)
form.

Among the other features that would be interesting to have is the
possibility to swap in a more advanced serializer (which would be able
to deal with more types of objects than the default one).

> I'm not sure the scope justifies a DEP, but some discussion of the API should
> happen. Personally, I'm not yet convinced that -- assuming indeed we are
> talking about a thinly-wrapped TextField -- such a field needs to have a
> canonical implementation (one declared so by the Django project).

Did my answer clear your doubts?

[Cristiano Coelho]

I would like it. I honestly one use JsonField to store json data, not really to query it, and having postgres store it a very efficient way is a very nice plus compared to plain text storage. Then the postgres features to query json data are godlike but I wouldn't mind trading something to allow current json field work with every database, mostly so I can still develop on SQLite while running postgres on production.

[Shai Berger]

On Friday 24 June 2016 14:49:54 Raphael Hertzog wrote:
>
> Did my answer clear your doubts?
>

It moves my opinion of the feature from -0.5 "are we sure we need this" to -0
"I won't stand in your way".

Shai.

[Brad Jasper]

Current maintainer of https://github.com/bradjasper/django-jsonfield here and I would definitely support this.

-Brad

[Sérgio Basto]

Yeah I got similar problems , I'm working with django 1.11 and rest framework I wrote [1]

we got about 5 options for JSON Fields

https://pypi.python.org/pypi/django-jsonfield 
https://pypi.python.org/pypi/jsonfield
from rest_framework.serializers import JSONField
from django.contrib.postgres.fields import JSONField
https://pypi.python.org/pypi/django-mysql

only django-jsonfield (of bitbucket) serialize json into on dictionary , the others put me json fields in a string which is not an option ...
 
but in this thread show some integration problems with django-jsonfield , what should I use to have Mysql JSON fields ?
how do the serialization ? , I need work the content of the field .

Thanks

[1]
https://github.com/encode/django-rest-framework/issues/5111

[Johannes Wilm]

Hey,

is anyone working on this? Have this patchwork of solutions for different databases systems in additionally the uncertainty associated with not knowing which of the postgresql solutions will survive in the long run really makes it a difficult decision to switch to anyone of these from a plain TextField. Even if there is not there is no consensus to add it to Django proper, could not at least the maintainers of the different postgresql packages come up with a basic solution between themselves with a simple fallback to TextField whenever one is using a different database? 

[Joe Tennies]

So, I think a DEP is needed. I think we should start with the following for every database supported by Django (and the more common 3rd party ones):

If every major DB supports JSON out of the box, we may  I mean we just forced everyone to go to Python3. We haven't hit an LTS since. Maybe bumping up a few DB versions is acceptable too?

I actually did this research on Oracle, and it looks like the currently supported version of Oracle supports JSON and should out of the box with the supported version!

***********

ORACLE

Django supported minimum version (server/driver):

Server:12.1; cx_Oracle: 5.2

Minimum version supporting JSON Field (server/driver):

Server: 12.1 (maybe earlier); cx_Oracle: 5.2+ (I think so as it's just a special string field)

Notes: Not a JSON specific field. Stored as VARCHAR2, CLOB, or BLOB. Suggest storing with is_json constraint to validate input is JSON that Oracle can parse.

Reference: https://docs.oracle.com/database/121/ADXDB/json.htm

***********

OTOH, MySQL looks like it'll need a version bump.

***********

MySQL

Django supported minimum version (server/driver):

Server:5.5; mysqlclient; 1.3.7; MySQL Connector/Python: 1.1

Minimum version supporting JSON Field (server/driver):

Server: 5.7.8; mysqlclient; ???; MySQL Connector/Python: ???

Notes: Implemented as a JSON field.

Reference: https://dev.mysql.com/doc/refman/5.7/en/json.html

***********

Quick research shows that PostgreSQL (at least from 9.3 the current minimum version), SQLite (via json1), MariaDB 10.0.1+, SQL Server 2016 (that's pretty new), Azure SQL Database (https://docs.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server), DB2 (since z/OS V3.1 and Win/Linux/UNIX since 10.5) all support it. It appears that FirebirdSQL may not (http://tracker.firebirdsql.org/browse/CORE-5148), and I don't think SQL Anywhere does either (based on quick googling). I don't think I missed any major relational DBs (though I'm sure people will comment with one I missed).

There's always the idea that it could be a standard contrib module that has higher standards. I'm thinking something along the lines of GeoDjango here. Otherwise, I think one could just add a caveat that use of a JSON field requires a DB that supports JSON and fails if the version is too low or missing the extension (like json1 for sqlite).

Then one should write a feature matrix to figure out what features are supported where. This can help drive the API. It also may point out that some relatively common feature doesn't appear until a newer version than just basic JSON support. With the general pervasiveness of JSON support, I'm not sure that one would need to write a full implementation based on a text field, though a couple features may require more hackery for certain DBs.

Finally one has to take into consideration whether fields can be migrated on each Database and whether Django should help (maybe it shouldn't and force people to write migrations manually).

--
You received this message because you are subscribed to the Google Groups "Django developers (Contributions to Django itself)" group.

To unsubscribe from this group and stop receiving emails from it, send an email to django-developers+unsubscribe@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.

Visit this group at https://groups.google.com/group/django-developers.

To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/54f8d071-dc0c-47f6-b63e-c9ec68288527%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

Joe Tennies
ten...@gmail.com

[Carlton Gibson]

This has come up again, with an example implementation for Oracle: 

https://code.djangoproject.com/ticket/29821

Anyone know of an SQLite implementation?

[Jon Dufresne]

> Anyone know of an SQLite implementation?

A quick search shows the JSON1 extensions exists:https://www.sqlite.org/json1.html

According to the release history (https://sqlite.org/changes.html) this was added in version 3.9.0 (2015-10-14).

But I have no direct experience working with it.

--
You received this message because you are subscribed to the Google Groups "Django developers (Contributions to Django itself)" group.

To unsubscribe from this group and stop receiving emails from it, send an email to django-develop...@googlegroups.com.
To post to this group, send email to django-d...@googlegroups.com.

Visit this group at https://groups.google.com/group/django-developers.

To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/215a0e1c-6947-4375-ba1d-38d310c32411%40googlegroups.com.

[Carlton Gibson]

Sorry, what I meant was a Django field. (I wasn't clear enough.) 

Charles Leifer has good posts covering SQLite+JSON http://charlesleifer.com/

His Peewee ORM has a JSONField http://docs.peewee-orm.com/en/latest/peewee/sqlite_ext.html#sqlite-ext

I was just wondering if someone knew if someone had already wrapped up a Django equivalent?

(If so we'd have reference implementations for all the supported DBs...)

[Adam Johnson]

I'd be up for helping with a database-agnostic one. One thing to note about MySQL world is that MariaDB diverges more from MySQL here and I haven't found the time to fix the differences here in Django-MySQL's JSONField. I'd get on with it if I knew someone needed the work for a DB-agnostic field though ;)

To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/593cb04e-6de6-4ccd-9b8d-d175f95d970f%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

Adam

[Tom Forbes]

I would also be very interested in helping out. I don’t know if this is useful, but I’ve had a brief look at the feature support compared to our current baseline Postgres features:

MySQL: https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html - JSON_CONTAINS_PATH() can implement most of our has_* filtering, and we can also do array index lookups. Will need to convert lookups to path expressions. There also appears to be some nastiness relating to quoting we may need to take care of. Also supports partial updating of JSON columns. The ->> operator can be used for filtering.

Sqlite: https://www.sqlite.org/json1.html - Some trickiness, sqlite preserves json objects with duplicate keys. Can do simple queries using json_extract(), but we may need to use json_each() for some lookups (which adds a bit of complexity). Supports updating values in-place as well.

Oracle: https://docs.oracle.com/en/database/oracle/oracle-database/18/adjsn/query-json-data.html#GUID–119E5069–77F2–45DC-B6F0-A1B312945590 - Seems to support nesting lookups without needing a path expression, i.e SELECT json_field.some_key.some_value FROM table. Supports functions that take JSON paths as well so perhaps it’s easier to use those. Supports updating in place.

It seems while all 4 backends support at least the minimum feature set Postgres does, they do so in pretty different ways (different functions, different arguments, etc).

To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/CAMyDDM2qz8xTL388_LZ8Eoqnm80bcFt_W%2Bu3axcC%2BC7g52PUHQ%40mail.gmail.com.

[Raphael Michel]

Hi everyone,

I used the sprints at DjangoCon US to work on this issue in form of a third-party package. Mainly, I created a subclass of django.contrib.postgres.fields.JSONField that

• includes code from django-mysql to work on MySQL 5.7+ as well
• does some nasty hacks to even work on MariaDB 10.2.7+
• gracefully falls back to storing JSON strings in a TextField on all other databases

Unfortunately, the differences between the JSON implementations (especially between MariaDB and MySQL) seem to be quite large. Since my primary objective was to prove that it is possible to create a multi-database field, I resolved this mostly with some back-and-forth data conversion hacks, instead of searching for a clean solution. Not all features of the PostgreSQL version are possible on MySQL/MariaDB, but the most useful ones all are.

This would probably need a lot of cleaning up before considering to move it to Django core, and you'd likely also want to explore integrating Oracle/SQLite for that. I will probably not have time to do that, but if someone else wants to, this might provide a starting point.

Here's the repository: https://github.com/raphaelm/django-jsonfallback

Best

Raphael

P.S. Thanks to all organizers and attendees for an amazing conference <3

[Tom Forbes]

Awesome work! Would you mind elaborating on the differences between mysql and mariadb?

--

You received this message because you are subscribed to the Google Groups "Django developers (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-develop...@googlegroups.com.
To post to this group, send email to django-d...@googlegroups.com.
Visit this group at https://groups.google.com/group/django-developers.

To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/330b1ade-ab27-4135-9952-d6a188789c31%40googlegroups.com.

[Adam Johnson]

The main thing is that MySQL added a binary JSON type, whilst MariaDB just made the JSON type an alias for TEXT (plus the corollaries that implies): https://mariadb.com/kb/en/library/json-data-type/

To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/CAFNZOJO8u3oMwH8Mra_eyGqaG%3DKs%3D5d%3DmF8g9U8YBAxswKxCcQ%40mail.gmail.com.

For more options, visit https://groups.google.com/d/optout.

--

Adam

[Raphael Michel]

Hi,

On Saturday, October 20, 2018 at 10:48:04 PM UTC+2, Adam Johnson wrote:

The main thing is that MySQL added a binary JSON type, whilst MariaDB just made the JSON type an alias for TEXT (plus the corollaries that implies): https://mariadb.com/kb/en/library/json-data-type/

Yes, this is the main difference, as MySQL requires CAST(x AS JSON) calls in many places while MariaDB does not support CAST(x AS JSON). I also noted a number of oddities with regards to case-(in)sensitivity when comparing values extracted from JSON, but I didn't research this deeper.

Best

Raphael

[nikesh...@nituk.ac.in]

Hello ,

I would like to work on this. I recently started working on django. I want to participate in GSoC so this might work for me.

Would you mind helping me. 

[Dan Davis]

I'm for this.   My only advice is that only some versions of Oracle have a native JSON type. The oracle backend should probably use some query to determine whether the Oracle instance supports JSON field, or there could be a flag in OPTIONS about tihs.

[Adam Johnson]

Dan we don't need a user-operated flag, instead we would introspect the database version in use and have a database backend feature. For example here's where the MySQL backend determines if the "over" clause is supported for window functions: https://github.com/django/django/blob/master/django/db/backends/mysql/features.py#L86