Use Nested Data in MySQL with JSON + Examples

NoSQL databases offers the ability to store and query nested or more flexibly structured data than traditional SQL databases has been capable of. However, using MySQL's built-in JSON abilities, you can build a hybrid solution. Here is how.

MySQL and MariaDB are both popular relational database management systems (RDBMS) that use Structured Query Language (SQL) to manage and manipulate data. SQL databases, in general, work by organizing data into tables with rows and columns. Each table represents a specific entity or concept, and each row in the table represents an instance of that entity or concept. Each column represents a specific attribute or characteristic of the entity.

The problem with SQL
The problem with NoSQL
MySQL with JSON - a hybrid solution
Create and query more advanced, nested data structures with MySQL and JSON
How to search and filter based on JSON values in MySQL
How to deal with unequally structured JSON data
Best practices for using JSON in MySQL/MariaDB
Using MySQLs JSON abilities with PHP
JSON differences between MariaDB and MySQL
How to deal with MySQL and MariaDB differences
Using MySQL JSON with PHP ORM (Doctrine)
More info
Conclusion

The problem with SQL

The limitation of storing data in predefined table structures is that it can be inflexible and make it difficult to store complex or hierarchical data structures. For example, if you want to store data about a person, a traditional SQL database would require you to create a separate table for each attribute of the person, such as name, address, phone number, and so on. This can quickly become cumbersome and make it difficult to manage and query the data.

This limitation has paved the way for NoSQL alternatives, which are designed to be more flexible and fluid in their structure. NoSQL databases do not rely on fixed table structures, instead using various methods to store data in more fluid formats. For example, document-oriented databases like MongoDB store data in documents that can contain nested data structures, making it easier to store hierarchical data.

NoSQL has transformed how data is stored by providing a more flexible and scalable alternative to traditional SQL databases. NoSQL databases can handle large amounts of unstructured data, making them ideal for applications that require real-time processing and analysis of large data sets. Additionally, NoSQL databases are often more horizontally scalable than traditional SQL databases, meaning they can easily scale across multiple servers.

In conclusion, SQL databases like MySQL and MariaDB use predefined table structures to organize and manage data, which can be inflexible and difficult to manage for complex or hierarchical data structures. NoSQL databases provide a more flexible and fluid alternative that can handle large amounts of unstructured data and easily scale across multiple servers, making them ideal for modern applications that require real-time processing and analysis of large data sets.

The problem with NoSQL

NoSQL databases, while offering advantages in flexibility and scalability, also have some limitations compared to traditional SQL databases. One limitation is that they may not support complex transactions, which are a critical feature of SQL databases. This can make it difficult to ensure data consistency in NoSQL databases, especially in highly concurrent environments.

Another limitation is that NoSQL databases may not offer the same level of data consistency guarantees as SQL databases. This is because NoSQL databases often prioritize performance and scalability over consistency, meaning that data may not always be immediately consistent across all nodes in a distributed database.

Additionally, NoSQL databases may not have the same level of support for ad hoc queries and reporting as SQL databases. This can make it more difficult to perform complex data analysis or generate reports from NoSQL databases.

Finally, while NoSQL databases offer more flexibility in data modeling than SQL databases, this can also be a disadvantage. Without the structure and constraints of a predefined schema, it can be challenging to ensure data quality and consistency, especially as data volumes and complexity grow.

MySQL with JSON - a hybrid solution

MySQL and MariaDB both support JSON as a data type, which allows for storing and querying data in a more flexible and hierarchical format similar to NoSQL databases. This provides several advantages over traditional SQL databases, including the ability to store and query complex or hierarchical data structures without having to define a rigid schema upfront.

Here are some SQL examples of how JSON features in MySQL and MariaDB can be leveraged:

Storing JSON data

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50),
  data JSON
);

INSERT INTO users (name, data)
VALUES ('John', '{"age": 25, "email": "john@example.com", "address": {"city": "New York", "state": "NY"}}');

Querying JSON data

-- Querying a top-level field in a JSON object
SELECT name, data->>"$.age" AS age FROM users;

-- Querying a nested field in a JSON object
SELECT name, data->>"$.address.city" AS city FROM users;

-- Querying all rows where a JSON field contains a specific value
SELECT name FROM users WHERE data->>"$.email" = "john@example.com";

