# JSON Data Type

The `JSON` alias was added to make it possible to use JSON columns in [statement based](https://mariadb.com/docs/server/server-management/server-monitoring-logs/binary-log/binary-log-formats#statement-based) [replication](https://mariadb.com/docs/server/ha-and-performance/standard-replication) from MySQL to MariaDB and to make it possible for MariaDB to read [mysqldumps](https://mariadb.com/docs/server/clients-and-utilities/legacy-clients-and-utilities/mysqldump) from MySQL.

`JSON` is an alias for `LONGTEXT COLLATE utf8mb4_bin` introduced for compatibility reasons with MySQL's JSON data type. MariaDB implements this as a [LONGTEXT](https://mariadb.com/docs/server/reference/data-types/string-data-types/longtext) rather, as the JSON data type contradicts the SQL:2016 standard, and MariaDB's benchmarks indicate that performance is at least equivalent.

In order to ensure that a valid json document is inserted, the [JSON\_VALID](https://mariadb.com/docs/server/reference/sql-functions/special-functions/json-functions/json_valid) function can be used as a [CHECK constraint](https://mariadb.com/docs/server/sql-statements/data-definition/constraint#check-constraint-expressions). This constraint is automatically included for types using the `JSON` alias.

The assigned text value is retained verbatim. If a value fails `JSON_VALID()`, an error is raised. This `CHECK` constraint can also be manually added to any `LONGTEXT` field. When a JSON object contains duplicate keys, only the first key-value pair is accessible via functions like `JSON_EXTRACT()`.

## Examples

```sql
CREATE TABLE t (j JSON);

DESC t;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| j     | longtext | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
```

With validation:

```sql
CREATE TABLE t2 (
  j JSON 
  CHECK (JSON_VALID(j))
);

INSERT INTO t2 VALUES ('invalid');
ERROR 4025 (23000): CONSTRAINT `j` failed for `test`.`t2`

INSERT INTO t2 VALUES ('{"id": 1, "name": "Monty"}');
Query OK, 1 row affected (0.13 sec)
```

JSON example:

```sql
CREATE TABLE json_example (
  description VARCHAR(20),
  example JSON
);
```

```sql
SHOW CREATE TABLE json_example\G
```

```sql
*************************** 1. row ***************************
       Table: json_example
Create Table: CREATE TABLE `json_example` (
  `description` varchar(20) DEFAULT NULL,
  `example` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`example`))
) ENGINE=InnoDB DEFAULT CHARSET=latin1
```

```sql
INSERT INTO json_example VALUES
  ('Array', '[ 1, 2, 3 ]'),
  ('Dictionary', '{ "a": 1, "b": 2 }'),
  ('Duplicates', '{ "a":1,"b":2, "a":3,"b": 4,"b":5}');
```

```sql
SELECT * FROM json_example;
```

```sql
+-------------+------------------------------------+
| description | example                            |
+-------------+------------------------------------+
| Array       | [ 1, 2, 3 ]                        |
| Dictionary  | { "a": 1, "b": 2 }                 |
| Duplicates  | { "a":1,"b":2, "a":3,"b": 4,"b":5} |
+-------------+------------------------------------+
```

```sql
SELECT description, JSON_EXTRACT(example, '$.b')
FROM json_example;
```

```sql
+-------------+------------------------------+
| description | JSON_EXTRACT(example, '$.b') |
+-------------+------------------------------+
| Array       | NULL                         |
| Dictionary  | 2                            |
| Duplicates  | 2                            |
+-------------+------------------------------+
```

```sql
INSERT INTO json_example VALUES
  ('Invalid', '{');
```

```sql
ERROR 4025 (23000): CONSTRAINT `json_example.example` failed for `test`.`json_example`
```

## Replicating JSON Data Between MySQL and MariaDB

The `JSON` type in MySQL stores the JSON object in a compact form, not as [LONGTEXT](https://mariadb.com/docs/server/reference/data-types/string-data-types/longtext) as in MariaDB.\
This means that row-based replication will not work for `JSON` types from MySQL to MariaDB.

There are a few different ways to solve this:

* Use statement-based replication.
* Change the `JSON` column to type `TEXT` in MySQL.
* If you must use row-based replication and cannot change the MySQL master from `JSON` to `TEXT`, you can try to introduce an intermediate MySQL replica, and change the column type from `JSON` to `TEXT` on it. Then you replicate from this intermediate slave to MariaDB.

## Converting a MySQL TABLE with JSON Fields to MariaDB

MariaDB can't directly access MySQL's `JSON` format.

There are a few different ways to move the table to MariaDB:

* From [MariaDB 10.5.7](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/10.5/10.5.7), you can use the [mysql\_json](https://mariadb.com/docs/server/reference/plugins/other-plugins/mysql_json) plugin. See [Making MariaDB understand MySQL JSON](https://mariadb.org/making-mariadb-understand-mysql-json/) (blog post).
* Change the JSON column to type `TEXT` in MySQL. After this, MariaDB can directly use the table without any need for a dump and restore.
* [Use mysqldump to copy the table](https://mariadb.com/docs/server/clients-and-utilities/legacy-clients-and-utilities/mysqldump#examples).

## Differences Between MySQL JSON Strings and MariaDB JSON Strings

* In MySQL, JSON is an object and is [compared according to json values](https://dev.mysql.com/doc/refman/8.0/en/json.html#json-comparison). In MariaDB JSON strings are normal strings and compared as strings. One exception is when using [JSON\_EXTRACT()](https://mariadb.com/docs/server/reference/sql-functions/special-functions/json-functions/json_extract) in which case strings are unescaped before comparison.

## See Also

* [JSON Functions](https://mariadb.com/docs/server/reference/sql-functions/special-functions/json-functions)
* [CONNECT JSON Table Type](https://mariadb.com/docs/server/server-usage/storage-engines/connect/connect-table-types/connect-json-table-type)
* [MDEV-9144](https://jira.mariadb.org/browse/MDEV-9144)

<sub>*This page is licensed: CC BY-SA / Gnu FDL*</sub>

{% @marketo/form formId="4316" %}


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://mariadb.com/docs/server/reference/data-types/string-data-types/json.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.