In MySQL 5.7 we introduced JSON functionality into the MySQL Server. This work included the introduction of a JSON data type, virtual columns and a set of approximately 20 SQL functions that allow you to manipulate and search JSON data on the server side.…
Category Archives: JSON
Upgrading JSON data stored in TEXT columns
One of the more frequently asked questions with MySQL 5.7 is “How can I upgrade my JSON data from using TEXT in an earlier version of MySQL to use the native JSON data type?”. Today I wanted to show an example of how to do so, using sample data from SF OpenData.…
Indexing JSON documents via Virtual Columns
In a previous post I took the new JSON datatype and functions for a test drive, using sample data provided by SF OpenData. Today I will extend that example to include indexing.
Introducing Generated Columns
MySQL 5.7 introduces a new feature called generated columns.…
Taking the new MySQL 5.7 JSON features for a test drive
MySQL 5.7 introduces both a new native JSON datatype, and a set of SQL functions to be able to manipulate and search data in a very natural way on the server-side. Today I wanted to show a simple of example of these features in action using sample data from SF OpenData.…
Inline JSON Path Expressions in MySQL 5.7
MySQL 5.7.9 has a new feature, that simplifies queries that deal with JSON data and makes more human-readable: inlined JSON path expressions. Now you can do following:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
mysql> CREATE TABLE employees (data JSON); Query OK, 0 rows affected (0,01 sec) mysql> INSERT INTO employees VALUES ('{"id": 1, "name": "Jane"}'); Query OK, 1 row affected (0,00 sec) mysql> INSERT INTO employees VALUES ('{"id": 2, "name": "Joe"}'); Query OK, 1 row affected (0,00 sec) mysql> SELECT * FROM employees WHERE data->'$.id'= 2; +--------------------------+ | data | +--------------------------+ | {"id": 2, "name": "Joe"} | +--------------------------+ 1 row in set (0,01 sec) |
Let’s take a closer look at the expression in the WHERE clause.…
Virtual Columns and Effective Functional Indexes in InnoDB
In April, I wrote a blog post introducing the new InnoDB virtual column and effective functional index work for the JSON lab release. Now the feature is officially in MySQL 5.7.8. It’s worth revisiting the topic again in order to write about what is in the 5.7 Release Candidate, along with providing some additional examples.…
Getting Started With MySQL & JSON on Windows
MySQL is getting native support for JSON. This blog post will show you how to quickly get the MySQL server with these new features running on your Windows rig and how to write a small C# program in Visual Studio 2015 that stores a JSON document using the new native JSON data type.…
MySQL 5.7 Labs — Inserting, Updating, and Deleting Records via HTTP
In the MySQL Labs version of MySQL version 5.7, there is a new HTTP plugin. The HTTP plugin documentation from the labs site provides this information (from MySQL Labs):
The HTTP Plugin for MySQL adds HTTP(S) interfaces to MySQL.…
JSON Labs Release: Overview
Summary
We’re very happy to announce that the MySQL JSON Labs release is now available on MySQL Labs!
With this work, MySQL continues to grow as a hybrid SQL/NoSQL DBMS, one that can offer the best of both worlds to application developers.…
JSON Labs Release: JSON Functions, Part 2 — Querying JSON Data
The MySQL 5.7.7 JSON Lab release introduces a native JSON datatype. In part 1 of this blog post series, Rick Hillegas introduced the new functions for creating and manipulating JSON documents using the new native JSON data type. In this blog post we will be using some of the same sample tables and JSON documents as in part 1, so it will be helpful to read that blog post now, if you haven’t already.…