-2

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.

5
  • 4
    A <5 minutes look into the MySql manual revealed: you can create tables with generated columns, you can use that to create an extra column which extracts the start_time from a JSON column, and you can place an index on this generated column as well as a primary key constraint. So why don't you simply try this out?
    – Doc Brown
    Commented Sep 22, 2021 at 20:52
  • @DocBrown This looks like it will work. Thank you!
    – simgineer
    Commented Sep 23, 2021 at 2:58
  • In your example, your data looks very flat. If that's the case, you could design the table according to the fields in your JSON object, so the JSON object becomes a record in this table, and generate a JSON object when you read from the table. Commented Sep 23, 2021 at 3:42
  • @simgineer: you are welcome. I would recommend to move the solution you found from the question into an answer (and accept it), that works better with the way the site is designed. For example, it lets people vote individually for the question and the answer. And avoid to use phrases like "Edit", anyone can see the question's history and timeline using the related buttons above.
    – Doc Brown
    Commented Sep 23, 2021 at 5:26
  • @Green The data actually has more interesting data that is not necessarily flat and potentially child objects with attributes, arrays, etc.
    – simgineer
    Commented Sep 24, 2021 at 4:37

2 Answers 2

2

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/

2

I was thinking the JSON document would also have the key so this would make it redundant.

Which attribute is the key?
How is your database supposed to know?
Short of indexing all of them, how is it going to efficiently retrieve any, one, JSON document?

One of the most important factors that will govern how you store any data is how you intended to retrieve it and what you need to do with it having done so.

If you only ever need to retrieve the whole JSON thing in one go and send it somewhere, that's fine. Leave it as a [suitably-Data-Typed] slab of JSON, with an extra, old-school, properly-indexed column holding the unique key to it.

As soon as you start thinking that you'll need to start pulling "bits" out of your JSON, your Relational Alarm Bells should start ringing. JSON, XML and all the other, "composite" Data Types are the very epitome of "More than one Value in a Field", which we've been told, for years, is a Bad Idea.

Databases are, generally, very good at finding lots of little bits of stuff and putting them together. They are, frankly, rubbish at taking big chunks of stuff and splitting out the smaller bits inside them. They can do it, but it won't be quick.

2
  • Timestamp of when the test session starts is the key. A test session will have n samples in which we have a choice to embed as json elements or place into a separate table. I suppose another option is to just store everything in third normal form in non json table format. We will likely only ever have to query by session start time.
    – simgineer
    Commented Sep 29, 2021 at 18:55
  • +1 for your insight. I believe there must be some reason the NoSQL DBs have become so mainstream so was hoping to understand the best practice for them but by using mysql and not have to introduce a separate DBMS like mongodb / couchdb. Would like to know the benefits of this nosql approach over the relational approach I'm already familiar. The only thing i need to look up by is the key which is also in the document being stored. I suppose I could just duplicate it from the document or remove it from the document before storing the document.
    – simgineer
    Commented Oct 14, 2021 at 16:09

Not the answer you're looking for? Browse other questions tagged or ask your own question.