Indexing JSON data

-- Creating an index on a specific JSON field
CREATE INDEX idx_users_age ON users((data->>"$.age"));

-- Querying a JSON field with an index
SELECT name FROM users WHERE data->>"$.age" > 30;

By leveraging JSON features in MySQL and MariaDB, developers can take advantage of the flexibility and fluidity of NoSQL databases while still benefitting from the reliability and stability of SQL databases. However, it's important to note that while JSON features in SQL databases provide more flexibility, they still may not offer the same level of scalability and performance as NoSQL databases for certain use cases.

Create and query more advanced, nested data structures with MySQL and JSON

Creating more advanced structures with nested data in SQL databases can be useful for applications that require storing and querying complex, hierarchical data structures. For example, e-commerce applications may need to store data about products with multiple prices, options, and descriptions, which can be difficult to manage using traditional SQL tables. By using JSON data types and nested structures, developers can store and query this data in a more flexible and fluid format, making it easier to manage and scale as the application grows.

Here's an example of creating and querying more advanced nested JSON data in MySQL/MariaDB. In the example above, we are creating a table called products with three columns: id, name, and details. The details column is defined as a JSON data type, which means that it can store data in a flexible, hierarchical format:

CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50),
  details JSON
);

In the INSERT statements, we are adding two rows to the products table, each with a name and a details field that contains a complex JSON object. The JSON object includes a description field, an array of prices with nested objects containing values and currencies, and an options field with nested properties for color and size:

INSERT INTO products (name, details)
VALUES ('Product 1', '{"description": "This is product 1", "prices": [{"value": 10, "currency": "USD"}, {"value": 8, "currency": "EUR"}], "options": {"color": "red", "size": "large"}}');

INSERT INTO products (name, details)
VALUES ('Product 2', '{"description": "This is product 2", "prices": [{"value": 15, "currency": "USD"}, {"value": 12, "currency": "EUR"}], "options": {"color": "blue", "size": "medium"}}');

Here is how the JSON looks like in a more human readable format:

{
  "description": "This is product 1",
  "prices": [
    {
      "value": 10,
      "currency": "USD"
    },
    {
      "value": 8,
      "currency": "EUR"
    }
  ],
  "options": {
    "color": "red",
    "size": "large"
  }
}

We then use SELECT statements to query the data stored in the JSON column, using the ->> operator to extract specific fields from the nested JSON structure

In the first query, we extract the description field from the top-level JSON object:

-- Querying a top-level field in a JSON object
SELECT name, details->>"$.description" AS description FROM products;

This query will return the following data:

| name      | description        |
|-----------|--------------------|
| Product 1 | This is product 1  |
| Product 2 | This is product 2  |

In the second query, we extract the color field from the nested options object:

-- Querying a nested field in a JSON object
SELECT name, details->>"$.options.color" AS color FROM products;

This query will return the following data:

| name      | color |
|-----------|-------|
| Product 1 | red   |
| Product 2 | blue  |

In the third query, we extract the value field of the first element in the prices array:

-- Querying an element in an array within a JSON object
SELECT name, details->"$['prices'][0]['value']" AS usd_price FROM products;

This query will return the following data:

| name      | usd_price |
|-----------|-----------|
| Product 1 | 10        |
| Product 2 | 15        |

How to search and filter based on JSON values in MySQL

You can create WHERE conditions on JSON data in MySQL/MariaDB by using the -> or ->> operators to access specific fields within the JSON object.

The -> operator returns a JSON object or array element at a specified index or key, while the ->> operator returns the value of a JSON object or array element as a string.

Here's an example of how to create a WHERE condition on JSON data:

SELECT *
FROM products
WHERE details->>'$.options.color' = 'red';

In this example, we are selecting all columns from the products table where the color field in the options object of the details column is equal to 'red'.

Alternatively, you could use the JSON_EXTRACT() function to extract JSON values and compare them in a WHERE condition:

SELECT *
FROM products
WHERE JSON_EXTRACT(details, '$.options.color') = 'red';

In this example, we are using the JSON_EXTRACT() function to extract the color field from the options object in the details column and compare it to 'red' in the WHERE condition.

