JSON

Objectives:

  • To learn what is JSON field in database
  • To know the pros and cons of using json field
  • To apply the best practices in using JSON
  • To know the different JSON functions used for TEXT, VARCHAR data typed fields

JSON Functions

What is JSON and how it is stored in Database?

From the basics, you were already familiarized with primitive data types such as INT for numbers, DATETIME for calendar, VARCHAR for strings, TEXT for larger strings, and so on. 

But did you know that we can store also JSON-format strings in VARCHAR and TEXT?

JSON stands for JavaScript Object Notation. It is a popular syntax representing structured data popularized by Javascript. Notice the example below having enclosed curly brace, with key-value pairs, and comma for every set. 

{ challenges_completed: 5, current_challenge_id: 6, current_module_id:1 }

Is it amazing that we can store various information in just a single table field? WOW! With proper usage, imagine how this can save you up by having 1 field versus 3 fields or more in your table! :D


Note: For naming convention, we usually pattern it with _json at the end. (Ex. progress_json)

When to use JSON-valued field?

There are common mistakes by companies when talking about fields (and you should be aware of). 

Now, what fields do we put together as JSON and store in the TEXT field? Hmmm. That should be fields ...