MySQL 5.7 supports JSON dataType field

MySQL 5.7 supports JSON dataType field

Below is the quick working syntax to play-around with JSON data Type field.

#Create a table with JSON Field.
#You can have primary key, foreign key and indexes with json type 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;


# Insert JSON Array

 INSERT INTO `book` (title, tags) VALUES
    ('JavaScript',
     '["JS", "E12", "JSON"]'
  );
 
 # Insert JSON Object
 INSERT INTO `book` (title, tags) VALUES
     ('JSP',
     '{"c" : 3, "d" : 4}'
     );


# Select Queries

SELECT id, title, tags FROM book;

SELECT id, title, JSON_TYPE(tags) FROM book;

SELECT id, title, JSON_ARRAY(tags) FROM book WHERE id = 1;

SELECT id, title, JSON_VALID(tags) FROM book WHERE id = 2;

SELECT * FROM book WHERE JSON_SEARCH(tags, 'one', 'JSON%') IS NOT NULL;

# Return available field in 0th index from JSON
SELECT title, tags->"$[0]" AS tag1 FROM book;

#Returns null on non-availability
SELECT title, tags->"$.a" AS tag1 FROM book;