Inline JSON Path Expressions in MySQL 5.7

MySQL 5.7.9 has a new feature, that simplifies queries that deal with JSON data and makes more human-readable: inlined JSON path expressions. Now you can do following:

Let’s take a closer look at the expression in the WHERE clause. It contains an equality  condition for the inlined JSON path expression and a constant. The arrow ‘->’ on the right side is the  separator. It splits the field on the left side and the path expression on the right of itself. The field could be specified in any way MySQL allows:

  • field
  • table.field
  • db.table.field

The path expression is a string containing valid JSON path expression described in WL#7909.  We have been inspired by Facebook’s DocStore project which has an intuitive way to access JSON in SQL. They used a dot notation though e.g. doc.address.zipcode. We have chosen -> as separator will avoid ambiguities.

The whole expression data->'$.id' is a syntactic sugar for json_extract(data, '$.id'). Due to that, it’s treated like json_extract() was specified instead. Here are few examples how it looks like. You can use it in a SELECT:

Define a Generated Column using it:

This way you could have your documents indexed automatically by an id stored inside the document. If you need a string data to be indexed, make sure you’re using the unquoting function — json_unquote() — in order to get index used properly:

Define a view:

The inlined path expressions are allowed everywhere, where regular call
to json_extract() function is allowed. For example (but not limited to):
SELECT lists, WHERE/HAVING clauses, ORDER/GROUP BY clauses, etc.

The same logic applies to where inlined JSON path expressions can’t be used — where json_extract() can’t be used. For example:

Though they’re nice and readable, inlined JSON path expressions aren’t a
complete replacement for the json_extract() function. There are number of

 Inlined JSON path expressionjson_extract() function
Data sourceA table's field onlyAny JSON-typed value
path expressionA plain string onlyAny string-typed value
Number of path expressionsOnly oneMultiple

If you want to dig further into the actual specification of the inlined JSON path expression, it is described in WL#8607.

Please let us know what you think of this new JSON features! We’d love to hear your feedback on what else you’d like to see related to our wider JSON support. If you encounter any problems with these new features, please let us know here in the comments, open a bug report at, or open a support ticket.

Thank you for using MySQL!

2 thoughts on “Inline JSON Path Expressions in MySQL 5.7

Leave a Reply