Avoid redundant field using JSON type in MySQL

jsonMySQLnosqlrelational-databaseschema

I have experienc with designing relational dbs but first time attempting to store JSON in a mysql table. I am using mysql 5.7.8 which has the ability to store json data type as a column.

When I create the schema for the table should I have two columns where one column is a key and the second is to store the JSON document? I was thinking the JSON document would also have the key so this would make it redundant. Or is there a way to just have the json document and a search index could be made over the key field in the json document. In our case we are just managing "test run" data so a timestamp is sufficient for a test run's key.

I suppose another route is to remove the key from the document.

for example should timestamp be column one the PK and JSON data type be column where an entry for the JSON would be:

{
  start_time: YYYY-MM-DD'T'HH:MM:SS.mmm,
  end_time: YYYY-MM-DD'T'HH:MM:SS.mmm,
  username: jchan,
  total_samples: 456,
  manually_triggered: true,
  target_number_samples: 4,
  detected_number_samples: 3
}

Or should I just have a single column with the json data and there is a way to create an index over "start_time". Or should start_time be omitted because it would be redundant with the key column? Should we be introducing something like mongo db for this?

There are more and future complex objects that could be incorporated into this JSON document which I believe will justify the flexibility to just storing the doc over traditional relational approach.

Best Answer

From DocBrown's advice I've learned that a generated column could be defined off of an attribute from the JSON document. Here is the table creation sql:

CREATE TABLE `test_run` (      `start_time` TIMESTAMP GENERATED ALWAYS
AS (`json_doc` ->> '$.start_time') STORED KEY NOT NULL PRIMARY KEY,   
`json_doc` JSON NOT NULL );

Note our data gets more complex which justifies use of JSON. The example simplified the data for clarity.

https://www.compose.com/articles/mysql-for-json-generated-columns-and-indexing/

Related Topic