JavaScript Object Notation with SQL

#json#json_contains#json_extract#mysql#sql#structured-data#strukturierte-daten

Databases are wonderful for storing structured data. Sometimes, though, for various reasons that isn't enough, and you'd rather store more complex data structures via SQL. For example, you want to store metadata about an image (so-called EXIF data) in the database, and for simplicity let's assume it consists of the following values:

Focal length
Exposure time
Author information

We could now either create table fields for each value

CREATE TABLE `photos` (
  `focal_length` INT,
  `exposure_time` INT,
  `author` VARCHAR
);

or write the data in JSON format into one table column

{"FocalLength": 55, "ExposureTime": 120, "Author": "devdave"}

The real advantage of the JSON format is that object instances can be quickly (de)serialized to/from JSON. If you have an object in PHP, for example,

class Photo {
  public $focalLength = 55;
  public $exposureTime = 120;
  public $author = "devdave";
}
$photo = new Photo();

we can quickly serialize it via

json_encode($photo)

and have the instance in hand as a JSON string that we can store in the database. Updating the database every time the structure of the Photo class changes is rather tedious and leaves room for errors. That's why in this case it makes sense to store the data directly in JSON format in the database. But how can we then access the data later?

1. JSON_EXTRACT(%DATA%, %PATH%)

With JSON_EXTRACT you can extract data from the JSON string. As the first parameter we pass the JSON string that provides our data. In our case that would be the name of the column containing the metadata. Under path you specify the path to the attribute that should be returned. Assume our JSON string looks like this:

{"FocalLength": 55, "ExposureTime": 120, "Author": "devdave"}

then we can access the value "Author" like this:

SELECT
JSON_EXTRACT('{
  "FocalLength": 55,
  "ExposureTime": 120,
  "Author": "devdave"
}', '$.Author');

We can access deeper nested levels accordingly. If the field "Author" is located under "People", we could access it as follows:

SELECT
JSON_EXTRACT('{
  "FocalLength": 55,
  "ExposureTime": 120,
  "People": {"Author": "devdave", "Model": "Hannah"}
}', '$.People.Author');

Lists of values can be accessed via an index. If we have several authors, we can access the first one (= "devdave") like this:

SELECT JSON_EXTRACT(
  '{"FocalLength": 55, "ExposureTime": 120,
  "People": {"Author": ["devdave", "john wayne"],
  "Model": "Hannah"}}',
  '$.People.Author[0]');

2. JSON_CONTAINS(%HAYSTACK%, %NEEDLE%, '%PATH%')

JSON_CONTAINS returns information about whether a value is found under a key. As always, we first specify the JSON string, then the element we're looking for (in the following 120 and Max respectively), and then the (hypothetical) path to the element.

JSON_CONTAINS('{
  "FocalLength": 55,
  "ExposureTime": 120,
  "People": {
    "Author": ["devdave", "john wayne"],
    "Model": "Hannah"
  }
}',
'120',
'$.ExposureTime');

returns 1.

JSON_CONTAINS('{
  "FocalLength": 55,
  "ExposureTime": 120,
  "People": {
    "Author": ["devdave", "john wayne"],
    "Model": "Hannah"
  }
}',
'"Max"',
'$.People.Author[0]');

returns 0.

Sources: dev.mysql.com/doc/refman/8.0/en/json-search-functions.html