By using WHERE conditions on JSON data, you can filter and retrieve specific rows that meet certain criteria based on the contents of the JSON object. This can be useful for applications that need to store and query complex, hierarchical data structures in a flexible and fluid format.

How to deal with unequally structured JSON data

If the JSON data is structured differently in the same row, it can make it difficult to query and extract specific values from the JSON object.

When querying JSON data in MySQL/MariaDB, you need to know the structure of the JSON object in order to extract specific values using the -> or ->> operators. If the JSON data is structured differently in the same row, it can be challenging to extract the values you need.

For example, let's say we have a products table with a details column that contains JSON data about each product. In some rows, the details JSON object may have a color field in a nested options object, while in other rows, the color field may be at the top level of the JSON object. This can make it challenging to write consistent queries that extract the color field from the JSON data.

To handle this situation, you may need to write more complex queries that use conditional statements to check the structure of the JSON object before extracting values. For example, you could use the JSON_VALID() function to check if the JSON object is valid before attempting to extract the color field:

SELECT *
FROM products
WHERE (JSON_VALID(details)
       AND details->>'$.options.color' = 'red')
   OR (JSON_VALID(details)
       AND details->>'$.color' = 'red');

In this example, we are using the JSON_VALID() function to check if the details column contains a valid JSON object. We then use conditional statements to extract the color field from either the nested options object or the top level of the JSON object, depending on the structure of the JSON data.

Overall, having differently structured JSON data in the same row can make querying and extracting specific values more challenging, but with the use of conditional statements and careful consideration of the JSON structure, it is still possible to retrieve the data you need.

Best practices for using JSON in MySQL/MariaDB

Use JSON data types only when necessary

While JSON data types offer flexibility and fluidity in data storage, they can also add complexity and slow down queries. Only use JSON data types when it is necessary to store and query complex, hierarchical data structures that would be difficult to represent using traditional SQL tables.

Use consistent JSON structures

To simplify querying and data extraction, it is best to use consistent JSON structures throughout the database. This can be achieved through data validation and normalization to ensure that all JSON objects in the database follow the same structure.

Use indexes on frequently queried JSON fields

Just like traditional SQL databases, you can create indexes on JSON fields in MySQL/MariaDB to speed up queries that frequently access these fields. However, be aware that creating too many indexes can also slow down insert and update operations.

Use JSON_EXTRACT() and JSON_SET() functions for data manipulation

To manipulate JSON data, use the built-in JSON_EXTRACT() and JSON_SET() functions in MySQL/MariaDB rather than manipulating the JSON data using string operations. This can ensure data consistency and avoid errors caused by incorrect JSON formatting.

Use JSON schema validation for data consistency

To ensure data consistency and prevent errors caused by incorrect JSON formatting, use JSON schema validation tools to validate data before inserting it into the database.

Be aware of the limitations of JSON data types

While JSON data types offer flexibility and fluidity, they also have limitations compared to traditional SQL tables. For example, they may not support complex transactions or provide the same level of data consistency guarantees as SQL databases.

By following these best practices, you can use JSON data types in MySQL/MariaDB effectively and efficiently while avoiding common pitfalls and challenges.

Using MySQLs JSON abilities with PHP

You can leverage MySQL JSON abilities with PHP by using the PDO library to connect to and query the database. Here's an example of how to use PDO to insert JSON data into a MySQL database and retrieve it using a PHP script:

Create a PDO connection to the MySQL database:

$host = 'localhost';
$dbname = 'mydatabase';
$username = 'myusername';
$password = 'mypassword';

$dsn = "mysql:host=$host;dbname=$dbname;charset=utf8mb4";

$pdo = new PDO($dsn, $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Insert JSON data into the database:

$product = array(
  'name' => 'Product 1',
  'details' => array(
    'description' => 'This is product 1',
    'prices' => array(
      array('value' => 10, 'currency' => 'USD'),
      array('value' => 8, 'currency' => 'EUR')
    ),
    'options' => array(
      'color' => 'red',
      'size' => 'large'
    )
  )
);

$sql = "INSERT INTO products (name, details) VALUES (:name, :details)";

$stmt = $pdo->prepare($sql);
$stmt->bindParam(':name', $product['name']);
$stmt->bindParam(':details', json_encode($product['details']));
$stmt->execute();

Retrieve JSON data from the database using a PHP script:

$sql = "SELECT * FROM products WHERE details->>'$.options.color' = 'red'";

$stmt = $pdo->query($sql);

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
  $details = json_decode($row['details'], true);
  echo $row['name'] . ': ' . $details['description'] . '<br>';
}

