MySQL JSON Support & Using JSON Fields

If you’re integrating TeamSQL with MySQL version 5.7.8 (or later), you can access data in JSON (JavaScript Object Notation) documents.

 

We’ll start by creating our table. We can easily create tables in TeamSQL by running a Create procedure using a query window.

CREATE TABLE `sakila`.`json_examples`(
`first_name` VARCHAR(50),
`last_name` VARCHAR(50),
`languages_spoken` JSON)
ENGINE=`InnoDB` AUTO_INCREMENT=1;

 

We’ll create a simple three-column table that will store users’ first and last names, as well as any languages they may speak and their level of fluency in that language. Notice that we set the languages_spoken column to the JSON type.

 

Creating New Records

Now that we have our table, we’ll need to add data to it. Creating a new record that includes JSON data is pretty simple since all you need to do is set a valid JSON object as the field value when writing your insert statement.

INSERT INTO json_examples(first_name, last_name, languages_spoken) VALUES('John', 'Smith', '{"Fluent": "English", "Intermediate":"Spanish"}');

Alternatively, you can use the built-in JSON_OBJECT function, which accepts a list of key/value pairs and returns a JSON object. For example, providing this

JSON_OBJECT(key1, value 1, key2, value2)

yields

{
"key1": "value1",
"key2": "value2"
}

 

Using this function in our insert statement yields this:

INSERT INTO json_examples(first_name, last_name, languages_spoken) VALUES('Jane','Doe','{"Fluent": "German", "Beginner": "French"}');

 

You can check for your newly-created records.

 

Reading JSON Data Values

Now that we have some JSON data in our table, we need to be able to read it. Using path expression, we narrow down which part of the JSON with which we want to work. We then provide the path expression to navigate through to the JSON_EXTRACT function.

SELECT * FROM `json_examples`
WHERE JSON_EXTRACT(languages_spoken, '$.Fluent')='English'

 

In the above example, we pass languages_spoken as the first argument to the JSON_EXTRACT function, indicating it’s the column to which we want to apply the path expression. Next, we use the $ to tokenize the object with which we’re working. Essentially $.Fluent’ translates to “return the value associated with the Fluent key.”

In summation, we’re looking for users who are fluent in English.

 

Note that we could have also use the alias symbol (->) instead of the full JSON_EXTRACT function syntax:

SELECT * FROM `json_examples`
WHERE `languages_spoken` -> '$.Fluent'='English'