Viva, the NoSQL Postgres !
Korotkov, Alexander
Bartunov, Oleg
PostgreSQL is the first relational database which recognized the need of non-atomic data types to support developers of applications from science to Web. Jsonb in Postgres is the attractive feature for modern application developers, who want to work with json documents without sacrificing a strong consistency and ability to use all the power of proven relational technology. Finally, SQL world has recognized the NoSQL and released the new SQL-2016 standard, which includes specification of SQL/JSON data model and path language, as well as SQL commands for storing, publishing and querying JSON data. We will present the implementation of jsonpath, the path language for querying json in PostgreSQL and discuss possible extensions, such as new indexing mode for non-atomic data, which allows to index only specified parts, for example, those parts of jsonb which matches given jsonpath[s] to opclass.
 I am Ali Egbertunov. I work for the Moscow University and the PostgreSQL Professional Company and today I will talk about the Moscow PostgreSQL, particularly about JSON Pass, which is a query language for JSON. Here is a... Yes? Yes, sorry. I forgot. Okay, it's okay? Okay. Today I will talk about NoSQL PostgreSQL and this slide is just a very short history of NoSQL features in PostgreSQL. As you see on this picture, this evolution of NoSQL databases, so they started from very primitive to very complex NoSQL database and that time, on vertical, PostgreSQL also was evolved from the HStore data type, which implemented 2003. It's 16 years ago, PostgreSQL already had some NoSQL features and in 2012 we got a JSON data type, which is... PostgreSQL was the first relational database who implemented JSON. In 2012, in 2014 we implemented JSON B, which is a binary storage for JSON. It supports nesting objects arrays and what was important, it was practical because it has index support. So people were very excited and start using JSON B and it's very popular. Today I will talk about JSON Pass. 2019, it is a part of SQL JSON 2016 standard and it has a lot of function operators and also support indexing. I hope next year I will talk about the full SQL JSON implementation. You see that this is a very brief NoSQL PostgreSQL in short and the state of art of JSON in PostgreSQL is that we have two data types actually. We have JSON and JSON B and this select shows all the different of the features of two data types. You see that left is a JSON, this is a textual storage as is and the right is a JSON B. So you will see the difference and JSON B is a binary storage. We don't need to parse, it has index support and it doesn't preserve white spaces and no duplicative case. So you see that last key win and JSON B has a key sorted first by length and then by key name. It has a lot of functions. If you have P-SQL you can just see the backslash df JSON B star. You see a lot of functions. So we have a very flexible functional interface and what's nice is it has a great performance because of indexes and performance of JSON B and many people verified and tested and said that it's the same level as MongoDB so people start using PostgreSQL instead of Mongo. Why use Mongo if we have good universal database with the same JSON support. Also we have a JS query. JS query, we don't know this is extension which provides JSON query language and also several index op classes for JSON. It's not part of the PostgreSQL distribution but it's very useful and I will show some of features. Okay, so JSON B is cool but it is a black box for SQL. So from SQL you have no idea how to operate with the structure of JSON. For example, this very simple case. You have some database, some table with red color and you just want to find all these rows without specifying exact nesting. So with SQL you have to do like this query with recursive. We specially designed JS query to do queries like this. It's very simple. So we developed it in 2014. It's maybe two years before SQL standard specified JSON pass. So you see that with SQL actually it is very difficult to work with JSON B, with JSON. So JSON pass is really interesting feature and SQL standard in 2016, in the fall of 2016 introduced JSON support specification. So why it happens? This is one marketing slide. It's because PostgreSQL evolution, because PostgreSQL embrace the relational database and no SQL users start, was attracted by no SQL postgres features. And this is a data from DB engines. I just download data from DB engines and made a relative growth of popularity Postgres, MySQL, Oracle, MS SQL. And you see that clearly you see that popularity of PostgreSQL from 2014 when we released 9.4 with the JSON B support. So this popularity growth is because of, particularly because of we got JSON B. So we, our community attract more users. And that's why it is very important to us to continue developing no SQL features in PostgreSQL. And new development is part of this project of no SQL postgres. So this screenshot of the actual SQL 2016 documentation and you see that there is a JSON data model, JSON functions and JSON pass language. What's actually specified in SQL JSON standard? So actually it doesn't specify the data type. It specifies the data model and string storage. So it's just a string where you store your JSON. You store a, it's a sequence of items of SQL values, no SQL JSON. It's not SQL null. It is JSON null. And JSON arrays and JSON objects. I also standard specified JSON pass language and a bunch of SQL JSON functions, construction functions and query functions. In PostgreSQL we already have JSON B. And JSON B is a practical subset of a SQL JSON data model. If you specify order it and unique keys. So we don't need to introduce new data type. We already have two. We don't need a third data type. So because JSON B is a practical, people use it, why more? Also JSON pass language is very important part. So we decided to implement it for PostgreSQL because it's the most important part. Because SQL JSON function, we already have constructed a function. JSON B construction function, it's the same. It looks like the same as the standard. Query functions, yes, we need some functions with JSON pass support. This is what we also implemented. Indexes. The challenge was that try to use already existing indexes. Building and just query. What do you mean? It means that if you have indexes already for JSON B, that indexes should support JSON pass also. And this is what we did. And also add some more specific to JSON pass of classes. So this was our plan for implementation of Pg12. So what is a JSON pass? It's an expression which specifies which part of JSON you interested. So you know that JSON actually, basically it's a tree. And with this language, you can specify which part of tree you interested to do. For example, because you know that JSON, for example, what to index, what to select. And it has some optional mode like strict and lax. Here is an example of how JSON pass looks like. First and first people said that it looks like not very interesting, not how to say, don't understand actually. Lax, dollar sign, dot, we use this dot notation. It's specified by SQL standard. And this is a accessor, a member accessor, a reaccessor. And after the dollar sign, question sign, we have in parenthesis, we have a filter. But if you make analogy with SQL, SQL is a language for relations. JSON pass is a language for the JSON, for the JSON elements. So it's very easy. You start from the beginning and made operator to the SQL JSON items. And output also SQL JSON items. So as in SQL, you can make the chain SQL operators. In the JSON pass, you also have chains just connected by dot. And the filter in the parenthesis is just as a SQL where it's filtered. So actually, JSON pass very easy to understand. So dollar is the current context element. It's a JSON, the whole JSON. And this is a reaccess. And the rest starts from zero as a JSON filters. We have a add sign, add sign we use in filter. This is a current context in filter expression. And we have item method. So this is just a function attached to the JSON pass. So you can apply, it's a predefined set of eight functions you can use for JSON pass. So actually, what this is, JSON pass looks like. And next, I will illustrate some example. Yes. Yes, I will show you. So to play with, to demonstrate the JSON pass, I will use the example of two floor house. So the house with two floors, it has rooms. Rooms has some metadata like area, room number, and so on. So this is a function. And for example, how JSON pass works, this JSON pass, we start from dollar sign. Dollar means that all green is dollar on the first step. Second step, we have a member access dot floor. So we choose floors object. And third step, we choose, we have an array accessor, array accessor. So we choose arrays. So floor one, zero floor one. Next, we have a member accessor for apartment. So we've got five apartments, five apartments. After applying array accessor, we got actually apartments. And finally, finally, we apply filter. It's like where? We filter all this apartment for the area, area of rooms. And we got just two items from all this. So this is how JSON pass works. This is a very simple example because you can continue, you can attach the more path elements, more filters to the JSON pass and do everything. What do you want? This is steps I just shown you. And Lax and strict is a mode, is a mode for JSON pass, which try to facilitate matching of layer slope document structure and pass expression. Because you know that JSON is a data type which has, there is no fixed schema. So sometimes keys may be missed. So what to do with Lax and strict? They manage what to do if you don't have, for example, key. So in Lax mode, Lax is a relaxed mode. Missing keys just ignored. You see this. JSON B, this operator is a, exists operator. JSON B and JSON pass. So in Lax mode, it just produce false and no error and action because you see that B key missed in the JSON. And filter just return false. In the strict mode, missing keys result at null. So strict mode requires more, I would say, more exact matching. The next example will illustrate this. For example, in Lax mode, arrays are unwrapped. You see that we have a nested array and we are looking for the member equals five. And in Lax mode, all these two variants are the same. So you don't need to specify a wild card array accessor. Because in Lax mode, arrays just unwrapped, but only one level. In the strict mode, you require exact nesting. So you have to specify array accessor to get the true, to match the JSON B. This is very important. Here are the examples of valid JSON pass expressions. So dollar sign, the whole JSON document, dollar foo, you can specify variable. And variable, you can pass to the JSON pass, actually, from outside. So you can string literal or numeric literal, true, null. You can specify member accessor. Member accessor looks like key. You can specify array accessor. You can use a complex pass with filters and variables. For example, you see that we have here two filters. And inside filter, we use variables. Variables come from outside. Yes. And you attach the member. So this is a complex pass expression. Or you can specify arithmetic expressions. It's very cool, actually. I always miss some possibility in posities to do something with the array, with all members of array. Here, you can just specify this. So you change sign of all members of array A. Or you add free to the any member of the array, to all members. Or even specify the complex expressions with variables. So this is what you can do now with the content of JSON. Here are a lot. Another example, you can specify item methods. So you have arithmetic expressions. And then you specify, you get the absolute value and then sale. And if you made a syntactical error, so you get error message. What is the filter expression? As I said, this is just as a way of closing SQL. So you have on input SQL JSON sequence. Sequence is an ordered list of SQL JSON items. And then you check if they satisfy the predicate. So the syntax, you see, it's a question of science. And in parenthesis, you can use some expression. Inside expression, you can use add sign. It is a reference to the current SQL JSON item to process. And predicate. We have several predicates you can use in filter expression. Exist comparison. Like regress, it's like posive regress. You can use even flex also. Start with and is unknown. So this predicate you can use in filters. Here's an example. All arithmetic errors in filters suppressed. So, for example, in this JSON pass, in this filter, you see the division to the context item. And some, here is a zero. So you can, it just ignored. So you get just one. I think this is very clear. First time it looks not like a human language. Yes. But after playing, you understand all this. JSON pass has a predefined method which attach to the JSON pass expression. So, for example, here we got key value pairs. We just use the key value method. And we get ID key in value. And we can combine methods. For example, attach to the key value, the next key method. And we get just key names. As I said, this is a predefined. We have eight methods we can use. So this was about standard. But what's about posive? We implemented JSON pass as a data type, JSON pass. Because it is a binary representation of parsed SQL JSON pass expression. You see that pass expression can be very complex. And we save the parsed in binary format that we save a lot of instruction in CPU. And also, we got, if we have a binary representation, we can use indexes. You can use indexing. And we can use existing indexes for JSON B. Because we introduced some operators for JSON B and JSON pass. And they are very useful. You will see, I will show you. And that's why we have not just a string literal, because as a standard, but separate data type. Also, we introduced several functions to query and to check for JSON pass. It's JSON B pass exists. It's a Boolean. We just check the pass expressions if it returned any items. We have a coding operator. JSON B pass match. Query, query array, and query first. So this query is what we have now. Functions, they have the same signature, all functions. The signature, the first argument is the first parameter is a JSON B. Second is JSON pass. The third is vars. It's a JSON B object. Remember that you can pass variables to the JSON pass. Here's how you can specify. And one Boolean flag, silent. So the silent is responsible for the error messages. Because sometimes you need error message. Sometimes you don't need. People, usually if you work with a JSON, it means that you don't need any errors. So just silent. True. But by default, we have silent false. Because we are in Postgres, we like some very strict, you know. Yeah. Here's the example how vars object used for passing JSON pass variables. So we have dollar X, dollar X variable, and it comes like this. This is a JSON B object. You can specify any complex JSON B expression. I said about silent. Here's the example how it's used. Yes. For example, the first query, you get error. Because this is a strict mode. This is strict mode. In strict mode. And when you try to access a non-existed key, you get error. Remember not found. But if you specify silent true, then you get just no silent result, silent zero. Here's examples of how to use JSON pass functions. They are, I would say, very simple. So for example, you understand JSON pass query. We have JSON, JSON B. Yes. This way, we get array accessor. So we just iterate through all this array element and check if they are greater than two. So results is very clear. Three, four, five. Just very simple. Another example for the other examples, maybe here's a Boolean operators for exist operators, match operator. They are actually interchanged. You can use match instead of exist, for example, or exist instead of match. And some examples, the same floor, the same house, some examples of queries. This we try to get all apartments from one to last, this query. So all this green, we want to get from this JSON. In PG 11, currently, you have to use like this query. You see? You have to use subselect. With the PG 12, you use just this. So actually, we can live without JSON pass. It will be just, it looks very ugly. And actually, it's just simple example. For some more complex example, it will be very complex query. And the nice thing about JSON pass that JSON pass actually faster than this query. I will show this. Here is another query. You get all apartment with area in the greater than 40 and less than 90. So again, how we choose all these green elements. In PG 12, you can use already known JSON pass expression like this. And in PG 11, again, you have to use like some subselect. So here's a, add sign is a room object. It pass from the, from the, from the pass expression. You see? First, we have a dollar. It's a full JSON. From dollar, we get a floor. Floors array. It's a two floors. And for each floor, we get all apartments from here. And then we check for the area. So it looks not very complex. Then we made extension. The only extension to JSON pass we implemented. We introduced recursive wild card. So for example, this query tried to look for how to get the Moscow without specifying which level where just need like a find in JSON Moscow string. With PG 12, it is very easy. You see, this is two queries. The same. So we just specify wild card nesting and the test in the filter for string matching Moscow. In JS query, also you can, you can write this already. No. In PG 11, you have to write again. Long, long, long. You should navigate through the old structure of JSON and check. So JSON pass is very good in POSGRIS as we see implemented. But one method we did not support. It's a daytime. As for JSON, they introduced daytime. And this daytime is not type supported by JSON. You know that JSON actually has no daytime support. People complained about this. But SQL JSON introduced just a method which used to, for comparison. So you still store on disk like a string. It's not a special data type. And the reason why we did not introduce, why we did not implement daytime is because POSGRIS, in POSGRIS, daytime is not immutable. You all know that it depends on the time zone, current time zone. So if we introduce daytime into the JSON pass data type, then the whole data type will be non-immutable. So you can't use index for this. And this problem is a bit complex because it touched some hidden parts of POSGRIS. We were not ready to work. So we decided, okay, let's, we can live without daytime, without daytime. But we have indexes. And actually, we have several, we have proposal how to implement immutable daytime, how to support daytime. And we published and commit first our patch. But we hoped to work it for the next version. So currently, we have this error, bad JSON pass representation. But SQL standards should work. Okay. So what we, we checked the SQL JSON standard conformance. And we, SQL standards specify 15 features. And we support 14. As I said, because of daytime, we just did not support all. So we have 14 out of 15. Oracle 11, MySQL 5, and SQL service 2. So POSGRIS 12 has the best implementation of JSON pass. How we check, we just write the scripts and check the actual database server how this works. So more information about JSON pass, you can find our gentle guide because when we wrote the documentation for the PG12, our documentation is just reference manual. And very difficult to get the whole picture. Because in our documentation, you have some parts described here, some parts are described there, and very difficult. So I decided to write a separate gentle guide. And you can read, try examples and so on. Indexing. Indexing is very important for performance, you know. Here is an explanation of how indexes work for JSON B. So we have a simple JSON B with K value, KV, and so on. And we have JSON B operations. It's a default GIN op class. When you, when you say create index using GIN, this op class will work. And it supports all operators, all operators. But it's a bit slow and a bit big, and big, because we index all key values separate, and then we have to overlap them. Yes. There is a much faster and much smaller operator JSON B hash ops, where we index hash of K value, K attached value. Of course, we doesn't, we support a container operator, but doesn't support actually exist operators. Yes. So if you don't need exist operator, this is recommended. JSON B hash operator. This is much faster and faster, and smaller than default. We have also three, three more op classes. In JS query extension, we have JSON B path value operations, which is hash, a full path with the value attached. So what does it mean? It means that this op class support comparison operation for values, exact and range queries, also exact path searches. For example, if you see the rooms whose area is greater than 40, this op class will support, because you will have value attached to the path. One specific to JSON path operation is a JSON B lax path value operations. It's the same as the first one. In JS query, we just made some optimization for lax mode of JSON path. And the last one is JSON B value path operations. Here's a value attached to the bloom, to the concatenation of all bloom, bloom filters. So it supports exact value search. So and wildcards past queries. So if you have, if you understand what you want to speed up, you can choose what you need. Actually, unfortunately, we can't make the single op class, which supports all everything. There is no silver bullet. JSON path queries could use existing JSON B indexes, and we verify it. So we have a JSON B index for bookmarks table. It's a delicious bookmarks. And we created using JSON B path ops. And we run query with JSON path. And we have for sequential scan, we have 35 seconds. And with index scan, we have just six milliseconds. So existing means that index already exists on the, on the, on the disk. So we did not introduce do something new. But JSON path supported already. Let's check the performance of JSON path. For simple queries, we use a test table with three million rows generated like this query. Actually, just three million of the same, same row. Three million. And we check the queries using JSON path, many, many variations. Yeah. And old way using error operators. Well, as now in PG 11, you can use error operator. And we see that error, error operator actually faster because it's simple query. And the podcast executor would optimize this for simple query. Yes, but JSON path remind you supports very complex query. And the next example is example how performance for the complex query we took a INDB international movie database, convert to the JSON. And the JSON looks like here, here's a roles, arrays of roles, actors, title and so on. Yeah. And we try to find all actors and editors in the same movie. So actor should, we are looking for the, and the size is more than six millions actors, names. Yes. And interesting that more than 40,000 people were actors and editors. We have every JSON path. We got several with sequential scan, sequential scan parallel. You see that parallel works very well. And we index count just query index we use. It's two and three seconds. Old way is a bit slower, someone one second slower. So for the complex queries, JSON path is better because we use JSON path use internal executor instead of postgres executor, which has a lot of overhead for calling functions and so on. If you use error operators, subselects. Also we check the relational way. So we took INDB database in relational format. Yes. And run queries. And we see that relational way also. It's a different, you know, different sequential scan with parallel scan, bitmap index, index on parallel. And the best results is the same as a error operator for JSON. And we took a Munga, the last version of Munga. And we see that performance also the same as relational and error operator. But all these three are slower than JSON path. So the roadmap. The roadmap we have in the next version, we want to complete SQL JSON implementation. Yes, we fixed the problem with datetime. We want to use planner support functions for JSON path. If you don't know that, what is it? It is a very useful feature of postgres, which was committed by Tom for Pg12. And we actually have already implementation just after feature freeze. So it helps you to, for JSON path very well. We want to implement, not implement, everything already implemented. We want to commit. We need just resources to commit and people, I ask people for reviewing and so on. So we have already op classes for, for parameters for op classes. Very important. You can imagine that you have very big JSON path, JSON B table. And you want to create index. Currently you index everything. You know. But with JSON path, you can just specify which part of the index. So your index will be smaller, faster, and that could be happens if we add support for parameters to op class. You know that in postgres we have many, not many, but we have several parameters hard coded into the op classes. For example, for signature. The length of signature is hard coded, for example. Or blue filter. Or, or green. We have some hard coded constants there. It would be much flexible if you could, could specify parameters for the op class. Because if you index small strings, you don't need two kilobytes bit string. You just say that eight kilobytes is okay. So parameters for op class are very good, not only for JSON path, but overall, overall postgres. Yes. We want to move some genome classes to core. Because we don't want people install JS query just to have some additional op classes. Yes. One is extend JSON path syntax. We have contribute JSON path X, which contains implementation for arrays, objects, sequence constructions, object subscripting. We have lambda expressions. And this is what we interesting its user defined methods and functions. We know that progress is well known for its extensibility. So why not to use in JSON some user defined methods and operators? So this is what we also need. Very interesting project is a copy with JSON, with support of JSON path. You know that now data in the internet distributed in the JSON. And this JSON, it's looked like big array of JSON. It's paying to import to the postgres. But if we copy, it has support of JSON path, you can just say what you want to import. Very flexible. This is our roadmap. And if you're interested in more, we have several addendum and all these items explained on separate slides. Yes. So we have good roadmap, no SQL policy, okay. So we don't need Munga. And the summary that we already have good no SQL database. And SQL JSON provides flexibility and interoperability. So the same query should run on Oracle, on postgres and other databases who supports SQL standard. And JSON path is very good for complex queries. Okay. This summary. These are reference all links to the additional materials. So this talks, you can find my talk slides. Very useful technical report for SQL JSON. It is free. You can download and read human-oriented specification of SQL JSON. Because SQL standard is not for us. Yes. So this is okay. Yeah. My gentle introduction to JSON path, JS query and presentation about JS query. So if you are lazy, you can just have a play with JSON path online. We have SQL Fiddly with the latest master and JSON path inside. So you can play just from your laptop or smartphone. We have a presentation about parameters for op class. And if you want to play with international movie database in JSON format, you can download it. So very interesting. You can get very interesting statistics about movies, actors and so on. Of course, here is a people who was working on all SQL progress. And this, I said that no SQL progress is an example of how our community works. So it's not just me or just my colleagues. There are many people from our community, from different companies. Several companies supported all this project for many years. And I want to say that thanks to all these people who invest their time, their resources to this very interesting project. Yes. Some of them are here. I see that people. Yes. The last slide is that all you need is posgres. Yes. Thank you. Yes. So the questions actually I have a lot of, an example of SQL JSON, how it looks at JSON table. What you expect in the next version? You see, this JSON table functions provides you ability to convert JSON to the relational. It's a very powerful, it's like XML table. So using JSON table, you can join to the relational data. Yeah, very powerful. This example, you have a parameter for up classes. We have planner support for JSON pass. How it looks like. We have syntax expression. For example, if you want to get array construction, just use arrays. Object sequence construction or subscripting array lambda, who wants lambda, map reduce fold. Very flexible. This is already implemented. We have extension. Yes. But we want to maybe contribute to core. Yes. Sequence with lambda expressions, user defined methods and functions. So you can just implement your performance of intrad joined. This example, if you want to play with MongoDB. So this is the best. We check maybe five different methods to run query. And this is example how it looks on Mongo. Looks, no. And you remember how it looks on with JSON pass. Very elegant query. But with MongoDB, you have to write like this. It's a map, of course, with mapping. Yes. Here is the example of relational. How to do relational? You see that with relational database, it becomes much bigger. Here is the JSON pass. With JSON B, here is a relational. We understand because relational contains much more roles. Because one actor could be participating in many, many roles. Yes. This is example of query on relational. Yes. This is how you can play with two floors. So you download slides. You study the slides, play. And you become, understand how JSON pass works. Thank you again. If you have questions, I am available all day. We can talk. Yes. Slides already available. You can download. So the main goal is, you know, it's very difficult to explain such a complex thing in 40 minutes. You just need to, that you are interested in. Yes. So you go to the all nestings, all levels, all levels, recursive. Yes. Star-star is the theme, yes.
 I am Ali Egbertunov. I work for the Moscow University and the PostgreSQL Professional  Company and today I will talk about the Moscow PostgreSQL, particularly about JSON Pass,  which is a query language for JSON. Here is a...  Yes? Yes, sorry. I forgot. Okay, it's okay? Okay. Today I will talk about NoSQL  PostgreSQL and this slide is just a very short history of NoSQL features in PostgreSQL. As  you see on this picture, this evolution of NoSQL databases, so they started from very  primitive to very complex NoSQL database and that time, on vertical, PostgreSQL also was  evolved from the HStore data type, which implemented 2003. It's 16 years ago, PostgreSQL already  had some NoSQL features and in 2012 we got a JSON data type, which is... PostgreSQL was  the first relational database who implemented JSON. In 2012, in 2014 we implemented JSON  B, which is a binary storage for JSON. It supports nesting objects arrays and what was important,  it was practical because it has index support. So people were very excited and start using  JSON B and it's very popular. Today I will talk about JSON Pass. 2019, it is a part of  SQL JSON 2016 standard and it has a lot of function operators and also support indexing.  I hope next year I will talk about the full SQL JSON implementation. You see that this  is a very brief NoSQL PostgreSQL in short and the state of art of JSON in PostgreSQL  is that we have two data types actually. We have JSON and JSON B and this select shows  all the different of the features of two data types. You see that left is a JSON, this is  a textual storage as is and the right is a JSON B. So you will see the difference and  JSON B is a binary storage. We don't need to parse, it has index support and it doesn't  preserve white spaces and no duplicative case. So you see that last key win and JSON B has  a key sorted first by length and then by key name. It has a lot of functions. If you have  P-SQL you can just see the backslash df JSON B star. You see a lot of functions. So we  have a very flexible functional interface and what's nice is it has a great performance  because of indexes and performance of JSON B and many people verified and tested and  said that it's the same level as MongoDB so people start using PostgreSQL instead of Mongo.  Why use Mongo if we have good universal database with the same JSON support. Also we have a  JS query. JS query, we don't know this is extension which provides JSON query language  and also several index op classes for JSON. It's not part of the PostgreSQL distribution  but it's very useful and I will show some of features.  Okay, so JSON B is cool but it is a black box for SQL. So from SQL you have no idea  how to operate with the structure of JSON. For example, this very simple case. You have  some database, some table with red color and you just want to find all these rows without  specifying exact nesting. So with SQL you have to do like this query with recursive. We specially  designed JS query to do queries like this. It's very simple. So we developed it in 2014.  It's maybe two years before SQL standard specified JSON pass. So you see that with SQL actually  it is very difficult to work with JSON B, with JSON. So JSON pass is really interesting  feature and SQL standard in 2016, in the fall of 2016 introduced JSON support specification.  So why it happens? This is one marketing slide. It's because PostgreSQL evolution, because  PostgreSQL embrace the relational database and no SQL users start, was attracted by no  SQL postgres features. And this is a data from DB engines. I just download data from DB  engines and made a relative growth of popularity Postgres, MySQL, Oracle, MS SQL. And you see  that clearly you see that popularity of PostgreSQL from 2014 when we released 9.4 with the  JSON B support. So this popularity growth is because of, particularly because of we  got JSON B. So we, our community attract more users. And that's why it is very important  to us to continue developing no SQL features in PostgreSQL. And new development is part  of this project of no SQL postgres. So this screenshot of the actual SQL 2016 documentation  and you see that there is a JSON data model, JSON functions and JSON pass language. What's  actually specified in SQL JSON standard? So actually it doesn't specify the data type.  It specifies the data model and string storage. So it's just a string where you store your  JSON. You store a, it's a sequence of items of SQL values, no SQL JSON. It's not SQL null.  It is JSON null. And JSON arrays and JSON objects. I also standard specified JSON pass language  and a bunch of SQL JSON functions, construction functions and query functions. In PostgreSQL  we already have JSON B. And JSON B is a practical subset of a SQL JSON data model. If you specify  order it and unique keys. So we don't need to introduce new data type. We already have  two. We don't need a third data type. So because JSON B is a practical, people use it, why  more? Also JSON pass language is very important part. So we decided to implement it for PostgreSQL  because it's the most important part. Because SQL JSON function, we already have constructed  a function. JSON B construction function, it's the same. It looks like the same as the standard.  Query functions, yes, we need some functions with JSON pass support. This is what we also  implemented. Indexes. The challenge was that try to use already existing indexes. Building  and just query. What do you mean? It means that if you have indexes already for JSON  B, that indexes should support JSON pass also. And this is what we did. And also add some  more specific to JSON pass of classes. So this was our plan for implementation of Pg12.  So what is a JSON pass? It's an expression which specifies which part of JSON you interested.  So you know that JSON actually, basically it's a tree. And with this language, you can specify  which part of tree you interested to do. For example, because you know that JSON, for example,  what to index, what to select. And it has some optional mode like strict and lax. Here  is an example of how JSON pass looks like. First and first people said that it looks  like not very interesting, not how to say, don't understand actually. Lax, dollar sign,  dot, we use this dot notation. It's specified by SQL standard. And this is a accessor, a  member accessor, a reaccessor. And after the dollar sign, question sign, we have in parenthesis,  we have a filter. But if you make analogy with SQL, SQL is a language for relations.  JSON pass is a language for the JSON, for the JSON elements. So it's very easy. You  start from the beginning and made operator to the SQL JSON items. And output also SQL  JSON items. So as in SQL, you can make the chain SQL operators. In the JSON pass, you  also have chains just connected by dot. And the filter in the parenthesis is just as a  SQL where it's filtered. So actually, JSON pass very easy to understand. So dollar is  the current context element. It's a JSON, the whole JSON. And this is a reaccess. And  the rest starts from zero as a JSON filters. We have a add sign, add sign we use in filter.  This is a current context in filter expression. And we have item method. So this is just  a function attached to the JSON pass. So you can apply, it's a predefined set of eight  functions you can use for JSON pass. So actually, what this is, JSON pass looks like. And next,  I will illustrate some example. Yes. Yes, I will show you. So to play with, to demonstrate  the JSON pass, I will use the example of two floor house. So the house with two floors,  it has rooms. Rooms has some metadata like area, room number, and so on. So this is a  function. And for example, how JSON pass works, this JSON pass, we start from dollar sign.  Dollar means that all green is dollar on the first step. Second step, we have a member  access dot floor. So we choose floors object. And third step, we choose, we have an array  accessor, array accessor. So we choose arrays. So floor one, zero floor one. Next, we have  a member accessor for apartment. So we've got five apartments, five apartments. After  applying array accessor, we got actually apartments. And finally, finally, we apply filter. It's  like where? We filter all this apartment for the area, area of rooms. And we got just two  items from all this. So this is how JSON pass works. This is a very simple example because  you can continue, you can attach the more path elements, more filters to the JSON pass  and do everything. What do you want? This is steps I just shown you. And Lax and strict  is a mode, is a mode for JSON pass, which try to facilitate matching of layer slope  document structure and pass expression. Because you know that JSON is a data type which has,  there is no fixed schema. So sometimes keys may be missed. So what to do with Lax and strict?  They manage what to do if you don't have, for example, key. So in Lax mode, Lax is a  relaxed mode. Missing keys just ignored. You see this. JSON B, this operator is a, exists  operator. JSON B and JSON pass. So in Lax mode, it just produce false and no error  and action because you see that B key missed in the JSON. And filter just return false.  In the strict mode, missing keys result at null. So strict mode requires more, I would  say, more exact matching. The next example will illustrate this. For example, in Lax  mode, arrays are unwrapped. You see that we have a nested array and we are looking for  the member equals five. And in Lax mode, all these two variants are the same. So you don't  need to specify a wild card array accessor. Because in Lax mode, arrays just unwrapped,  but only one level. In the strict mode, you require exact nesting. So you have to specify  array accessor to get the true, to match the JSON B. This is very important. Here are the  examples of valid JSON pass expressions. So dollar sign, the whole JSON document, dollar  foo, you can specify variable. And variable, you can pass to the JSON pass, actually, from  outside. So you can string literal or numeric literal, true, null. You can specify member  accessor. Member accessor looks like key. You can specify array accessor. You can use  a complex pass with filters and variables. For example, you see that we have here two  filters. And inside filter, we use variables. Variables come from outside. Yes. And you attach  the member. So this is a complex pass expression. Or you can specify arithmetic expressions.  It's very cool, actually. I always miss some possibility in posities to do something with  the array, with all members of array. Here, you can just specify this. So you change sign  of all members of array A. Or you add free to the any member of the array, to all members.  Or even specify the complex expressions with variables. So this is what you can do now  with the content of JSON. Here are a lot. Another example, you can specify item methods.  So you have arithmetic expressions. And then you specify, you get the absolute value and  then sale. And if you made a syntactical error, so you get error message. What is the filter  expression? As I said, this is just as a way of closing SQL. So you have on input SQL  JSON sequence. Sequence is an ordered list of SQL JSON items. And then you check if they  satisfy the predicate. So the syntax, you see, it's a question of science. And in parenthesis,  you can use some expression. Inside expression, you can use add sign. It is a reference to  the current SQL JSON item to process. And predicate. We have several predicates you  can use in filter expression. Exist comparison. Like regress, it's like posive regress. You  can use even flex also. Start with and is unknown. So this predicate you can use in filters.  Here's an example. All arithmetic errors in filters suppressed. So, for example, in this  JSON pass, in this filter, you see the division to the context item. And some, here is a zero.  So you can, it just ignored. So you get just one. I think this is very clear. First time  it looks not like a human language. Yes. But after playing, you understand all this. JSON  pass has a predefined method which attach to the JSON pass expression. So, for example,  here we got key value pairs. We just use the key value method. And we get ID key in value.  And we can combine methods. For example, attach to the key value, the next key method. And  we get just key names. As I said, this is a predefined. We have eight methods we can  use. So this was about standard. But what's about posive? We implemented JSON pass as  a data type, JSON pass. Because it is a binary representation of parsed SQL JSON pass expression.  You see that pass expression can be very complex. And we save the parsed in binary format that  we save a lot of instruction in CPU. And also, we got, if we have a binary representation,  we can use indexes. You can use indexing. And we can use existing indexes for JSON  B. Because we introduced some operators for JSON B and JSON pass. And they are very useful.  You will see, I will show you. And that's why we have not just a string literal, because  as a standard, but separate data type. Also, we introduced several functions to query and  to check for JSON pass. It's JSON B pass exists. It's a Boolean. We just check the pass expressions  if it returned any items. We have a coding operator. JSON B pass match. Query, query  array, and query first. So this query is what we have now. Functions, they have the same  signature, all functions. The signature, the first argument is the first parameter is a  JSON B. Second is JSON pass. The third is vars. It's a JSON B object. Remember that  you can pass variables to the JSON pass. Here's how you can specify. And one Boolean flag,  silent. So the silent is responsible for the error messages. Because sometimes you need  error message. Sometimes you don't need. People, usually if you work with a JSON, it means  that you don't need any errors. So just silent. True. But by default, we have silent false.  Because we are in Postgres, we like some very strict, you know. Yeah. Here's the example  how vars object used for passing JSON pass variables. So we have dollar X, dollar X variable,  and it comes like this. This is a JSON B object. You can specify any complex JSON B expression.  I said about silent. Here's the example how it's used. Yes. For example, the first query,  you get error. Because this is a strict mode. This is strict mode. In strict mode. And when  you try to access a non-existed key, you get error. Remember not found. But if you specify  silent true, then you get just no silent result, silent zero. Here's examples of how to use  JSON pass functions. They are, I would say, very simple. So for example, you understand  JSON pass query. We have JSON, JSON B. Yes. This way, we get array accessor. So we just  iterate through all this array element and check if they are greater than two. So results  is very clear. Three, four, five. Just very simple. Another example for the other examples,  maybe here's a Boolean operators for exist operators, match operator. They are actually  interchanged. You can use match instead of exist, for example, or exist instead of match.  And some examples, the same floor, the same house, some examples of queries. This we try  to get all apartments from one to last, this query. So all this green, we want to get from  this JSON. In PG 11, currently, you have to use like this query. You see? You have to  use subselect. With the PG 12, you use just this. So actually, we can live without JSON  pass. It will be just, it looks very ugly. And actually, it's just simple example. For  some more complex example, it will be very complex query. And the nice thing about JSON  pass that JSON pass actually faster than this query. I will show this. Here is another query.  You get all apartment with area in the greater than 40 and less than 90. So again, how we  choose all these green elements. In PG 12, you can use already known JSON pass expression  like this. And in PG 11, again, you have to use like some subselect. So here's a, add  sign is a room object. It pass from the, from the, from the pass expression. You see? First,  we have a dollar. It's a full JSON. From dollar, we get a floor. Floors array. It's a two floors.  And for each floor, we get all apartments from here. And then we check for the area. So it  looks not very complex. Then we made extension. The only extension to JSON pass we implemented.  We introduced recursive wild card. So for example, this query tried to look for how to get the  Moscow without specifying which level where just need like a find in JSON Moscow string.  With PG 12, it is very easy. You see, this is two queries. The same. So we just specify  wild card nesting and the test in the filter for string matching Moscow. In JS query,  also you can, you can write this already. No. In PG 11, you have to write again. Long,  long, long. You should navigate through the old structure of JSON and check. So JSON pass  is very good in POSGRIS as we see implemented. But one method we did not support. It's a  daytime. As for JSON, they introduced daytime. And this daytime is not type supported by  JSON. You know that JSON actually has no daytime support. People complained about this.  But SQL JSON introduced just a method which used to, for comparison. So you still store  on disk like a string. It's not a special data type. And the reason why we did not introduce,  why we did not implement daytime is because POSGRIS, in POSGRIS, daytime is not immutable.  You all know that it depends on the time zone, current time zone. So if we introduce daytime  into the JSON pass data type, then the whole data type will be non-immutable. So you can't  use index for this. And this problem is a bit complex because it touched some hidden  parts of POSGRIS. We were not ready to work. So we decided, okay, let's, we can live without  daytime, without daytime. But we have indexes. And actually, we have several, we have proposal  how to implement immutable daytime, how to support daytime. And we published and commit  first our patch. But we hoped to work it for the next version. So currently, we have this  error, bad JSON pass representation. But SQL standards should work. Okay. So what we,  we checked the SQL JSON standard conformance. And we, SQL standards specify 15 features.  And we support 14. As I said, because of daytime, we just did not support all. So we have 14  out of 15. Oracle 11, MySQL 5, and SQL service 2. So POSGRIS 12 has the best implementation  of JSON pass. How we check, we just write the scripts and check the actual database  server how this works. So more information about JSON pass, you can find our gentle guide  because when we wrote the documentation for the PG12, our documentation is just reference  manual. And very difficult to get the whole picture. Because in our documentation, you  have some parts described here, some parts are described there, and very difficult. So  I decided to write a separate gentle guide. And you can read, try examples and so on.  Indexing. Indexing is very important for performance, you know. Here is an explanation  of how indexes work for JSON B. So we have a simple JSON B with K value, KV, and so on.  And we have JSON B operations. It's a default GIN op class. When you, when you say create  index using GIN, this op class will work. And it supports all operators, all operators.  But it's a bit slow and a bit big, and big, because we index all key values separate,  and then we have to overlap them. Yes. There is a much faster and much smaller operator  JSON B hash ops, where we index hash of K value, K attached value. Of course, we doesn't,  we support a container operator, but doesn't support actually exist operators. Yes. So  if you don't need exist operator, this is recommended. JSON B hash operator. This is  much faster and faster, and smaller than default. We have also three, three more op classes.  In JS query extension, we have JSON B path value operations, which is hash, a full path  with the value attached. So what does it mean? It means that this op class support comparison  operation for values, exact and range queries, also exact path searches. For example, if you  see the rooms whose area is greater than 40, this op class will support, because you will  have value attached to the path. One specific to JSON path operation is a JSON B lax path  value operations. It's the same as the first one. In JS query, we just made some optimization  for lax mode of JSON path. And the last one is JSON B value path operations. Here's a  value attached to the bloom, to the concatenation of all bloom, bloom filters. So it supports  exact value search. So and wildcards past queries. So if you have, if you understand  what you want to speed up, you can choose what you need. Actually, unfortunately, we  can't make the single op class, which supports all everything. There is no silver bullet.  JSON path queries could use existing JSON B indexes, and we verify it. So we have a  JSON B index for bookmarks table. It's a delicious bookmarks. And we created using  JSON B path ops. And we run query with JSON path. And we have for sequential scan, we  have 35 seconds. And with index scan, we have just six milliseconds. So existing means that  index already exists on the, on the, on the disk. So we did not introduce do something  new. But JSON path supported already. Let's check the performance of JSON path. For simple  queries, we use a test table with three million rows generated like this query. Actually,  just three million of the same, same row. Three million. And we check the queries using  JSON path, many, many variations. Yeah. And old way using error operators. Well, as now  in PG 11, you can use error operator. And we see that error, error operator actually  faster because it's simple query. And the podcast executor would optimize this for simple  query. Yes, but JSON path remind you supports very complex query. And the next example is  example how performance for the complex query we took a INDB international movie database,  convert to the JSON. And the JSON looks like here, here's a roles, arrays of roles, actors,  title and so on. Yeah. And we try to find all actors and editors in the same movie. So  actor should, we are looking for the, and the size is more than six millions actors,  names. Yes. And interesting that more than 40,000 people were actors and editors. We have  every JSON path. We got several with sequential scan, sequential scan parallel. You see that  parallel works very well. And we index count just query index we use. It's two and three  seconds. Old way is a bit slower, someone one second slower. So for the complex queries,  JSON path is better because we use JSON path use internal executor instead of postgres  executor, which has a lot of overhead for calling functions and so on. If you use error operators,  subselects. Also we check the relational way. So we took INDB database in relational format.  Yes. And run queries. And we see that relational way also. It's a different, you know, different  sequential scan with parallel scan, bitmap index, index on parallel. And the best results  is the same as a error operator for JSON. And we took a Munga, the last version of Munga.  And we see that performance also the same as relational and error operator. But all these  three are slower than JSON path. So the roadmap. The roadmap we have in the next version, we  want to complete SQL JSON implementation. Yes, we fixed the problem with datetime. We  want to use planner support functions for JSON path. If you don't know that, what is  it? It is a very useful feature of postgres, which was committed by Tom for Pg12. And we  actually have already implementation just after feature freeze. So it helps you to, for  JSON path very well. We want to implement, not implement, everything already implemented.  We want to commit. We need just resources to commit and people, I ask people for reviewing  and so on. So we have already op classes for, for parameters for op classes. Very important.  You can imagine that you have very big JSON path, JSON B table. And you want to create  index. Currently you index everything. You know. But with JSON path, you can just specify  which part of the index. So your index will be smaller, faster, and that could be happens  if we add support for parameters to op class. You know that in postgres we have many, not  many, but we have several parameters hard coded into the op classes. For example, for  signature. The length of signature is hard coded, for example. Or blue filter. Or, or  green. We have some hard coded constants there. It would be much flexible if you could, could  specify parameters for the op class. Because if you index small strings, you don't need  two kilobytes bit string. You just say that eight kilobytes is okay. So parameters for  op class are very good, not only for JSON path, but overall, overall postgres. Yes. We want  to move some genome classes to core. Because we don't want people install JS query just  to have some additional op classes. Yes. One is extend JSON path syntax. We have contribute  JSON path X, which contains implementation for arrays, objects, sequence constructions,  object subscripting. We have lambda expressions. And this is what we interesting its user defined  methods and functions. We know that progress is well known for its extensibility. So why  not to use in JSON some user defined methods and operators? So this is what we also need.  Very interesting project is a copy with JSON, with support of JSON path. You know that now  data in the internet distributed in the JSON. And this JSON, it's looked like big array  of JSON. It's paying to import to the postgres. But if we copy, it has support of JSON path,  you can just say what you want to import. Very flexible. This is our roadmap. And if  you're interested in more, we have several addendum and all these items explained on  separate slides. Yes. So we have good roadmap, no SQL policy, okay. So we don't need Munga.  And the summary that we already have good no SQL database. And SQL JSON provides flexibility  and interoperability. So the same query should run on Oracle, on postgres and other databases  who supports SQL standard. And JSON path is very good for complex queries. Okay. This  summary. These are reference all links to the additional materials. So this talks, you  can find my talk slides. Very useful technical report for SQL JSON. It is free. You can download  and read human-oriented specification of SQL JSON. Because SQL standard is not for us.  Yes. So this is okay. Yeah. My gentle introduction to JSON path, JS query and presentation about  JS query. So if you are lazy, you can just have a play with JSON path online. We have  SQL Fiddly with the latest master and JSON path inside. So you can play just from your  laptop or smartphone. We have a presentation about parameters for op class. And if you  want to play with international movie database in JSON format, you can download it. So very  interesting. You can get very interesting statistics about movies, actors and so on.  Of course, here is a people who was working on all SQL progress. And this, I said that  no SQL progress is an example of how our community works. So it's not just me or just my colleagues.  There are many people from our community, from different companies. Several companies  supported all this project for many years. And I want to say that thanks to all these  people who invest their time, their resources to this very interesting project. Yes. Some  of them are here. I see that people. Yes. The last slide is that all you need is posgres.  Yes. Thank you. Yes. So the questions actually I have a lot of, an example of SQL JSON, how  it looks at JSON table. What you expect in the next version? You see, this JSON table  functions provides you ability to convert JSON to the relational. It's a very powerful,  it's like XML table. So using JSON table, you can join to the relational data. Yeah,  very powerful. This example, you have a parameter for up classes. We have planner support for  JSON pass. How it looks like. We have syntax expression. For example, if you want to get  array construction, just use arrays. Object sequence construction or subscripting array  lambda, who wants lambda, map reduce fold. Very flexible. This is already implemented.  We have extension. Yes. But we want to maybe contribute to core. Yes. Sequence with lambda  expressions, user defined methods and functions. So you can just implement your performance  of intrad joined. This example, if you want to play with MongoDB. So this is the best.  We check maybe five different methods to run query. And this is example how it looks on  Mongo. Looks, no. And you remember how it looks on with JSON pass. Very elegant query.  But with MongoDB, you have to write like this. It's a map, of course, with mapping. Yes. Here  is the example of relational. How to do relational? You see that with relational database, it  becomes much bigger. Here is the JSON pass. With JSON B, here is a relational. We understand  because relational contains much more roles. Because one actor could be participating in  many, many roles. Yes. This is example of query on relational. Yes. This is how you can play  with two floors. So you download slides. You study the slides, play. And you become, understand  how JSON pass works. Thank you again. If you have questions, I am available all day. We  can talk. Yes. Slides already available. You can download. So the main goal is, you know,  it's very difficult to explain such a complex thing in 40 minutes. You just need to, that  you are interested in. Yes.  So you go to the all nestings, all levels, all levels, recursive. Yes. Star-star is the  theme, yes.