In this example, we first create a PDO connection to the MySQL database, then insert JSON data into the products table using a prepared statement with bind parameters. We encode the details array as a JSON string using json_encode() before inserting it into the database.

To retrieve the JSON data from the database, we execute a SQL query that uses the ->> operator to extract the color field from the options object in the details column. We then loop through the results and use json_decode() to decode the details JSON string into an associative array, which we can use to extract specific fields.

By leveraging MySQL JSON abilities with PHP and PDO, you can store and retrieve JSON data from a MySQL database using a flexible and fluid data structure, and manipulate the data using the powerful tools and functions of the PHP language.

JSON differences between MariaDB and MySQL

MariaDB and MySQL both support JSON data type and provide similar JSON functions for manipulating JSON data. However, there are some differences between the two databases when it comes to JSON support:

JSON validation

MariaDB has built-in JSON validation, which checks the syntax and structure of JSON data and returns an error if it is not valid. MySQL does not provide built-in JSON validation.

JSON storage

In MariaDB, JSON data can be stored in a column with any data type, such as VARCHAR or TEXT. In MySQL, JSON data can only be stored in a column with the JSON data type.

JSON indexes

MariaDB provides an index type called "JSON index," which allows for indexing of specific JSON values within a JSON column. MySQL does not provide JSON indexing.

SQL compatibility

MariaDB aims to be more compatible with SQL standards than MySQL, and this also applies to JSON support. For example, MariaDB supports the standard SQL/JSON path language for querying JSON data, while MySQL uses its own JSON path language.

How to deal with MySQL and MariaDB differences

When dealing with a mixed database environment where some instances are MySQL and some are MariaDB, there are a few strategies you can use to work around the differences in JSON support:

Use common JSON features

Both MySQL and MariaDB support common JSON features such as the JSON data type and common JSON functions. You can design your application to use these common features and avoid using features that are specific to one database.

Use conditional SQL

If your application needs to use JSON features that are specific to one database, you can use conditional SQL to handle the differences. For example, you can use a conditional SQL statement to check if a JSON validation function is available in the current database, and use an alternative approach if it is not.

Use a compatibility layer

Some third-party tools or libraries can provide a compatibility layer that allows you to use a common JSON API across different databases. For example, the JPA (Java Persistence API) specification provides a common API for working with JSON data, which can be used across different databases including MySQL and MariaDB. For PHP, an ORM framework like e.g. Doctrine can be used.

Migrate to a common database

If the differences in JSON support are causing significant issues or complexity, you may consider migrating to a common database that supports the required JSON features. This could involve migrating from MySQL to MariaDB, or vice versa, or migrating to a different database that supports a common JSON API.

Using MySQL JSON with PHP ORM (Doctrine)

There is an Object Relational Mapping (ORM) framework for PHP called Doctrine that provides a similar API to the Java Persistence API (JPA). Doctrine provides a set of PHP classes and libraries that can be used to map PHP objects to database tables, and to query and manipulate data using an object-oriented interface.

Doctrine supports several database systems, including MySQL and MariaDB, and provides support for working with JSON data using the JSON data type and related functions.

Here's an example of how to use Doctrine to work with JSON data in MySQL and MariaDB:

Installing Doctrine

You can install Doctrine using Composer, which is a dependency manager for PHP. Here's an example of how to install Doctrine using Composer:

composer require doctrine/orm

Configuring Doctrine

You'll need to configure Doctrine to connect to your MySQL or MariaDB database. Here's an example of how to configure Doctrine using a YAML configuration file:

# config.yml

doctrine:
  dbal:
    driver: pdo_mysql
    host: localhost
    port: 3306
    dbname: mydatabase
    user: myusername
    password: mypassword
    charset: UTF8
  orm:
    auto_generate_proxy_classes: true
    default_repository_class: Doctrine\ORM\EntityRepository

Creating a JSON entity

You can create a Doctrine entity class to represent a JSON object stored in a MySQL or MariaDB database. Here's an example of how to create a JSON entity class:

// src/Entity/JsonData.php

