Support JSON fields in MySQL schema

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"

The last submitted patch, 32: 3143512-32.patch, failed testing. View results →

Random unrelated failure that was popping up today.

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