json datatype #245
Description
MySQL 5.7 and MySQL 8.0 offer the JSON datatype as do the new releases of other major RDBMSs (RedShift, PostgreSQL, Oracle). This will allow storing semistructured data in fields while keeping its components addressable in queries.
This has the potential for greatly simplifying some schema design. For example, instead of having different tables for different visual stimuli, one table can be used with a json field for the stimulus parameters, which can differ for each type of stimulus. This will simplify the great majority of queries.
Normalization and strict schemas are essential for the overall design but sometimes a less rigid approach is more appropriate for cases where dependent attributes are allowed to vary between tuples.
I propose to add support for the json datatype. Inserting a structure will add it as a json object (unordered set of key-value pairs). Simplified restriction syntax will enable restrictions by individual values of a json object. One should be able to do something like
vis.Condition() & 'parameters.contrast>0.5'where parameter is a json type and contrast is a key value in some of the tuples. This will return all conditions for which there is a contrast key and its value is greater than 0.5.