use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity
 * @ORM\Table(name="mytable")
 */
class JsonData
{
    /**
     * @ORM\Id
     * @ORM\GeneratedValue
     * @ORM\Column(type="integer")
     */
    private $id;

    /**
     * @ORM\Column(type="json")
     */
    private $data;

    public function getId(): int
    {
        return $this->id;
    }

    public function getData(): array
    {
        return $this->data;
    }

    public function setData(array $data): void
    {
        $this->data = $data;
    }
}

Inserting and querying JSON data

You can use the Doctrine EntityManager to insert and query JSON data. Here's an example of how to insert and query JSON data using Doctrine:

use Doctrine\ORM\EntityManagerInterface;

// Inserting JSON data
$jsonData = new JsonData();
$jsonData->setData(['name' => 'John', 'age' => 30, 'email' => 'john@example.com']);

$entityManager->persist($jsonData);
$entityManager->flush();

// Querying JSON data
$jsonDataRepository = $entityManager->getRepository(JsonData::class);

$queryBuilder = $jsonDataRepository->createQueryBuilder('jd')
    ->where("JSON_EXTRACT(jd.data, '$.name') = :name")
    ->setParameter('name', 'John');

$result = $queryBuilder->getQuery()->getResult();

In the above example, we're using Doctrine to insert and query JSON data in a MySQL or MariaDB database. We're using a Doctrine entity class to represent the JSON data, and we're using the JSON_EXTRACT() function to query specific values within the JSON data.

In summary, Doctrine is an ORM framework for PHP that provides a similar API to the Java Persistence API (JPA). Doctrine supports working with JSON data in MySQL and MariaDB using the JSON data type and related functions, and can be used to map PHP objects to database tables and to query and manipulate data using an object-oriented interface.

More info

For reference and more examples, check out the official MySQL documentation for JSON functions.

Conclusion

Using MySQL's JSON abilities and selecting a NoSQL database both have their advantages and disadvantages, depending on the specific use case and requirements of your application.

On one hand, MySQL's JSON abilities allow developers to store and query complex, hierarchical data structures in a flexible and fluid format without having to define a rigid schema upfront. This can be especially useful for applications that require data that is highly relational and requires transactions and consistency guarantees. Additionally, MySQL has a long history of stability, reliability, and community support, making it a trusted choice for many developers and organizations.

On the other hand, NoSQL databases are designed specifically to handle unstructured and semi-structured data, and they often offer better scalability, performance, and ease of use for certain use cases. NoSQL databases also tend to be more flexible and adaptable to changing data structures over time, as they don't require rigid schemas like traditional SQL databases.

Ultimately, the choice between using MySQL's JSON abilities and selecting a NoSQL database will depend on the specific needs and requirements of your application, including factors such as the complexity and structure of your data, the need for transactions and consistency guarantees, and the scale and performance requirements of your application. Both MySQL's JSON abilities and NoSQL databases can be effective solutions, and the best choice will depend on the specific use case and requirements of your application.

Updated April 23, 2023

Use Nested Data in MySQL with JSON + Examples

In this article

The problem with SQL

The problem with NoSQL

MySQL with JSON - a hybrid solution

Storing JSON data

Querying JSON data

Indexing JSON data

Create and query more advanced, nested data structures with MySQL and JSON

How to search and filter based on JSON values in MySQL

How to deal with unequally structured JSON data

Best practices for using JSON in MySQL/MariaDB

Use JSON data types only when necessary

Use consistent JSON structures

Use indexes on frequently queried JSON fields

Use JSON_EXTRACT() and JSON_SET() functions for data manipulation

Use JSON schema validation for data consistency

Be aware of the limitations of JSON data types

Using MySQLs JSON abilities with PHP

Create a PDO connection to the MySQL database:

Insert JSON data into the database:

Retrieve JSON data from the database using a PHP script:

JSON differences between MariaDB and MySQL

JSON validation

JSON storage

JSON indexes

SQL compatibility

How to deal with MySQL and MariaDB differences

Use common JSON features

Use conditional SQL

Use a compatibility layer

Migrate to a common database

Using MySQL JSON with PHP ORM (Doctrine)

Installing Doctrine

Configuring Doctrine

Creating a JSON entity

Inserting and querying JSON data

More info

Conclusion