MYSQL storing JSON - Best way?

Question

I am requiring my students to keep a 3 day food log. I want to use that data to display on their personalize web dashboard and will be bringing that info in as a JSON. The primary data I'll display is the date, food they ate, calories from fat, protein, carbs and total calories.

What would be the best way to structure my database? Set up a table with a JSON data type?

Here is a sample of the JSON data I'll need to store. This is one food item but I'll be saving 3 days worth:

{
    "foods":[
        {
            "isFavorite":true,
            "logDate":"2011-06-29",
            "logId":1820,
            "loggedFood":{
                "accessLevel":"PUBLIC",
                "amount":132.57,
                "brand":"",
                "calories":752,
                "foodId":18828,
                "mealTypeId":4,
                "locale":"en_US",
                "name":"Chocolate, Milk",
                "unit":{
                    "id":147,
                    "name":"gram",
                    "plural":"grams"
                },
                "units":[226,180,147,389]
            },
            "nutritionalValues":{
                "calories":752,
                "carbs":66.5,
                "fat":49,
                "fiber":0.5,
                "protein":12.5,
                "sodium":186
            }
        }
    ],
    "summary":{
        "calories":752,
        "carbs":66.5,
        "fat":49,
        "fiber":0.5,
        "protein":12.5,
        "sodium":186,
        "water":0
    },
    "goals":{
        "calories": 2286
    }
}

Thanks in advance for any recommendations.

Tim

In a relational database, you should assume JSON is the wrong solution, except in extraordinary cases. You should use normal tables and columns by default. — Bill Karwin, Jan 31 '19 at 18:30
MySQL marketing is promoting their JSON features currently, because they're trying to compete against MongoDB. But JSON is really inefficient and difficult to use with SQL. You might like my presentation [How to Use JSON in MySQL Wrong](https://www.slideshare.net/billkarwin/how-to-use-json-in-mysql-wrong). — Bill Karwin, Jan 31 '19 at 18:32
@BillKarwin What's "wrong" for MySQL is actually ridiculously amazing in Postgres and that platform's ability to query, index, and alter JSON data, so maybe MySQL will catch up some day. — tadman, Jan 31 '19 at 19:19
"MySQL marketing is promoting their JSON features currently, because they're trying to compete against MongoDB." whats wrong? @tadman Well MySQL's 5.7 and 8.0 "NoSQL" (document store) features and the document store API (X Plugin/X protocol) which needs to compete against MongoDB operate undercover as InnoDB table with a JSON datatype column... With other words the document store API will be converted by the engine into a plain SQL queries.. — Raymond Nijland, Jan 31 '19 at 19:39
@tadman i agree with the native JSON features. PostgreSQL has indeed much better support then MySQL has. MySQL 8.0 has `JSON_TABLE()` which is a great added feature.. Besides you can index JSON in MySQL also but you need to workaround it with a generated column.which can be indexed. — Raymond Nijland, Jan 31 '19 at 19:47

MYSQL storing JSON - Best way?

0 Answers0