Viva_the_NoSQL_Postgres_-_Alexande_Korotkov_0_47.png Viva_the_NoSQL_Postgres_-_Alexande_Korotkov_48_131.png Viva_the_NoSQL_Postgres_-_Alexande_Korotkov_132_1235.png Viva_the_NoSQL_Postgres_-_Alexande_Korotkov_1236_3347.png Viva_the_NoSQL_Postgres_-_Alexande_Korotkov_3348_3419.png Viva_the_NoSQL_Postgres_-_Alexande_Korotkov_3420_3491.png Viva_the_NoSQL_Postgres_-_Alexande_Korotkov_3492_5891.png Viva_the_NoSQL_Postgres_-_Alexande_Korotkov_5892_5975.png Viva_the_NoSQL_Postgres_-_Alexande_Korotkov_5976_7679.png Viva_the_NoSQL_Postgres_-_Alexande_Korotkov_7680_7955.png Viva_the_NoSQL_Postgres_-_Alexande_Korotkov_7956_9707.png Viva_the_NoSQL_Postgres_-_Alexande_Korotkov_9708_11183.png Viva_the_NoSQL_Postgres_-_Alexande_Korotkov_11184_13295.png Viva_the_NoSQL_Postgres_-_Alexande_Korotkov_13296_17219.png Viva_the_NoSQL_Postgres_-_Alexande_Korotkov_17220_18011.png Viva_the_NoSQL_Postgres_-_Alexande_Korotkov_18012_20111.png Viva_the_NoSQL_Postgres_-_Alexande_Korotkov_20112_20291.png Viva_the_NoSQL_Postgres_-_Alexande_Korotkov_20292_22235.png Viva_the_NoSQL_Postgres_-_Alexande_Korotkov_22236_23591.png Viva_the_NoSQL_Postgres_-_Alexande_Korotkov_23592_24995.png Viva_the_NoSQL_Postgres_-_Alexande_Korotkov_24996_25895.png Viva_the_NoSQL_Postgres_-_Alexande_Korotkov_25896_26495.png Viva_the_NoSQL_Postgres_-_Alexande_Korotkov_26496_28103.png Viva_the_NoSQL_Postgres_-_Alexande_Korotkov_28104_28847.png Viva_the_NoSQL_Postgres_-_Alexande_Korotkov_28848_29075.png Viva_the_NoSQL_Postgres_-_Alexande_Korotkov_29076_30083.png Viva_the_NoSQL_Postgres_-_Alexande_Korotkov_30084_31835.png Viva_the_NoSQL_Postgres_-_Alexande_Korotkov_31836_32579.png Viva_the_NoSQL_Postgres_-_Alexande_Korotkov_32580_34559.png Viva_the_NoSQL_Postgres_-_Alexande_Korotkov_34560_35267.png Viva_the_NoSQL_Postgres_-_Alexande_Korotkov_35268_36239.png Viva_the_NoSQL_Postgres_-_Alexande_Korotkov_36240_36359.png Viva_the_NoSQL_Postgres_-_Alexande_Korotkov_36360_36803.png Viva_the_NoSQL_Postgres_-_Alexande_Korotkov_36804_37391.png Viva_the_NoSQL_Postgres_-_Alexande_Korotkov_37392_38615.png Viva_the_NoSQL_Postgres_-_Alexande_Korotkov_38616_40487.png Viva_the_NoSQL_Postgres_-_Alexande_Korotkov_40488_41207.png Viva_the_NoSQL_Postgres_-_Alexande_Korotkov_41208_41807.png Viva_the_NoSQL_Postgres_-_Alexande_Korotkov_41808_42143.png Viva_the_NoSQL_Postgres_-_Alexande_Korotkov_42144_42191.png Viva_the_NoSQL_Postgres_-_Alexande_Korotkov_42192_45131.png Viva_the_NoSQL_Postgres_-_Alexande_Korotkov_45132_46151.png Viva_the_NoSQL_Postgres_-_Alexande_Korotkov_46152_47075.png Viva_the_NoSQL_Postgres_-_Alexande_Korotkov_47076_51503.png Viva_the_NoSQL_Postgres_-_Alexande_Korotkov_51504_52667.png Viva_the_NoSQL_Postgres_-_Alexande_Korotkov_52668_53135.png Viva_the_NoSQL_Postgres_-_Alexande_Korotkov_53136_53999.png Viva_the_NoSQL_Postgres_-_Alexande_Korotkov_54000_56459.png Viva_the_NoSQL_Postgres_-_Alexande_Korotkov_56460_57455.png Viva_the_NoSQL_Postgres_-_Alexande_Korotkov_57456_63275.png Viva_the_NoSQL_Postgres_-_Alexande_Korotkov_63276_63491.png Viva_the_NoSQL_Postgres_-_Alexande_Korotkov_63492_64127.png Viva_the_NoSQL_Postgres_-_Alexande_Korotkov_64128_65891.png Viva_the_NoSQL_Postgres_-_Alexande_Korotkov_65892_67175.png Viva_the_NoSQL_Postgres_-_Alexande_Korotkov_67176_67415.png Viva_the_NoSQL_Postgres_-_Alexande_Korotkov_67416_67499.png Viva_the_NoSQL_Postgres_-_Alexande_Korotkov_67500_67547.png Viva_the_NoSQL_Postgres_-_Alexande_Korotkov_67548_68123.png Viva_the_NoSQL_Postgres_-_Alexande_Korotkov_68124_68171.png Viva_the_NoSQL_Postgres_-_Alexande_Korotkov_68172_68231.png Viva_the_NoSQL_Postgres_-_Alexande_Korotkov_68232_68291.png Viva_the_NoSQL_Postgres_-_Alexande_Korotkov_68292_68339.png Viva_the_NoSQL_Postgres_-_Alexande_Korotkov_68340_68579.png Viva_the_NoSQL_Postgres_-_Alexande_Korotkov_68580_69011.png Viva_the_NoSQL_Postgres_-_Alexande_Korotkov_69012_69107.png Viva_the_NoSQL_Postgres_-_Alexande_Korotkov_69108_69275.png Viva_the_NoSQL_Postgres_-_Alexande_Korotkov_69276_69323.png Viva_the_NoSQL_Postgres_-_Alexande_Korotkov_69324_70115.png Viva_the_NoSQL_Postgres_-_Alexande_Korotkov_70116_70211.png Viva_the_NoSQL_Postgres_-_Alexande_Korotkov_70212_71039.png Viva_the_NoSQL_Postgres_-_Alexande_Korotkov_71040_72863.png
I am Ali Egbertunov. I work for the Moscow University and the PostgreSQL Professional Company and today I will talk about the Moscow PostgreSQL, particularly about JSON Pass, which is a query language for JSON. Here is a... Yes? Yes, sorry. I forgot. Okay, it's okay? Okay. Today I will talk about NoSQL PostgreSQL and this slide is just a very short history of NoSQL features in PostgreSQL. As you see on this picture, this evolution of NoSQL databases, so they started from very primitive to very complex NoSQL database and that time, on vertical, PostgreSQL also was evolved from the HStore data type, which implemented 2003. It's 16 years ago, PostgreSQL already had some NoSQL features and in 2012 we got a JSON data type, which is... PostgreSQL was the first relational database who implemented JSON. In 2012, in 2014 we implemented JSON B, which is a binary storage for JSON. It supports nesting objects arrays and what was important, it was practical because it has index support. So people were very excited and start using JSON B and it's very popular. Today I will talk about JSON Pass. 2019, it is a part of SQL JSON 2016 standard and it has a lot of function operators and also support indexing. I hope next year I will talk about the full SQL JSON implementation. You see that this is a very brief NoSQL PostgreSQL in short and the state of art of JSON in PostgreSQL is that we have two data types actually. We have JSON and JSON B and this select shows all the different of the features of two data types. You see that left is a JSON, this is a textual storage as is and the right is a JSON B. So you will see the difference and JSON B is a binary storage. We don't need to parse, it has index support and it doesn't preserve white spaces and no duplicative case. So you see that last key win and JSON B has a key sorted first by length and then by key name. It has a lot of functions. If you have P-SQL you can just see the backslash df JSON B star. You see a lot of functions. So we have a very flexible functional interface and what's nice is it has a great performance because of indexes and performance of JSON B and many people verified and tested and said that it's the same level as MongoDB so people start using PostgreSQL instead of Mongo. Why use Mongo if we have good universal database with the same JSON support. Also we have a JS query. JS query, we don't know this is extension which provides JSON query language and also several index op classes for JSON. It's not part of the PostgreSQL distribution but it's very useful and I will show some of features. Okay, so JSON B is cool but it is a black box for SQL. So from SQL you have no idea how to operate with the structure of JSON. For example, this very simple case. You have some database, some table with red color and you just want to find all these rows without specifying exact nesting. So with SQL you have to do like this query with recursive. We specially designed JS query to do queries like this. It's very simple. So we developed it in 2014. It's maybe two years before SQL standard specified JSON pass. So you see that with SQL actually it is very difficult to work with JSON B, with JSON. So JSON pass is really interesting feature and SQL standard in 2016, in the fall of 2016 introduced JSON support specification. So why it happens? This is one marketing slide. It's because PostgreSQL evolution, because PostgreSQL embrace the relational database and no SQL users start, was attracted by no SQL postgres features. And this is a data from DB engines. I just download data from DB engines and made a relative growth of popularity Postgres, MySQL, Oracle, MS SQL. And you see that clearly you see that popularity of PostgreSQL from 2014 when we released 9.4 with the JSON B support. So this popularity growth is because of, particularly because of we got JSON B. So we, our community attract more users. And that's why it is very important to us to continue developing no SQL features in PostgreSQL. And new development is part of this project of no SQL postgres. So this screenshot of the actual SQL 2016 documentation and you see that there is a JSON data model, JSON functions and JSON pass language. What's actually specified in SQL JSON standard? So actually it doesn't specify the data type. It specifies the data model and string storage. So it's just a string where you store your JSON. You store a, it's a sequence of items of SQL values, no SQL JSON. It's not SQL null. It is JSON null. And JSON arrays and JSON objects. I also standard specified JSON pass language and a bunch of SQL JSON functions, construction functions and query functions. In PostgreSQL we already have JSON B. And JSON B is a practical subset of a SQL JSON data model. If you specify order it and unique keys. So we don't need to introduce new data type. We already have two. We don't need a third data type. So because JSON B is a practical, people use it, why more? Also JSON pass language is very important part. So we decided to implement it for PostgreSQL because it's the most important part. Because SQL JSON function, we already have constructed a function. JSON B construction function, it's the same. It looks like the same as the standard. Query functions, yes, we need some functions with JSON pass support. This is what we also implemented. Indexes. The challenge was that try to use already existing indexes. Building and just query. What do you mean? It means that if you have indexes already for JSON B, that indexes should support JSON pass also. And this is what we did. And also add some more specific to JSON pass of classes. So this was our plan for implementation of Pg12. So what is a JSON pass? It's an expression which specifies which part of JSON you interested. So you know that JSON actually, basically it's a tree. And with this language, you can specify which part of tree you interested to do. For example, because you know that JSON, for example, what to index, what to select. And it has some optional mode like strict and lax. Here is an example of how JSON pass looks like. First and first people said that it looks like not very interesting, not how to say, don't understand actually. Lax, dollar sign, dot, we use this dot notation. It's specified by SQL standard. And this is a accessor, a member accessor, a reaccessor. And after the dollar sign, question sign, we have in parenthesis, we have a filter. But if you make analogy with SQL, SQL is a language for relations. JSON pass is a language for the JSON, for the JSON elements. So it's very easy. You start from the beginning and made operator to the SQL JSON items. And output also SQL JSON items. So as in SQL, you can make the chain SQL operators. In the JSON pass, you also have chains just connected by dot. And the filter in the parenthesis is just as a SQL where it's filtered. So actually, JSON pass very easy to understand. So dollar is the current context element. It's a JSON, the whole JSON. And this is a reaccess. And the rest starts from zero as a JSON filters. We have a add sign, add sign we use in filter. This is a current context in filter expression. And we have item method. So this is just a function attached to the JSON pass. So you can apply, it's a predefined set of eight functions you can use for JSON pass. So actually, what this is, JSON pass looks like. And next, I will illustrate some example. Yes. Yes, I will show you. So to play with, to demonstrate the JSON pass, I will use the example of two floor house. So the house with two floors, it has rooms. Rooms has some metadata like area, room number, and so on. So this is a function. And for example, how JSON pass works, this JSON pass, we start from dollar sign. Dollar means that all green is dollar on the first step. Second step, we have a member access dot floor. So we choose floors object. And third step, we choose, we have an array accessor, array accessor. So we choose arrays. So floor one, zero floor one. Next, we have a member accessor for apartment. So we've got five apartments, five apartments. After applying array accessor, we got actually apartments. And finally, finally, we apply filter. It's like where? We filter all this apartment for the area, area of rooms. And we got just two items from all this. So this is how JSON pass works. This is a very simple example because you can continue, you can attach the more path elements, more filters to the JSON pass and do everything. What do you want? This is steps I just shown you. And Lax and strict is a mode, is a mode for JSON pass, which try to facilitate matching of layer slope document structure and pass expression. Because you know that JSON is a data type which has, there is no fixed schema. So sometimes keys may be missed. So what to do with Lax and strict? They manage what to do if you don't have, for example, key. So in Lax mode, Lax is a relaxed mode. Missing keys just ignored. You see this. JSON B, this operator is a, exists operator. JSON B and JSON pass. So in Lax mode, it just produce false and no error and action because you see that B key missed in the JSON. And filter just return false. In the strict mode, missing keys result at null. So strict mode requires more, I would say, more exact matching. The next example will illustrate this. For example, in Lax mode, arrays are unwrapped. You see that we have a nested array and we are looking for the member equals five. And in Lax mode, all these two variants are the same. So you don't need to specify a wild card array accessor. Because in Lax mode, arrays just unwrapped, but only one level. In the strict mode, you require exact nesting. So you have to specify array accessor to get the true, to match the JSON B. This is very important. Here are the examples of valid JSON pass expressions. So dollar sign, the whole JSON document, dollar foo, you can specify variable. And variable, you can pass to the JSON pass, actually, from outside. So you can string literal or numeric literal, true, null. You can specify member accessor. Member accessor looks like key. You can specify array accessor. You can use a complex pass with filters and variables. For example, you see that we have here two filters. And inside filter, we use variables. Variables come from outside. Yes. And you attach the member. So this is a complex pass expression. Or you can specify arithmetic expressions. It's very cool, actually. I always miss some possibility in posities to do something with the array, with all members of array. Here, you can just specify this. So you change sign of all members of array A. Or you add free to the any member of the array, to all members. Or even specify the complex expressions with variables. So this is what you can do now with the content of JSON. Here are a lot. Another example, you can specify item methods. So you have arithmetic expressions. And then you specify, you get the absolute value and then sale. And if you made a syntactical error, so you get error message. What is the filter expression? As I said, this is just as a way of closing SQL. So you have on input SQL JSON sequence. Sequence is an ordered list of SQL JSON items. And then you check if they satisfy the predicate. So the syntax, you see, it's a question of science. And in parenthesis, you can use some expression. Inside expression, you can use add sign. It is a reference to the current SQL JSON item to process. And predicate. We have several predicates you can use in filter expression. Exist comparison. Like regress, it's like posive regress. You can use even flex also. Start with and is unknown. So this predicate you can use in filters. Here's an example. All arithmetic errors in filters suppressed. So, for example, in this JSON pass, in this filter, you see the division to the context item. And some, here is a zero. So you can, it just ignored. So you get just one. I think this is very clear. First time it looks not like a human language. Yes. But after playing, you understand all this. JSON pass has a predefined method which attach to the JSON pass expression. So, for example, here we got key value pairs. We just use the key value method. And we get ID key in value. And we can combine methods. For example, attach to the key value, the next key method. And we get just key names. As I said, this is a predefined. We have eight methods we can use. So this was about standard. But what's about posive? We implemented JSON pass as a data type, JSON pass. Because it is a binary representation of parsed SQL JSON pass expression. You see that pass expression can be very complex. And we save the parsed in binary format that we save a lot of instruction in CPU. And also, we got, if we have a binary representation, we can use indexes. You can use indexing. And we can use existing indexes for JSON B. Because we introduced some operators for JSON B and JSON pass. And they are very useful. You will see, I will show you. And that's why we have not just a string literal, because as a standard, but separate data type. Also, we introduced several functions to query and to check for JSON pass. It's JSON B pass exists. It's a Boolean. We just check the pass expressions if it returned any items. We have a coding operator. JSON B pass match. Query, query array, and query first. So this query is what we have now. Functions, they have the same signature, all functions. The signature, the first argument is the first parameter is a JSON B. Second is JSON pass. The third is vars. It's a JSON B object. Remember that you can pass variables to the JSON pass. Here's how you can specify. And one Boolean flag, silent. So the silent is responsible for the error messages. Because sometimes you need error message. Sometimes you don't need. People, usually if you work with a JSON, it means that you don't need any errors. So just silent. True. But by default, we have silent false. Because we are in Postgres, we like some very strict, you know. Yeah. Here's the example how vars object used for passing JSON pass variables. So we have dollar X, dollar X variable, and it comes like this. This is a JSON B object. You can specify any complex JSON B expression. I said about silent. Here's the example how it's used. Yes. For example, the first query, you get error. Because this is a strict mode. This is strict mode. In strict mode. And when you try to access a non-existed key, you get error. Remember not found. But if you specify silent true, then you get just no silent result, silent zero. Here's examples of how to use JSON pass functions. They are, I would say, very simple. So for example, you understand JSON pass query. We have JSON, JSON B. Yes. This way, we get array accessor. So we just iterate through all this array element and check if they are greater than two. So results is very clear. Three, four, five. Just very simple. Another example for the other examples, maybe here's a Boolean operators for exist operators, match operator. They are actually interchanged. You can use match instead of exist, for example, or exist instead of match. And some examples, the same floor, the same house, some examples of queries. This we try to get all apartments from one to last, this query. So all this green, we want to get from this JSON. In PG 11, currently, you have to use like this query. You see? You have to use subselect. With the PG 12, you use just this. So actually, we can live without JSON pass. It will be just, it looks very ugly. And actually, it's just simple example. For some more complex example, it will be very complex query. And the nice thing about JSON pass that JSON pass actually faster than this query. I will show this. Here is another query. You get all apartment with area in the greater than 40 and less than 90. So again, how we choose all these green elements. In PG 12, you can use already known JSON pass expression like this. And in PG 11, again, you have to use like some subselect. So here's a, add sign is a room object. It pass from the, from the, from the pass expression. You see? First, we have a dollar. It's a full JSON. From dollar, we get a floor. Floors array. It's a two floors. And for each floor, we get all apartments from here. And then we check for the area. So it looks not very complex. Then we made extension. The only extension to JSON pass we implemented. We introduced recursive wild card. So for example, this query tried to look for how to get the Moscow without specifying which level where just need like a find in JSON Moscow string. With PG 12, it is very easy. You see, this is two queries. The same. So we just specify wild card nesting and the test in the filter for string matching Moscow. In JS query, also you can, you can write this already. No. In PG 11, you have to write again. Long, long, long. You should navigate through the old structure of JSON and check. So JSON pass is very good in POSGRIS as we see implemented. But one method we did not support. It's a daytime. As for JSON, they introduced daytime. And this daytime is not type supported by JSON. You know that JSON actually has no daytime support. People complained about this. But SQL JSON introduced just a method which used to, for comparison. So you still store on disk like a string. It's not a special data type. And the reason why we did not introduce, why we did not implement daytime is because POSGRIS, in POSGRIS, daytime is not immutable. You all know that it depends on the time zone, current time zone. So if we introduce daytime into the JSON pass data type, then the whole data type will be non-immutable. So you can't use index for this. And this problem is a bit complex because it touched some hidden parts of POSGRIS. We were not ready to work. So we decided, okay, let's, we can live without daytime, without daytime. But we have indexes. And actually, we have several, we have proposal how to implement immutable daytime, how to support daytime. And we published and commit first our patch. But we hoped to work it for the next version. So currently, we have this error, bad JSON pass representation. But SQL standards should work. Okay. So what we, we checked the SQL JSON standard conformance. And we, SQL standards specify 15 features. And we support 14. As I said, because of daytime, we just did not support all. So we have 14 out of 15. Oracle 11, MySQL 5, and SQL service 2. So POSGRIS 12 has the best implementation of JSON pass. How we check, we just write the scripts and check the actual database server how this works. So more information about JSON pass, you can find our gentle guide because when we wrote the documentation for the PG12, our documentation is just reference manual. And very difficult to get the whole picture. Because in our documentation, you have some parts described here, some parts are described there, and very difficult. So I decided to write a separate gentle guide. And you can read, try examples and so on. Indexing. Indexing is very important for performance, you know. Here is an explanation of how indexes work for JSON B. So we have a simple JSON B with K value, KV, and so on. And we have JSON B operations. It's a default GIN op class. When you, when you say create index using GIN, this op class will work. And it supports all operators, all operators. But it's a bit slow and a bit big, and big, because we index all key values separate, and then we have to overlap them. Yes. There is a much faster and much smaller operator JSON B hash ops, where we index hash of K value, K attached value. Of course, we doesn't, we support a container operator, but doesn't support actually exist operators. Yes. So if you don't need exist operator, this is recommended. JSON B hash operator. This is much faster and faster, and smaller than default. We have also three, three more op classes. In JS query extension, we have JSON B path value operations, which is hash, a full path with the value attached. So what does it mean? It means that this op class support comparison operation for values, exact and range queries, also exact path searches. For example, if you see the rooms whose area is greater than 40, this op class will support, because you will have value attached to the path. One specific to JSON path operation is a JSON B lax path value operations. It's the same as the first one. In JS query, we just made some optimization for lax mode of JSON path. And the last one is JSON B value path operations. Here's a value attached to the bloom, to the concatenation of all bloom, bloom filters. So it supports exact value search. So and wildcards past queries. So if you have, if you understand what you want to speed up, you can choose what you need. Actually, unfortunately, we can't make the single op class, which supports all everything. There is no silver bullet. JSON path queries could use existing JSON B indexes, and we verify it. So we have a JSON B index for bookmarks table. It's a delicious bookmarks. And we created using JSON B path ops. And we run query with JSON path. And we have for sequential scan, we have 35 seconds. And with index scan, we have just six milliseconds. So existing means that index already exists on the, on the, on the disk. So we did not introduce do something new. But JSON path supported already. Let's check the performance of JSON path. For simple queries, we use a test table with three million rows generated like this query. Actually, just three million of the same, same row. Three million. And we check the queries using JSON path, many, many variations. Yeah. And old way using error operators. Well, as now in PG 11, you can use error operator. And we see that error, error operator actually faster because it's simple query. And the podcast executor would optimize this for simple query. Yes, but JSON path remind you supports very complex query. And the next example is example how performance for the complex query we took a INDB international movie database, convert to the JSON. And the JSON looks like here, here's a roles, arrays of roles, actors, title and so on. Yeah. And we try to find all actors and editors in the same movie. So actor should, we are looking for the, and the size is more than six millions actors, names. Yes. And interesting that more than 40,000 people were actors and editors. We have every JSON path. We got several with sequential scan, sequential scan parallel. You see that parallel works very well. And we index count just query index we use. It's two and three seconds. Old way is a bit slower, someone one second slower. So for the complex queries, JSON path is better because we use JSON path use internal executor instead of postgres executor, which has a lot of overhead for calling functions and so on. If you use error operators, subselects. Also we check the relational way. So we took INDB database in relational format. Yes. And run queries. And we see that relational way also. It's a different, you know, different sequential scan with parallel scan, bitmap index, index on parallel. And the best results is the same as a error operator for JSON. And we took a Munga, the last version of Munga. And we see that performance also the same as relational and error operator. But all these three are slower than JSON path. So the roadmap. The roadmap we have in the next version, we want to complete SQL JSON implementation. Yes, we fixed the problem with datetime. We want to use planner support functions for JSON path. If you don't know that, what is it? It is a very useful feature of postgres, which was committed by Tom for Pg12. And we actually have already implementation just after feature freeze. So it helps you to, for JSON path very well. We want to implement, not implement, everything already implemented. We want to commit. We need just resources to commit and people, I ask people for reviewing and so on. So we have already op classes for, for parameters for op classes. Very important. You can imagine that you have very big JSON path, JSON B table. And you want to create index. Currently you index everything. You know. But with JSON path, you can just specify which part of the index. So your index will be smaller, faster, and that could be happens if we add support for parameters to op class. You know that in postgres we have many, not many, but we have several parameters hard coded into the op classes. For example, for signature. The length of signature is hard coded, for example. Or blue filter. Or, or green. We have some hard coded constants there. It would be much flexible if you could, could specify parameters for the op class. Because if you index small strings, you don't need two kilobytes bit string. You just say that eight kilobytes is okay. So parameters for op class are very good, not only for JSON path, but overall, overall postgres. Yes. We want to move some genome classes to core. Because we don't want people install JS query just to have some additional op classes. Yes. One is extend JSON path syntax. We have contribute JSON path X, which contains implementation for arrays, objects, sequence constructions, object subscripting. We have lambda expressions. And this is what we interesting its user defined methods and functions. We know that progress is well known for its extensibility. So why not to use in JSON some user defined methods and operators? So this is what we also need. Very interesting project is a copy with JSON, with support of JSON path. You know that now data in the internet distributed in the JSON. And this JSON, it's looked like big array of JSON. It's paying to import to the postgres. But if we copy, it has support of JSON path, you can just say what you want to import. Very flexible. This is our roadmap. And if you're interested in more, we have several addendum and all these items explained on separate slides. Yes. So we have good roadmap, no SQL policy, okay. So we don't need Munga. And the summary that we already have good no SQL database. And SQL JSON provides flexibility and interoperability. So the same query should run on Oracle, on postgres and other databases who supports SQL standard. And JSON path is very good for complex queries. Okay. This summary. These are reference all links to the additional materials. So this talks, you can find my talk slides. Very useful technical report for SQL JSON. It is free. You can download and read human-oriented specification of SQL JSON. Because SQL standard is not for us. Yes. So this is okay. Yeah. My gentle introduction to JSON path, JS query and presentation about JS query. So if you are lazy, you can just have a play with JSON path online. We have SQL Fiddly with the latest master and JSON path inside. So you can play just from your laptop or smartphone. We have a presentation about parameters for op class. And if you want to play with international movie database in JSON format, you can download it. So very interesting. You can get very interesting statistics about movies, actors and so on. Of course, here is a people who was working on all SQL progress. And this, I said that no SQL progress is an example of how our community works. So it's not just me or just my colleagues. There are many people from our community, from different companies. Several companies supported all this project for many years. And I want to say that thanks to all these people who invest their time, their resources to this very interesting project. Yes. Some of them are here. I see that people. Yes. The last slide is that all you need is posgres. Yes. Thank you. Yes. So the questions actually I have a lot of, an example of SQL JSON, how it looks at JSON table. What you expect in the next version? You see, this JSON table functions provides you ability to convert JSON to the relational. It's a very powerful, it's like XML table. So using JSON table, you can join to the relational data. Yeah, very powerful. This example, you have a parameter for up classes. We have planner support for JSON pass. How it looks like. We have syntax expression. For example, if you want to get array construction, just use arrays. Object sequence construction or subscripting array lambda, who wants lambda, map reduce fold. Very flexible. This is already implemented. We have extension. Yes. But we want to maybe contribute to core. Yes. Sequence with lambda expressions, user defined methods and functions. So you can just implement your performance of intrad joined. This example, if you want to play with MongoDB. So this is the best. We check maybe five different methods to run query. And this is example how it looks on Mongo. Looks, no. And you remember how it looks on with JSON pass. Very elegant query. But with MongoDB, you have to write like this. It's a map, of course, with mapping. Yes. Here is the example of relational. How to do relational? You see that with relational database, it becomes much bigger. Here is the JSON pass. With JSON B, here is a relational. We understand because relational contains much more roles. Because one actor could be participating in many, many roles. Yes. This is example of query on relational. Yes. This is how you can play with two floors. So you download slides. You study the slides, play. And you become, understand how JSON pass works. Thank you again. If you have questions, I am available all day. We can talk. Yes. Slides already available. You can download. So the main goal is, you know, it's very difficult to explain such a complex thing in 40 minutes. You just need to, that you are interested in. Yes. So you go to the all nestings, all levels, all levels, recursive. Yes. Star-star is the theme, yes.
Today I will talk about NoSQL PostgreSQL and this PostgreSQL was the first relational database who JSON in PostgreSQL is that we have two data types feature and SQL standard in 2016, in the fall of released 9.4 with the JSON B support. So this and you see that there is a JSON data model, JSON specified JSON pass language and a bunch of SQL Indexes. The challenge was that try to use that JSON, for example, what to index, what to language for the JSON, for the JSON elements. So just two items from all this. So this is how JSON a mode for JSON pass, which try to facilitate JSON pass as a data type, JSON pass. Because it work with a JSON, it means that you don't need we have silent false. Because we are in Postgres, extension. The only extension to JSON pass we POSGRIS 12 has the best implementation of JSON helps you to, for JSON path very well. We want to human-oriented specification of SQL JSON. Because ability to convert JSON to the relational. It's a