JSON Support: news, non-news, and the bigger picture
CC Attribution 2.0 Belgium:
You are free to use, adapt and copy, distribute and transmit the work or content in adapted or unchanged form for any legal purpose as long as the work is attributed to the author in the manner specified by the author or licensor.
Petrunia, Sergei
This talk aims to cover everything about the current state of JSON support. First, I'll cover the newest addition, JSON_TABLE in MariaDB 10.6. Then, I'll discuss the power of JSONPath in MariaDB and how it compares to the SQL Standard and other databases. This is technically non-news but it hasn't been discussed before. Finally, we'll take a look at other present and missing features and see what are the biggest and lowest-hanging fruits in JSON support in MariaDB.
 Hello, this is a talk titled JSON support in MariaDB, use, non-use and the bigger picture. My name is Sergey Petronyev, I am a MariaDB developer, so for me, use means new code, non-use means old code, so we are going to talk about the new code in JSON support in MariaDB as well as some of the code that is already there. The first topic is the JSON path. This is not use, there are multiple JSON functions in MariaDB already and a lot of them accept JSON path expressions as an argument. These are used to locate elements in JSON document. Path language in MariaDB wasn't documented until recently, perhaps some people thought that it is just follow.bar.buzz or what can be its obvious, but the reality is not that easy. If one searches for the definition of JSON path on the web, you can find many different definitions, but the one that should be used with SQL language was defined in SQL 2016 in the section SQL JSON path language. So let's look at that in detail. A JSON path starts with the mode. The mode can be either lux, which is the default that is used if it is not specified or strict. Then goes the dollar sign. Dollar sign denotes the context element. By default, it is the root of the JSON document, but some functions may use other context elements. Then it is followed by multiple steps, which select the elements in the JSON document. The first kind of step is object member selection step. You can select a member by its name. For example, here there is a JSON object and you can select name.name and you get the value MariaDB. You can select all members by specifying.star and this produces a sequence of values of all members of the array, in this case MariaDB followed by 10.5. Strict mode requires that the context element is an object and if the member name is specified, it must be present in the array, otherwise it is in the error. Lux mode ignores missing elements, unwraps one element array and tries to avoid errors. The second kind of step is array element selection step. If the context is an array, then you can select one of several elements from it. You can just use number, the numbers are zero based, so if you take element number zero and you get the first element in the array, you can specify a range, you can use the word last to specify do you want the last element, you can use a comma-separated list of any of the above, or you can specify all elements by using star. In this case, you will get the sequence of all elements in the array. Strict mode requires that indexes are within the bounds, otherwise it is in the error. With Lux mode, there is no such restriction. After each step, optionally, there can be a filter. I mentioned that we get a sequence of elements and filter allows to filter out elements of the sequence. The syntax is question mark and then predicate. It can be a formula, it can be a logical and or formula which has comparisons of constants and elements which uses arithmetic. There are certain functions supported and you can refer to parameters, pass it from outside. Here is a basic example. We have a JSON document with two objects, we select them both by specifying we want all members in the array and then we want only those that have member color equals to black. Then we add another step which is an item, which is a select member named item, so we get just one value laptop here. I won't go into further detail with filters because neither MariaDB nor MySQL supports them. Let's switch to discussing what does MariaDB and MySQL support. They support only Lux mode. I have actually discovered that even there they are not fully compliant. Then I discussed this question with MariaDB developer who developed this feature. He said he was just following MySQL. MySQL market is bugger verified so at least perhaps they intended to be compliant. We don't know for certain, they don't state in their documentation. Object member selection is supported. It's the basics. For array selection single index is supported by both. MySQL also supports last and M to M. As I mentioned, filters are not supported. The people who coded this to cut down a lot because they didn't need to support expressions or arithmetic of functions. On the other hand, it uses the expressive power of JSON paths quite a bit. There is however an extension supported, recursive search. For example, we have a task to find certain member anywhere in the JSON document. With SQL, JSON path is not possible. You need to specify the full path to where you want. So both MariaDB and MySQL support an extension, while the current search steps. If you use double star, it will select all direct and indirect children of the current element. Then you can add another step to select member of the array. This way you can find, for example, price, element number price, limit price anywhere in the JSON document. PostgreSQL also supports this extension, but their syntax is dot star star. So the syntax is different. But the semantics is the same. How is that useful? Well, one and the use that I'm using almost daily is optimizer trace. Optimizer trace is a JSON document. It's basically a look of query optimizer actions. This is a huge JSON document. It has recursive structure because SQL allows recursive constructs like subqueries. So we get the same kind of actions done at different levels. Some of the actions done by the optimizer are done for each table. One of them is to estimate the number of rows in the table, rows estimations. So you can write a query like this, select JSON detected. It's just that formatting JSON extract. And then dollar star star rows estimation. It finds you all rows estimation that we've done for the tables used by the query. In the top level select and the subqueries everywhere. If filters were supported, we would be able to find row estimation for a specific table. Unfortunately, they're not so we need to get them all that just look ourselves. So for deeply nested JSON documents, wildcard search step is very useful. Summary. So the language for pointing nodes in JSON document is called SQL JSON path language. It was introduced in SQL 2016. Both MariaDB and MySQL implement is upset. It's now documented. Lacks mod only. Filters are not supported, which is unfortunate. Array indexing only supports single integer index. And both MySQL and MariaDB have the same recursive search expression. That is good. As if you look at other databases, PostgreSQL seem to have the most compliant and feature-rich implementation. They support filtering strict locks mode and so forth. I'm not sure if they are fully compliant, but they seem to be. Other databases support different and often very restrictive subsets. So here my score and MariaDB are quite competitive. The next topic I wanted to discuss is news. It is JSON table feature. News because it is under development in MariaDB. JSON table is a table function. It takes JSON input and converts it into a table. So unlike other functions, use it in the front close. It was introduced in SQL 2016. It's supported in Oracle database in MySQL. It is under development in MariaDB. We are trying to get it into MariaDB 10.6. Let's start with an example. We get a JSON document which is an array of two objects which have the same elements. And suppose we want to get that data in table or form. So we can call the JSON table function. We select star from JSON table, then we specify which cobs we want and we get it in the table or form. Let's look at the syntax for detail. Select star from JSON table. As I've mentioned, JSON table is a table function. So you use it where you would use a table. The first argument is JSON doc is a source document. In this case, it's in a session variable, but it can be in other places as well. The second argument is a path to node, JSON path to nodes to examine. This produces a sequence and then each element of the sequence produces one, its own row. Then go column definitions. This is a bridge from schemaless and typeless JSON into the highly-typed world of SQL. This is a schema on read where you specify what schema we expect to get when we are getting the data from JSON. And here we specify paths where to get the values from. The context for these paths is the node being examined. If that was not complex enough, it gets more complex. Nested paths are supported. Suppose we get some JSON document describing some items and they in turn have some code arrays in them. How do we normalize this structure? JSON table supports nested path syntax. So we first specify that we will break the JSON document into a sequence of objects. And then inside object we specify that each color will also produce its own row. And the output will be normalized. Normalized form where the first object which describes a laptop will produce three rows for one for each color. And for the second for the t-shirt we will get two rows for one for each color as well. This allows to switch from nested structures to normalized form in a relational form. Multiple nested paths are supported. You can nest nested paths and one can have sibling nested paths. The standard allows one to specify how they want to unnest. It's called plan close. The default is outer join like. So if we get an object which says name to shirt, which can be two colors and three sizes, we get all of the colors with size null and we get all of the sizes with color being null. The plan close allows to specify from cross join or some other variants how to normalize it into relational form. My score and sum MariaDB will only support outer join like unnesting, which is the default, which basically means we don't support the plan close. Another feature of JSON table is error handling. JSON doesn't have types, so one need to account for possible errors when converting to a relational form. And JSON table syntax allows to specify that after specifying path, you can specify action to do on empty, which is used when the JSON element requested is missing or on the error when there was the type conversion. You can produce a score now, you can use some default value or you can just see me in the error. Both my score and sum MariaDB support this. An important part that JSON table can be used with joins. Let me explain what I mean by it. Suppose you've got the table orders, which there are two rows describing two orders. Under each order, you get a list of JSON document, which describes the items in that order. How do I get that in the relational form? You can write a query like so. You can take table orders, then join it with JSON table, and its argument will refer to table orders, which is not typically possible, but JSON table allows it. And this way, you will get normalized output. This JSON table can refer to other tables, and this produces lateral-like semantics. Contents of the table produced by JSON table will depend on the parameter values. This is great to do normalization. If you have some data in... If you have a table which has some data in JSON, you can normalize the data to SQL by doing join with JSON table function. Summary for JSON table. So it is a table function to convert JSON data to relational form. It has been introduced in SQL 2016. The standard specifiers of all the features may escalate, it will be upset, but in particular plan clause is not supported. In MariaDB, it is under development. This is the generation number, and we are going to implement a subset that is very close to what MySQL did. As for other databases, let me quote slide from Markus Wienand, which shows that basically only Oracle MySQL supported. Now we are going to add MySQL. Well, I know that PostgreSQL have it under development. They had a patch posted on their mailing lists. I'm not sure what is how much work they have left to do. Total takeaways is MySQL 2016. MySQL has implemented a reasonably good subset of it with some meaningful extensions. MariaDB is catching up to it and we include the extensions. Another one more slide about low-hanging fruits. As you saw, there are some missing features. I think JSON is a very good portion of code to start contributing to because it is fairly isolated from the rest of the code and it doesn't have a lot of legacy or compatibility concerns to care about. So low-hanging fruits, in my opinion, will be to implement last or end-to-end array indexing steps. A big and perhaps a bit higher hanging fruit is to add filtering support, but this would be very useful. Much lower-hanging fruit is to improve the JSON-detailed function. JSON-detailed function is a basically JSON-pretty-printed, print JSON in a readable form. This is very useful for development. This is also useful when dividing the optimizer, because you can get the data from the optimizer phrase, do some processing, and then you need to print it for viewing. That function works, but it's fairly dumb. So we would be glad to review and accept the contribution to make it produce more condensed output. All these are great ways to start contributing to MariaDB, so please contact us if you are interested. This concludes the talk. Now we can have a Q&A session. Thanks.
 Hello, this is a talk titled JSON support in MariaDB, use, non-use and the bigger picture.  My name is Sergey Petronyev, I am a MariaDB developer, so for me, use means new code,  non-use means old code, so we are going to talk about the new code in JSON support in  MariaDB as well as some of the code that is already there.  The first topic is the JSON path.  This is not use, there are multiple JSON functions in MariaDB already and a lot of them accept  JSON path expressions as an argument.  These are used to locate elements in JSON document.  Path language in MariaDB wasn't documented until recently, perhaps some people thought  that it is just follow.bar.buzz or what can be its obvious, but the reality is not that  easy.  If one searches for the definition of JSON path on the web, you can find many different  definitions, but the one that should be used with SQL language was defined in SQL 2016  in the section SQL JSON path language.  So let's look at that in detail.  A JSON path starts with the mode.  The mode can be either lux, which is the default that is used if it is not specified or strict.  Then goes the dollar sign.  Dollar sign denotes the context element.  By default, it is the root of the JSON document, but some functions may use other context elements.  Then it is followed by multiple steps, which select the elements in the JSON document.  The first kind of step is object member selection step.  You can select a member by its name.  For example, here there is a JSON object and you can select name.name and you get the value  MariaDB.  You can select all members by specifying.star and this produces a sequence of values of  all members of the array, in this case MariaDB followed by 10.5.  Strict mode requires that the context element is an object and if the member name is specified,  it must be present in the array, otherwise it is in the error.  Lux mode ignores missing elements, unwraps one element array and tries to avoid errors.  The second kind of step is array element selection step.  If the context is an array, then you can select one of several elements from it.  You can just use number, the numbers are zero based, so if you take element number zero  and you get the first element in the array, you can specify a range, you can use the word  last to specify do you want the last element, you can use a comma-separated list of any  of the above, or you can specify all elements by using star.  In this case, you will get the sequence of all elements in the array.  Strict mode requires that indexes are within the bounds, otherwise it is in the error.  With Lux mode, there is no such restriction.  After each step, optionally, there can be a filter.  I mentioned that we get a sequence of elements and filter allows to filter out elements of  the sequence.  The syntax is question mark and then predicate.  It can be a formula, it can be a logical and or formula which has comparisons of constants  and elements which uses arithmetic.  There are certain functions supported and you can refer to parameters, pass it from  outside.  Here is a basic example.  We have a JSON document with two objects, we select them both by specifying we want  all members in the array and then we want only those that have member color equals to  black.  Then we add another step which is an item, which is a select member named item, so we  get just one value laptop here.  I won't go into further detail with filters because neither MariaDB nor MySQL supports  them.  Let's switch to discussing what does MariaDB and MySQL support.  They support only Lux mode.  I have actually discovered that even there they are not fully compliant.  Then I discussed this question with MariaDB developer who developed this feature.  He said he was just following MySQL.  MySQL market is bugger verified so at least perhaps they intended to be compliant.  We don't know for certain, they don't state in their documentation.  Object member selection is supported.  It's the basics.  For array selection single index is supported by both.  MySQL also supports last and M to M.  As I mentioned, filters are not supported.  The people who coded this to cut down a lot because they didn't need to support expressions  or arithmetic of functions.  On the other hand, it uses the expressive power of JSON paths quite a bit.  There is however an extension supported, recursive search.  For example, we have a task to find certain member anywhere in the JSON document.  With SQL, JSON path is not possible.  You need to specify the full path to where you want.  So both MariaDB and MySQL support an extension, while the current search steps.  If you use double star, it will select all direct and indirect children of the current  element.  Then you can add another step to select member of the array.  This way you can find, for example, price, element number price, limit price anywhere  in the JSON document.  PostgreSQL also supports this extension, but their syntax is dot star star.  So the syntax is different.  But the semantics is the same.  How is that useful?  Well, one and the use that I'm using almost daily is optimizer trace.  Optimizer trace is a JSON document.  It's basically a look of query optimizer actions.  This is a huge JSON document.  It has recursive structure because SQL allows recursive constructs like subqueries.  So we get the same kind of actions done at different levels.  Some of the actions done by the optimizer are done for each table.  One of them is to estimate the number of rows in the table, rows estimations.  So you can write a query like this, select JSON detected.  It's just that formatting JSON extract.  And then dollar star star rows estimation.  It finds you all rows estimation that we've done for the tables used by the query.  In the top level select and the subqueries everywhere.  If filters were supported, we would be able to find row estimation for a specific table.  Unfortunately, they're not so we need to get them all that just look ourselves.  So for deeply nested JSON documents, wildcard search step is very useful.  Summary.  So the language for pointing nodes in JSON document is called SQL JSON path language.  It was introduced in SQL 2016.  Both MariaDB and MySQL implement is upset.  It's now documented.  Lacks mod only.  Filters are not supported, which is unfortunate.  Array indexing only supports single integer index.  And both MySQL and MariaDB have the same recursive search expression.  That is good.  As if you look at other databases, PostgreSQL seem to have the most compliant and feature-rich  implementation.  They support filtering strict locks mode and so forth.  I'm not sure if they are fully compliant, but they seem to be.  Other databases support different and often very restrictive subsets.  So here my score and MariaDB are quite competitive.  The next topic I wanted to discuss is news.  It is JSON table feature.  News because it is under development in MariaDB.  JSON table is a table function.  It takes JSON input and converts it into a table.  So unlike other functions, use it in the front close.  It was introduced in SQL 2016.  It's supported in Oracle database in MySQL.  It is under development in MariaDB.  We are trying to get it into MariaDB 10.6.  Let's start with an example.  We get a JSON document which is an array of two objects which have the same elements.  And suppose we want to get that data in table or form.  So we can call the JSON table function.  We select star from JSON table, then we specify which cobs we want and we get it in the table  or form.  Let's look at the syntax for detail.  Select star from JSON table.  As I've mentioned, JSON table is a table function.  So you use it where you would use a table.  The first argument is JSON doc is a source document.  In this case, it's in a session variable, but it can be in other places as well.  The second argument is a path to node, JSON path to nodes to examine.  This produces a sequence and then each element of the sequence produces one, its own row.  Then go column definitions.  This is a bridge from schemaless and typeless JSON into the highly-typed world of SQL.  This is a schema on read where you specify what schema we expect to get when we are getting  the data from JSON.  And here we specify paths where to get the values from.  The context for these paths is the node being examined.  If that was not complex enough, it gets more complex.  Nested paths are supported.  Suppose we get some JSON document describing some items and they in turn have some code  arrays in them.  How do we normalize this structure?  JSON table supports nested path syntax.  So we first specify that we will break the JSON document into a sequence of objects.  And then inside object we specify that each color will also produce its own row.  And the output will be normalized.  Normalized form where the first object which describes a laptop will produce three rows  for one for each color.  And for the second for the t-shirt we will get two rows for one for each color as well.  This allows to switch from nested structures to normalized form in a relational form.  Multiple nested paths are supported.  You can nest nested paths and one can have sibling nested paths.  The standard allows one to specify how they want to unnest.  It's called plan close.  The default is outer join like.  So if we get an object which says name to shirt, which can be two colors and three sizes,  we get all of the colors with size null and we get all of the sizes with color being null.  The plan close allows to specify from cross join or some other variants how to normalize  it into relational form.  My score and sum MariaDB will only support outer join like unnesting, which is the default,  which basically means we don't support the plan close.  Another feature of JSON table is error handling.  JSON doesn't have types, so one need to account for possible errors when converting to a relational  form.  And JSON table syntax allows to specify that after specifying path, you can specify action  to do on empty, which is used when the JSON element requested is missing or on the error  when there was the type conversion.  You can produce a score now, you can use some default value or you can just see me in the  error.  Both my score and sum MariaDB support this.  An important part that JSON table can be used with joins.  Let me explain what I mean by it.  Suppose you've got the table orders, which there are two rows describing two orders.  Under each order, you get a list of JSON document, which describes the items in that order.  How do I get that in the relational form?  You can write a query like so.  You can take table orders, then join it with JSON table, and its argument will refer to  table orders, which is not typically possible, but JSON table allows it.  And this way, you will get normalized output.  This JSON table can refer to other tables, and this produces lateral-like semantics.  Contents of the table produced by JSON table will depend on the parameter values.  This is great to do normalization.  If you have some data in...  If you have a table which has some data in JSON, you can normalize the data to SQL by  doing join with JSON table function.  Summary for JSON table.  So it is a table function to convert JSON data to relational form.  It has been introduced in SQL 2016.  The standard specifiers of all the features may escalate, it will be upset, but in particular  plan clause is not supported.  In MariaDB, it is under development.  This is the generation number, and we are going to implement a subset that is very close to  what MySQL did.  As for other databases, let me quote slide from Markus Wienand, which shows that basically  only Oracle MySQL supported.  Now we are going to add MySQL.  Well, I know that PostgreSQL have it under development.  They had a patch posted on their mailing lists.  I'm not sure what is how much work they have left to do.  Total takeaways is MySQL 2016.  MySQL has implemented a reasonably good subset of it with some meaningful extensions.  MariaDB is catching up to it and we include the extensions.  Another one more slide about low-hanging fruits.  As you saw, there are some missing features.  I think JSON is a very good portion of code to start contributing to because it is fairly  isolated from the rest of the code and it doesn't have a lot of legacy or compatibility  concerns to care about.  So low-hanging fruits, in my opinion, will be to implement last or end-to-end array indexing  steps.  A big and perhaps a bit higher hanging fruit is to add filtering support, but this would  be very useful.  Much lower-hanging fruit is to improve the JSON-detailed function.  JSON-detailed function is a basically JSON-pretty-printed, print JSON in a readable form.  This is very useful for development.  This is also useful when dividing the optimizer, because you can get the data from the optimizer  phrase, do some processing, and then you need to print it for viewing.  That function works, but it's fairly dumb.  So we would be glad to review and accept the contribution to make it produce more condensed  output.  All these are great ways to start contributing to MariaDB, so please contact us if you are  interested.  This concludes the talk.  Now we can have a Q&A session.  Thanks.
mariadb_json_0_179.png mariadb_json_180_731.png mariadb_json_732_875.png mariadb_json_876_2087.png mariadb_json_2088_3047.png mariadb_json_3048_4367.png mariadb_json_4368_5603.png mariadb_json_5604_6047.png mariadb_json_6048_7511.png mariadb_json_7512_9131.png mariadb_json_9132_10703.png mariadb_json_10704_12947.png mariadb_json_12948_13907.png mariadb_json_13908_14519.png mariadb_json_14520_14807.png mariadb_json_14808_15347.png mariadb_json_15348_16331.png mariadb_json_16332_18419.png mariadb_json_18420_20183.png mariadb_json_20184_21491.png mariadb_json_21492_22487.png mariadb_json_22488_22643.png mariadb_json_22644_23639.png mariadb_json_23640_24407.png mariadb_json_24408_25199.png mariadb_json_25200_25751.png mariadb_json_25752_26231.png mariadb_json_26232_28391.png mariadb_json_28392_28511.png mariadb_json_28512_28787.png mariadb_json_28788_28911.png