MariaDB 13.1 Feature in Focus: JSON Operators and JSON_TABLE Improvements

JSON support in MariaDB has improved significantly over the years.

We have functions to create JSON documents, extract values, modify objects, inspect arrays, compare documents, and even transform JSON into relational rows using JSON_TABLE().

But sometimes, a very small piece of syntax can make a surprisingly large difference.

With MariaDB 13.1, we can finally write:

document->'$.customer.name'

and:

document->>'$.customer.name'

Yes, the JSON arrow operators have arrived in MariaDB!!

And MariaDB 13.1 also improves JSON_TABLE() with support for formatted JSON columns, allowing complete JSON objects and arrays to be returned without converting them into scalar SQL values.

Let’s have a look.

The Long Way Around

Imagine a table containing orders and some additional information stored as JSON:

CREATE TABLE orders (
  id INT PRIMARY KEY,
  details JSON
);

We insert a small document:

INSERT INTO orders VALUES (
  1,
  '{
     "customer": {
       "name": "Alice",
       "country": "Belgium"
     },
     "items": [
       {"product": "Keyboard", "quantity": 1},
       {"product": "Mouse", "quantity": 2}
     ],
     "paid": true
   }'
);

Until MariaDB 13.1, extracting the customer name required using JSON_EXTRACT():

SELECT JSON_EXTRACT(details, '$.customer.name')
FROM orders;

The result is still a JSON string:

"Alice"

When we want the actual unquoted value, we need another function:

SELECT JSON_UNQUOTE(
         JSON_EXTRACT(details, '$.customer.name')
       )
FROM orders;

That works.

It has always worked. But it is not exactly pleasant to type.

And when a query contains several JSON expressions, it quickly becomes difficult to read:

SELECT
  JSON_UNQUOTE(JSON_EXTRACT(details, '$.customer.name')) AS customer,
  JSON_UNQUOTE(JSON_EXTRACT(details, '$.customer.country')) AS country,
  JSON_EXTRACT(details, '$.paid') AS paid
FROM orders;

There is nothing technically wrong with this query.

It is just carrying a little too much luggage.

Follow the Arrows

MariaDB 13.1 adds the -> and ->> JSON operators.

The single-arrow operator:

column->path

is the shorter equivalent of:

JSON_EXTRACT(column, path)

The double-arrow operator:

column->>path

is the shorter equivalent of:

JSON_UNQUOTE(JSON_EXTRACT(column, path))

Our previous query can now be written as:

SELECT
  details->>'$.customer.name' AS customer,
  details->>'$.customer.country' AS country,
  details->'$.paid' AS paid
FROM orders;

Much better.

The result is:

+----------+---------+------+
| customer | country | paid |
+----------+---------+------+
| Alice    | Belgium | true |
+----------+---------+------+

No new JSON functionality is hidden behind these operators.

They provide a shorter, more familiar syntax for functionality that MariaDB already had.

But that does not make them unimportant.

Readable SQL matters.

One Arrow or Two?

The difference between the two operators is simple.

Use -> when you want the value represented as JSON:

SELECT details->'$.customer.name'
FROM orders;

Result:

"Alice"

Use ->> when you want the unquoted SQL string:

SELECT details->>'$.customer.name'
FROM orders;

Result:

Alice

For objects and arrays, the single-arrow operator is especially useful:

SELECT details->'$.customer' AS customer
FROM orders;

Result:

{"name": "Alice", "country": "Belgium"}

And we can retrieve the complete items array:

SELECT details->'$.items' AS items
FROM orders;

Result:

[
  {"product": "Keyboard", "quantity": 1},
  {"product": "Mouse", "quantity": 2}
]

The double-arrow operator is more convenient when the value will be displayed, compared, sorted, or used by another SQL expression:

SELECT id
FROM orders
WHERE details->>'$.customer.country' = 'Belgium';

That is significantly easier to read than:

SELECT id
FROM orders
WHERE JSON_UNQUOTE(
        JSON_EXTRACT(details, '$.customer.country')
      ) = 'Belgium';

Both are valid.

But I know which one I prefer.

Choosing the Correct Arrow

A simple rule is:

• use -> when the result must remain JSON;
• use ->> when the result should become an ordinary SQL string.

What do you need?	Operator	Result example	Typical use
A JSON string, including its quotes	->	"Alice"	Pass the value to another JSON function
An ordinary SQL string	->>	Alice	Display, compare, sort, or concatenate
A complete JSON object	->	{"name":"Alice", "country":"Belgium"}	Preserve or manipulate the object
A complete JSON array	->	[{"product":"Keyboard"}, "product":"Mouse"}]	Preserve or process the array
A scalar used in a WHERE condition	->>	Belgium	Compare it with an SQL value

Or, as a small decision tree:

Consider a more deeply nested document:

