Support JSON fields in MySQL schema

Rerolled.

Is it possible to alter the database type map through hooks, or is it too low-level? i.e. could json_field handle this change directly without needing to extend the MySQL driver?

Would there be any downside to committing this without fully extending the data type system to support JSON?

What are the next steps? Should we start with this being a recommended patch for sites that want to use JSON Field?

More simply - when a field is created using JSON Field and this patch, should the column in the database have the column "type" set to "json"? Because it's showing up as "longtext" in MariaDB 10.4 in ddev:

MariaDB [db]> describe node__field_json;
+------------------+------------------+------+-----+---------+-------+
| Field            | Type             | Null | Key | Default | Extra |
+------------------+------------------+------+-----+---------+-------+
| bundle           | varchar(128)     | NO   | MUL |         |       |
| deleted          | tinyint(4)       | NO   | PRI | 0       |       |
| entity_id        | int(10) unsigned | NO   | PRI | NULL    |       |
| revision_id      | int(10) unsigned | NO   | MUL | NULL    |       |
| langcode         | varchar(32)      | NO   | PRI |         |       |
| delta            | int(10) unsigned | NO   | PRI | NULL    |       |
| field_json_value | longtext         | YES  |     | NULL    |       |
+------------------+------------------+------+-----+---------+-------+
7 rows in set (0.001 sec)

The queries work as expected in MariaDB:

MariaDB [db]> SELECT entity_id, JSON_VALUE(field_json_value, '$.type') as 'type' FROM node__field_json;
+-----------+------+
| entity_id | type |
+-----------+------+
|         4 | 1    |
+-----------+------+
1 row in set (0.001 sec)

MariaDB [db]> SELECT * FROM node__field_json WHERE JSON_VALUE(field_json_value, '$.type') = 1;
+---------+---------+-----------+-------------+----------+-------+------------------+
| bundle  | deleted | entity_id | revision_id | langcode | delta | field_json_value |
+---------+---------+-----------+-------------+----------+-------+------------------+
| article |       0 |         4 |           6 | en       |     0 | {"type":true}    |
+---------+---------+-----------+-------------+----------+-------+------------------+
1 row in set (0.001 sec)

Thanks. I was doing some final testing of JSON Field prior to a 1.0 release and was confused by what I saw in ddev's default MariaDB database, but this explains it.

Wow, that's a bit unexpected and disappointing. If I understand correctly, this means that in MariaDB the whole new JSON support is in fact just a glorified string operation, right? I'm also surprised that they claim that their benchmarks don't show a performance difference. I would have thought that it's better to store sth. preparsed in binary form - especially when you try to lookup something.

Exciting to see this happening.

Does this mean that now we can do this instead of use a serialized blob store?

      'data' => [
        'type' => 'blob',
        'mysql_type' => 'json',
        'pgsql_type' => 'jsonb',

Other than this patch and query functions, is there anything else to be aware of?

Moving to NW for the test coverage. If the solution changes per the current discussion please update issue summary also.

I wonder if it wouldn't be nice to have a "shortcut" so instead of this:

data' => [
        'type' => 'blob',
        'mysql_type' => 'json',
        'pgsql_type' => 'jsonb',
]

just this would be possible:

data' => [
        'type' => 'json',
]

PostgreSQL has two datatypes, json and jsonb with slightly different behaviour - it looks like MySQL's json type is more similar to PostgreSQL's jsonb, in that it does some normalization like removing duplicate object keys and extra whitespace when a value is stored in the field.

Should the MySQL driver define both to match the current patch for the PostgresSQL driver?

  'json:normal'     => 'JSON',
  'jsonb:normal'    => 'JSON',

The differing behaviour is probably something to note in documentation, and that jsonb will probably provide the best consistency of behaviour between db backends.

Looks the most compatible way is to provide only json:normal OOB and use mysql_type and pgsql_type to vary

re-parenting

Here's a patch with test for returned data types

The issue I found comparing to sqlite and pgsql is that syntax for ->> operator is bit different

- sqlite, pgsql - select [field] -> "key1" ->> "key2"
- mysql is select [field] -> '$.key1.key1' or braces for array index select [field] -> '$.key1[0]'

Fix cs

simplified tests

Tests appear green

Verified the test locally running on ddev with mysql:8.0 I get
Undefined array key "json:normal"

Random unrelated failure that was popping up today.

Per #3343634-14: Add "json" as core data type to Schema and Database API this is being consolidated into the parent issue for a single change.