How to Store JSON Data in MySQL

JSON is a popular data format used for data transfer and exchange between websites, applications & APIs. So many times you may need to store JSON data in MySQL. Of course, you can always store it as plain string or plain text but this only limits its utility, since you will not be able to do any key-value lookups via your database queries. But MySQL allows you to store JSON data as key-value pairs that makes it easy to fetch information using keys. In this article, we will learn how to store JSON data in MySQL properly.

How to Store JSON Data in MySQL

MySQL supports JSON data type as columns making it easy to store JSON data within them. But it is important to remember that since JSON data consists of key-value pairs stored as strings, you should use them only if you are unable to store the individual values in separate columns. This is because JSON columns are not indexed and cannot adhere to relational rules. So avoid using it on fields that regularly searched or updated. However, if you want to store many different attributed for an entity object, which vary from one object to another, then you using JSON column type is advisable.

Let us say you have a table book(id, title, tags) where id is a primary key, title is a string and tags is a JSON field.

CREATE TABLE `book` (
  `id` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(200) NOT NULL,
  `tags` JSON DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB;

Since each book can have different tags which can vary in kind and number, it is best to store it as JSON field.

We define JSON fields with the following syntax.

field_name JSON <constraints>

where we specify fieldname followed by JSON data type, followed by optional list of constraints. Please note, JSON column cannot have default value, be primary or foreign key.

Insert JSON Data

Once you have defined table, you can easily insert data into JSON column using INSERT statement.

INSERT INTO `book` (`title`, `tags`)
VALUES (
  'JavaScript 2022',
  '["JavaScript", "ES2022", "JSON"]'
);

You can pass the entire JSON string as-is, within single quotes for the JSON column, as shown above.

Since we have defined our column as JSON data type, MySQL offers many useful functions to work with it. You can use the following functions to insert data to JSON columns. JSON_ARRAY returns an array, JSON_OBJECT returns an object and JSON_QUOTE quotes string as JSON value.

-- returns [1, 2, "abc"]:
SELECT JSON_ARRAY(1, 2, 'abc');

-- returns {"a": 1, "b": 2}:
SELECT JSON_OBJECT('a', 1, 'b', 2);

-- returns "[1, 2, \"abc\"]":
SELECT JSON_QUOTE('[1, 2, "abc"]');

You can also use cast function to cast any value as JSON.

(CAST Value AS JSON)

Search JSON Data

MySQL also provides many useful functions to search JSON column. Here is an example to use JSON_CONTAINS() function to search a value within JSON document. It returns 1 when a match is found, else returns 0. Here is a query to select rows where tags JSON columns contains ‘Javascript’ string in it.

-- all books with the 'JavaScript' tag:
SELECT * FROM `book` WHERE JSON_CONTAINS(tags, '["JavaScript"]');

JSON Paths

One of the coolest benefits of storing data as JSON column in MySQL is that it allows you to precisely fetch JSON values using keys and indexes. Here is a simple example to get title and second tag from your JSON column in books table.

SELECT
  title, tags->"$[1]" AS `tag1`
FROM `book`;

The above query will return

'JavaScript 2022','ES2022'

Basically, you need to combine JSON column name along with JSON path using -> operator to get the required value.

JSON_colum_name->JSON_path
# for example
tags->"$[1]"

Let us say your JSON column ‘test’ contains the following data

{
  "a": 1,
  "b": 2,
  "c": [3, 4],
  "d": {
    "e": 5,
    "f": 6
  }
}

Here are some sample paths to help you understand how to fetch different types of data in your SQL query using JSON paths

$.a returns 1
$.c returns [3, 4]
$.c[1] returns 4
$.d.e returns 5
$**.e returns [5]

So if your column is called test, you can use the above JSON paths along with your JSON column name to fetch data precisely.

test->"$.a" returns 1
test->"$.c" returns [3, 4]
test->"$.c[1]" returns 4
test->"$.d.e" returns 5
test->"$**.e" returns [5]

You can even use JSON paths in WHERE clause. Here is an example to select title and first tag from books table where the first tag is not null.

SELECT
  title, tags->"$[0]" AS `twitter`
FROM `books`
WHERE
  tags->"$[0]" IS NOT NULL;

In this article, we have learnt how to store JSON data in MySQL as well as how to retrieve JSON data using JSON paths.

How to Store JSON Data in MySQL