SET @document = '{
  "order": {
    "customer": {
      "identity": {
        "name": "Alice",
        "email": "alice@example.com"
      },
      "shipping": {
        "address": {
          "city": "Brussels",
          "country": "Belgium"
        }
      }
    },
    "items": [
      {
        "product": {
          "name": "Keyboard",
          "category": "Accessories"
        },
        "quantity": 1
      },
      {
        "product": {
          "name": "Mouse",
          "category": "Accessories"
        },
        "quantity": 2
      }
    ]
  }
}';

MariaDB and MySQL use a complete JSON path, even when the requested value is nested several levels deep:

SELECT
  @document->>'$.order.customer.identity.name' AS customer,
  @document->>'$.order.customer.shipping.address.city' AS city;
+----------+----------+
| customer | city     |
+----------+----------+
| Alice    | Brussels |
+----------+----------+

The single-arrow operator can preserve an entire nested object:

SELECT
  @document->'$.order.customer.shipping.address' AS address;
+--------------------------------------------+
| address                                    |
+--------------------------------------------+
| {"city": "Brussels", "country": "Belgium"} |
+--------------------------------------------+

It can also preserve an object inside an array:

SELECT
  @document->'$.order.items[0].product' AS first_product;
+-------------------------------------------------+
| first_product                                   |
+-------------------------------------------------+
| {"name": "Keyboard", "category": "Accessories"} |
+-------------------------------------------------+

But when we need only the product name as an SQL string, we use two arrows:

SELECT
  @document->>'$.order.items[0].product.name' AS first_product;
+---------------+
| first_product |
+---------------+
| Keyboard      |
+---------------+

The number of arrows does not represent the nesting depth. We do not add another arrow for each level of the document. The complete navigation remains inside the JSON path expression.

A Long-Awaited Compatibility Improvement

These operators will look familiar to developers coming from MySQL.

MySQL has supported the JSON arrow syntax for many years, while MariaDB users had to use the equivalent JSON functions.

This difference may look small, but small syntax differences matter when migrating applications.

An ORM, framework, generated query, or application may contain expressions such as:

payload->>'$.status'

Previously, those queries needed to be rewritten when moving from MySQL to MariaDB.

With MariaDB 13.1, one more compatibility obstacle disappears.

But MySQL is not the only database using arrows to navigate JSON documents.

PostgreSQL also supports the -> and ->> operators. The meaning is very similar:

• -> returns a JSON value;
• ->> returns the extracted value as text.

For example, in PostgreSQL we can write:

SELECT details->'customer'->>'name'
FROM orders;

The equivalent MariaDB 13.1 query is:

SELECT details->>'$.customer.name'
FROM orders;

So the arrows point in the same general direction, but they do not follow exactly the same road.

MariaDB and MySQL use a complete JSON path expression on the right-hand side:

details->>'$.customer.name'

PostgreSQL’s traditional arrow operators navigate one object key or array position at a time:

details->'customer'->>'name'

This means the new MariaDB syntax also improves familiarity for PostgreSQL developers, even though nested expressions are not always directly portable between the databases.

The JSON_TABLE() improvement also brings MariaDB closer to the SQL/JSON syntax supported by PostgreSQL. PostgreSQL supports JSON_TABLE() columns declared with FORMAT JSON, allowing nested objects and arrays to remain JSON instead of being converted into scalar values.

For example, both databases can now use a column declaration following this pattern:

preferences TEXT FORMAT JSON PATH '$.preferences'

There may still be differences in supported options, data types, error handling, and JSON storage, but the SQL itself is becoming increasingly familiar across MariaDB, MySQL, and PostgreSQL.

This is good news for developers working with multiple databases.

It does not make every JSON query magically portable.

But at least the arrows no longer immediately point to a database migration guide.

This feature was tracked as MDEV-13594.

And it really was long-awaited: the request was originally created in 2017.

Sometimes arrows need almost nine years to find the correct direction. 🎯

The feature was contributed by Mohd Jarir Khan.

Thank you!

JSON_TABLE: Turning JSON into Rows

The arrow operators make it easier to extract individual values.

But sometimes we do not want one value.

We want to transform an entire JSON array into relational rows.

That is where JSON_TABLE() comes in.

JSON_TABLE() has been available in MariaDB since 10.6. It allows a JSON document to behave like a table inside a query.

Using the items array from our order, we can write:

SELECT
  o.id,
  item.product,
  item.quantity
FROM orders AS o
JOIN JSON_TABLE(
  o.details,
  '$.items[*]' COLUMNS (
    product VARCHAR(100) PATH '$.product',
    quantity INT PATH '$.quantity'
  )
) AS item;

The result becomes:

+----+----------+----------+
| id | product  | quantity |
+----+----------+----------+
|  1 | Keyboard |        1 |
|  1 | Mouse    |        2 |
+----+----------+----------+

The JSON array has been converted into normal rows and columns.

We can filter it:

SELECT
  o.id,
  item.product,
  item.quantity
