MySQL 5.7 JSON type support

[Mathieu Rodic]

MySQL came up with an implementation of the JSON type : https://dev.mysql.com/doc/refman/5.7/en/json.html

It would be nice to have it available in sqlalchemy.dialects.mysql, in the same way its PostgreSQL counterpart can be imported from sqlalchemy.dialects.postgresql.

I wouldn't mind working on the implementation, if given the required elements (some exceptions are poorly documented, e.g.: sqlalchemy.exc.CompileError: (in table 'entities', column 'data'): Compiler <sqlalchemy.dialects.mysql.base.MySQLTypeCompiler object at 0x7f1fcd4a0d10> can't render element of type <class '__main__.JSON'>).

[Mike Bayer]

1.1 is tentative here but sure, the implementation is quite simple just imitate what you see at https://bitbucket.org/zzzeek/sqlalchemy/src/ac08920284935e7e7519ce77ba369703390155dc/lib/sqlalchemy/dialects/postgresql/json.py?at=master&fileviewer=file-view-default. However the PR must have tests in test/dialect/mysql/test_types.py, at least database round trip tests.

[Mathieu Rodic]

Woking on it ([see fork)(https://github.com/mathieurodic/sqlalchemy)):

• will make a first push tomorrow, for now only the simplest access works (i.e., reading/writing/querying the whole field).
• will work on the [] operator implementation, which can be tricky as MySQL implements it as a function.

[Stephen Rauch]

What is the status of this?

[Stephen Rauch]

Just pushed some code in support of this ticket.

https://github.com/zzzeek/sqlalchemy/pull/221

[Mike Bayer]

that is impressive. i'll have to take a detailed look at it thanks!

[Mike Bayer]

• Added :class:.mysql.JSON for MySQL 5.7. The JSON type provides persistence of JSON values in MySQL as well as basic operator support of "getitem" and "getpath", making use of the JSON_EXTRACT function in order to refer to individual paths in a JSON structure. fixes #3547
• Added a new type to core :class:.types.JSON. This is the base of the PostgreSQL :class:.postgresql.JSON type as well as that of the new :class:.mysql.JSON type, so that a PG/MySQL-agnostic JSON column may be used. The type features basic index and path searching support. fixes #3619
• reorganization of migration docs etc. to try to refer both to the fixes to JSON that helps Postgresql while at the same time indicating these are new features of the new base JSON type.
• a rework of the Array/Indexable system some more, moving things that are specific to Array out of Indexable.
• new operators for JSON indexing added to core so that these can be compiled by the PG and MySQL dialects individually
• rename sqltypes.Array to sqltypes.ARRAY - as there is no generic Array implementation, this is an uppercase type for now, consistent with the new sqltypes.JSON type that is also not a generic implementation. There may need to be some convention change to handle the case of datatypes that aren't generic, rely upon DB-native implementations, but aren't necessarily all named the same thing.

→ <<cset a80bb4e5aabc>>