30 mins with MySQL JSON functions

July 23, 2018
Tags: , ,

Lire cet article en français

Note: You may also be interested by 30 mins with JSON in MySQL

Note 2: Handling JSON documents could be also done with MySQL Document Store.

JSON (JavaScript Object Notation) is a popular way for moving data between various systems, including databases.  Starting with 5.7 MySQL supports a native JSON data type (internal binary format for efficiency) and a set of built-in JSON functions that allows you to perform operations on JSON documents.

This blog post is not a complete overview of the entire MySQL JSON functions set (RTFM instead) but rather an arbitrary presentation of some of them.

Note: MySQL 8 enables an alternative way of working with MySQL as a document store. (Not cover in this blog post).

I’m using MySQL 8.0.11, downloadable here.

MySQL native JSON data type

JSON Utility Functions

JSON_PRETTY

Improve readability with JSON_PRETTY

By default, display a JSON document in MySQL looks like something like this :

You can have a prettier display with JSON_PRETTY :

JSON_STORAGE_SIZE

Return the number of bytes used to store the binary representation of a JSON document with JSON_STORAGE_SIZE.

In this collection, the heavier document is 916 bytes, the lighter is 255 and the average size is 537.2814

Note: This is the space used to store the JSON document as it was inserted into the column, prior to any partial updates that may have been performed on it afterwards.

Functions That Search JSON Values

JSON_EXTRACT (->) / JSON_UNQUOTE / ->> operator

JSON_EXTRACT (or ->) returns data from a JSON document.

JSON_UNQUOTE unquotes JSON value and returns the result as a utf8mb4 string.

->> the JSON unquoting extraction operator is a shortcut for JSON_UNQUOTE(JSON_EXTRACT())

Both queries above are similar.

If you want the same result but without quotes use ->> or JSON_UNQUOTE(JSON_EXTRACT()) :

Both queries above are similar.

JSON_CONTAINS

Search whether the value of specified key matches a specified value with JSON_CONTAINS.

JSON_CONTAINS_PATH

Indicate whether a JSON document contains data at a given path or paths with JSON_CONTAINS_PATH.

Let’s insert a dummy document in the collection restaurants

How many documents without grades? :

Ok, only 1. We can easily check the structure of this document :

A bridge between 2 models

To paraphrase David Stokes (MySQL Community Manager) in his book MySQL and JSON – A practical Programming Guide.

The advantages of traditional relational data and schemaless data are both large. But in some cases, data in a schema needs to be schemaless, or schemaless-data needs to be in a schema. 

Making such metamorphosis is very easy to do with MySQL!

Relational to JSON

JSON_OBJECT

Evaluates a list of key-value pairs and returns a JSON object containing those pairs with JSON_OBJECT.

A traditional SQL query with a relational result set. The JSON document output non-JSON data :

This result set could be convert in a JSON format, actually a JSON object :

Other example :

JSON_OBJECTAGG

Takes two column names or expressions and returns a JSON object containing key-value pairs with JSON_OBJECTAGG.

Grouping rows are very often useful. This why we implemented some JSON aggregate functions like this one.

  • Note
    • It’s usually not a good idea to use ORDER BY RAND(). It works like a charm for small dataset, but it’s a true performance killer with huge datasets.
    • The best practice is to do it in the application or pre-compute random value in the database.

JSON_ARRAY

Evaluate a list of values and returns a JSON array containing those values with JSON_ARRAY.

Next example is an hierarchical query using a recursive  Common Table Expression aka recursive CTE (or WITH Syntax)

JSON format output with JSON_OBJECT & JSON_ARRAY :

JSON_ARRAYAGG

Aggregate a result set as a single JSON array whose elements consist of the rows with JSON_ARRAYAGG.

With this other JSON aggregate functions we will see different combinations of JSON format output :

JSON to Relational

Ok let’s transform JSON data into relational data!

JSON_TABLE

Extract data from a JSON document and returns it as a relational table having the specified columns with JSON_TABLE.

Actually I highly recommend you to spend time in the documentation for this powerful function, that allows you to map JSON data into a temporary relational table and then query from this table.

Enough blabla, let’s see some examples :

JSON_TABLE – Nested Data

Walk down the JSON document path and retrieve nested data.

For example, extract all grades for Hawaiian cuisine restaurants :

JSON_TABLE – Missing Data

Specify what to do when data is missing.

Default behavior :

Enforce the default behavior :

Raise an error :

Specify a default value :

Wrapup

I’ll stop here this introduction to this rich MySQL JSON functions world. I presented a subset of these functions but it definitely worth to spend some time to discover the entire set e.g. how to create, modify, indexing, … JSON documents.

Furthermore, if your workload does not fit in the relational model, you should use the MySQL 8 Document Store, that provide a CRUD API and some other cool stuffs. I’ll blog about it soon, so stay tune!

Anyway I’ll recommend you to read : Top 10 reasons for NoSQL with MySQL.

Misc

Documentation

Articles

Other resources

  • You’ll find some of the sample databases used in this article here.
  • Restaurants collection could be find here.
  • Some books that could be useful : here.

Thanks for using MySQL!

4 Responses to “30 mins with MySQL JSON functions”

  1. […] Read this post in English […]

  2. Nice article Olivier, I am a JSON lover and loved that every db support JSON. Nice work on in detailed explanation on json function in mysql.

    for JSON I love to suggest a tool which can help every json user https://jsonformatter.org, it helps me to validate and format json data.

    Thanks again Olivier for this article.

  3. […] Note 2: You may also be interested by 30 mins with MySQL JSON functions […]

  4. […] 30 mins with MySQL JSON functions […]