FROM orders AS o
JOIN JSON_TABLE(
  o.details,
  '$.items[*]' COLUMNS (
    product VARCHAR(100) PATH '$.product',
    quantity INT PATH '$.quantity'
  )
) AS item
WHERE item.quantity > 1;

We can join it with other tables. We can aggregate it. We can use it almost like any other relational result. JSON becomes SQL again.

Everyone is happy.

The Scalar Limitation

A regular JSON_TABLE() column extracts a value and converts it to the declared SQL type:

JSON_TABLE(
  document,
  '$[*]' COLUMNS (
    name VARCHAR(100) PATH '$.name'
  )
)

This works perfectly for scalar values such as strings, numbers, dates, and booleans.

But what happens when the selected value is itself an object or an array?

Consider this document:

[
  {
    "id": 1,
    "name": "Alice",
    "preferences": {
      "language": "en",
      "theme": "dark"
    },
    "roles": ["admin", "developer"]
  }
]

Extracting name is easy because it is a scalar value.

But preferences is an object, and roles is an array.

Those values should remain JSON.

Trying to treat them like ordinary scalar columns is not what we want.

FORMAT JSON to the Rescue

MariaDB 13.1 adds formatted column support to JSON_TABLE().

A column can now be declared using FORMAT JSON:

SELECT *
FROM JSON_TABLE(
  '[
     {
       "id": 1,
       "name": "Alice",
       "preferences": {
         "language": "en",
         "theme": "dark"
       },
       "roles": ["admin", "developer"]
     }
   ]',
  '$[*]' COLUMNS (
    id INT PATH '$.id',
    name VARCHAR(100) PATH '$.name',
    preferences LONGTEXT FORMAT JSON PATH '$.preferences',
    roles LONGTEXT FORMAT JSON PATH '$.roles'
  )
) AS users;

The result contains the scalar columns as usual, while the nested object and array remain valid JSON documents:

+----+-------+----------------------------------+------------------------+
| id | name  | preferences                      | roles                  |
+----+-------+----------------------------------+------------------------+
|  1 | Alice | {"language":"en","theme":"dark"} | ["admin","developer"]  |
+----+-------+----------------------------------+------------------------+

The important part is:

preferences LONGTEXT FORMAT JSON PATH '$.preferences'

and:

roles LONGTEXT FORMAT JSON PATH '$.roles'

FORMAT JSON tells JSON_TABLE() that the extracted value should be returned using its JSON representation instead of being treated as an ordinary scalar value.

This makes it possible to flatten only the part of a document that we need while preserving nested structures for later processing.

Flatten Some, Keep Some

That is probably the most useful way to think about this improvement.

Sometimes we want to completely normalize a JSON document:

JSON document
      |
      v
rows and columns

But sometimes we only want to flatten the outer structure:

JSON document
      |
      +--> relational columns
      |
      +--> preserved JSON object
      |
      +--> preserved JSON array

For example, an application may need the user identifier and name as relational values, while still returning the complete preferences object to the application:

SELECT
  jt.id,
  jt.name,
  jt.preferences
FROM JSON_TABLE(
  @users,
  '$[*]' COLUMNS (
    id INT PATH '$.id',
    name VARCHAR(100) PATH '$.name',
    preferences LONGTEXT FORMAT JSON PATH '$.preferences'
  )
) AS jt;

We do not always need to explode every nested array and object.

Sometimes keeping part of the document intact is exactly what we need.

The Two Features Work Nicely Together

The new features address different JSON use cases.

The arrow operators are useful when retrieving a small number of values directly from a JSON column:

SELECT
  details->>'$.customer.name',
  details->>'$.customer.country'
FROM orders;

JSON_TABLE() is useful when a document contains arrays or repeated structures that need to become rows:

SELECT item.*
FROM orders AS o
JOIN JSON_TABLE(
  o.details,
  '$.items[*]' COLUMNS (
    product VARCHAR(100) PATH '$.product',
    quantity INT PATH '$.quantity'
  )
) AS item;

And formatted JSON_TABLE() columns are useful when part of the nested JSON structure must remain intact:

JSON_TABLE(
  document,
  '$[*]' COLUMNS (
    id INT PATH '$.id',
    metadata LONGTEXT FORMAT JSON PATH '$.metadata'
  )
)

Together, these improvements make working with JSON more natural.

Less ceremony. Less nesting of functions. More readable SQL.

Community Contributions

Both improvements included in this article came from community contributions.

Support for the JSON arrow operators was implemented for MDEV-13594 and contributed by Mohd Jarir Khan.

Formatted column support for JSON_TABLE() was implemented for MDEV-25727 and contributed by Varun Deep Saini.

These are good examples of contributions that may appear small when described in a release-note bullet point:

Support JSON operators.
Add formatted columns to JSON_TABLE.

But for developers writing SQL every day, they make a real difference.

They improve compatibility. They make queries easier to read. And they remove unnecessary workarounds from applications.

Thank you to both contributors!