Implementing a Cross-DB JSONField - Sage M. Abdullah

Speaker 1: Hello everyone, I hope you're having a good day wherever you are. This is a pre-recorded talk, which means that if you are watching this at the time of the conference, I will be in the chat room trying my best to answer your questions. during the talk. Now I hope you're prepared for this one because there's going to be a lot of code snippets as I think it's much easier to understand this topic through code snippets Before we get into this whole JSON field thing, let me just give you some background on myself. I'm Sage Abdullah. I'm a computer science undergraduate student at Universitas Indonesia. I'm currently writing my bachelor's thesis on JSON Field, so I can't wait to finish that and share it with you

Speaker 1: I've been a Django user since 2018 and I'm also a Django contributor. Last year I participated in the Google Summer of Code program with Django, during which I implemented the crash database JSON field that you can use starting from Django 3. 1, which was just released last month You can find me on GitHub and Twitter at Laymanage. So, JSON field. Oops, JSON field, what is it? Now if you're a Django user and you see the suffix field, You probably think that it's probably a model field or a form field, in which case you are right. But I'm gonna talk more about the model

Speaker 1: field here because that's where things get interesting But what exactly is JSON field? I'm just gonna quote the Django docs here because we all know that the docs are awesome. So it says that JSON field is a field for storing JSON encoded data. In Python, the data is represented as dictionaries, lists, and the basic types that we see in Python. Now it says JSON encoded data, but what does it mean? Before we get into that, I'd like to talk a bit about JSON. If you're a web developer, you've probably already used JSON before, but if you're not familiar with JSON It stands for JavaScript Object Notation

Speaker 1: and it's a key-value data format much like Python dictionaries. Now JSON encoded data is basically a string that contains valid JSON data, and this string is what we're gonna save into our database. When talking about databases in Django, we usually talk about the models. Now this model represents this table in our database. And the fields represent the columns. Now let's say I want to store the user's preferences or configuration in the database. such as a dark mode setting. Now to achieve this I would have to add another field to my model, generate the migration

Speaker 1: and run the migration. And then sometime in the future, if I want to add a font size configuration, I would have to do the same thing again. I would add a new field to the model, generate a new migration, and run the migration. And I always have to do that every time I add a new setting. Now this may be cumbersome if you keep adding more and more columns as you can see. here maybe we can do something to make our table simpler. Now if you see the last scene column it's a datetime field. Well you can see this as a regular string, it actually is more complex than that as the database knows that this part of the data

Speaker 1: is the date. and this part of the data is the time. Now maybe we can do the same thing for our config columns by merging all the columns and using a delimiter for the values But we lose the information of what these values represent. So we have to remember which part of the value is for what configuration? Plus we would have to write our own function that parses this value into separate values. Now wouldn't it be great if we can just just use JSON in our database, we can still have the information of the configuration name and it's also much

Speaker 1: more readable. However, it does have a downside. We repeat the keys for each row. Still, you don't have to create a new migration every time you want to add a new configuration. you just add them to the JSON data. So in the end, your database will look like this. We have seen how JSON data is stored in the database, but how does JSON feel like actually work. Now let's say I have replaced the configuration fields with one single JSON field. And then let's say we have this dictionary that contains the configuration values that we want to store in our database. So what we do is we create a new profile object with that dictionary

Speaker 1: as the value for our JSON field. And then sometime later, when we retrieve the object from the database and we access . config, we see that it's a dictionary and it's equal to the dictionary that we previously have and we can see here that it's really a dictionary so that means we can do dictionary operations such as updating the values like this. After that, if we want to save the data, we just call . save. on the object and later when we retrieve the object back from the database we can see that it has also changed in the database. But how does it work in the background? Now let's go back here. So from the dictionary

Speaker 1: we call objects. create with that dictionary as the convict value. Actually I'm gonna break this down into the instantiation of the profile object and then calling . save on the object. When we call. save, what happens in the background So what we want to do is we turn the dictionary into a JSON-encoded data, and then Django will compose an SQL query to store the data in the database like this. The config value is just a string that contains valid JSON data. And then later on, when we retrieve the object from the database, Django will issue an SQL query like this, and the database driver will return the JSON-encoded data.

Speaker 1: which is a string to Django. So we want Django to somehow convert this JSON encoded data into a Python dictionary. Now how do we do that? Well thankfully Python has its own JSON library that lets us encode Python objects into JSON and decode JSON into Python objects Now how it works is that we import JSON and then if we have a Python dictionary that we want to encode into JSON. we call json. dumps with that dictionary as the argument and then we would have the json encoded data as a string. And then if we have JSON encoded data, we can pass it into json.

Speaker 1: loads, which will give us the Python dictionary. And it is equal to the original dictionary that I have. And this does not only apply to dictionaries, it actually applies to any Python object that Can be encoded into JSON. Now what if I tell you that you can implement your own cross-database JSON field in less than 10 lines? Well, that happens to be the case. This is a cross-database JSON field in less than 10 lines, but it's a bit tight, so let's apply some physical distancing to the code. That's a bit much Okay, perfect. Now it's more than 10 lines, but it's still less than 15 lines

Speaker 1: and we have a nice dock string on top. So how it works is we create a subclass of text field because we are storing our JSON data as text. And then what we need to do is override the get prepValue method. This method is called by Django to convert the Python object into into the query value, which is used for queries, and by default the database value. Now the thing is none is reserved for the SQL null as defined in the DBAPI standard in Python. So we should not call JSON dumps on that and just leave it be so that we can still store SQL nulls

Speaker 1: And then for other values we just call json dumps. And then one other method that we need to override is the from db value. which is the method that gets called when Django retrieves the value from the database. And as none is reserved for SQL null, we should not touch that And for other values, we just call json. loads, which will decode the data into Python objects. Now if we want we can also add some extra functionality such as a custom encoder and decoder by adding a subclass of the JSON encoder and JSON decoder class and that class will be passed as the CLS argument

Speaker 1: when we are calling JSON dumps and JSON loads. And that is a cross-database JSON field that you can do in 15 lines. Alright, there's the thing about empty values. So the non-value is reserved for SQL null. If we are talking about string-based fields, we have two possible values for empty. Be none or the empty string. However, Django recommends using the empty string instead of nulls on string-based fields. Unless you need the field to be unique, which means that you can't have multiple objects that have the same empty string. However, since we are talking about JSON

Speaker 1: data, what about the JSON empty string? And the empty JSON object or the empty json array or even the value null, they are all valid JSON values, which means that you can store them in your JSON field as the top level value, these values will be encoded as JSON encoded strings. Now let's see some comparison. So in Python for empty strings you can either use single quotes or double quotes. But in JSON you can only use double quotes. And as we know, the data that is stored in the database is JSON encoded data. So So we basically just wrap the valid JSON

Speaker 1: values in strings like this. And the same also goes for the empty JSON objects and empty the JSON arrays, so we have no problem with that. Now what about the non-value? The JSON equivalent of the non-value would be the JSON null. And if we encode none in Python with JSON dumps, we will get the null string. And that is JSON encoded data. It contains valid JSON. However, as I previously said, the non-value is reserved for the SQL null, so we cannot use that. But what if we want to store JSON null as the top level value in our JSON field?

Speaker 1: That means we want to store the string null in our database. Well, what we can do is we wrap the null string as a value object. The value object means that It's a literal database value. So that means it does not need to be prepared for the database. Therefore, JSON dumps is not called. And that means we can store JSON null in our database. However, there's a problem with this. When we retrieve the value from the database, we call JSON loads. On the value other than none. The value from the database would be the string null, and that is not none So Django will call JSON loads on that value, which will give us none.

Speaker 1: And then when you save that none back into the database, Django will leave it as it is and it will be SQL. null so it's kind of tricky and for this case Django does not recommend you to use null values either JSON null or sql null And instead, Django recommends you to use a default in your JSON field, such as an empty dictionary, instead of null. But you should not use the literal anti -dictionary object because that will be shared across your model instances. because it's mutable. So what you would do is you pass a callable that returns a fresh object every time. A simple example would be the dictionary class

Speaker 1: which will return a fresh empty dictionary every time. This only happens if you want to store JSON null as the top level value of the JSON field. If you have JSON null in JSON objects or JSON arrays, well there's no problem with that. Previously we were storing the data as text, however, some of the database backends and supported by Django actually have native JSON data types and we would like to use that. Django supports PostgreSQL, SQLite , MySQL, MariaDB, and Oracle Database, but not all of them have native JSON data types In order to define the data types that will be used on each database backend, we can override the dbty type

Speaker 1: method. MySQL has a native JSON data type stored as binary. MariaDB which uses the same database back and as MySQL, however, stores JSON data as text, but it does have a JSON data type which is an alias for long text. Oracle database lets you store JSON data. in Farchar or LOB data types. Postgres has two JSON data types. The first is JSON and the second one is is JSON B. With JSON you store the data as text so when you store the data is a bit faster as it doesn't need to encode data into binary

Speaker 1: but for querying it can be a bit slower because decoding has to be done on the fly while with json b it's the other way around Data is usually more often queried than stored, so we use JSON B. SQLite does not have a JSON data. type but it does have JSON functions which we can use to query JSON data. Those functions are included in the JSON one extension. Note that this is not how the JSON field in Django 3. 1 was implemented. For the actual JSON field implementation, we define the data types in the database wrapper instead. of the JSON field class and we don't subclass text field

Speaker 1: and then we can add check constraints to ensure that the data inserted into the database is valid JSON. On MySQL, there's no need to add a check constraint because that already comes with the JSON data type. But for MariaDB prior to version 10. 4. 3 you have to add an explicit JSON-valid check constraint to ensure that the data is valid JSON. On later versions, if you use the JSON alias it will automatically apply the JSON valid constraint. On Oracle database you just have to add

Speaker 1: The isJSON keyword. Now on SQLite it's pretty similar but it's a bit different. The thing is, on MySQL and MariaDB, the JSON valid function returns true if you pass SQL null as the argument. However on SQLite it returns false. So in order to be able to store SQL null values, we would have to add this OR column S null clause to the check constraint. This is merely a different way of thinking because you can say that No data is valid JSON, but you can also say that it's not valid. The SQLite

Speaker 1: developers decide that no data is not valid JSON and for Postgres like MySQL the check already comes with the JSON data type So we just return none. We have explored about storing and retrieving JSON data to and from the database. And we have also explored how we can ensure that the data we insert. is valid JSON. There's one more thing about the ORM. It's a very important thing that we have an explorer and that is querying the JSON data. Let's say we have this JSON data and then we want to query the model objects that have the values sage at the path name in the JSON

Speaker 1: data. Well, looking at how transforms work on other fields, the natural way to do this would be something like this. So we just chain the JSON field. with double underscore and with the path that we want to check for and then the value would be on the right hand side and that is exactly how JSON field transforms work. However, the implementation is different on each database backend. For example, on Postgres You can use the arrow operator to extract the value at a given path and check for that value whether it's equal to something that you look for. On SQLite you can do the same thing using the function JSONEXTRACT.

Speaker 1: On MySQL and MariaDB, it's a bit different. because the JSON extract function will return this value with the double quotes still in it. So it's an SQL string that contains double quotes at the beginning and the end of the string. So we will need to call another function called json unquote which will unquote the value for us On Oracle database, the function is named differently. It's called JSON Valley, but it works pretty much the same way. But it can only be used for scalar values, so you cannot use json value for paths that return JSON objects or JSON arrays.

Speaker 1: have to use a different function called json query for that. Let's add some more complexity. Now we have additional information about pets. I have a pet cat. His name is Buggle. Well his actually is treat cat but he comes to my house every day so we treat him like a pet And then let's say we want to query for objects that have the first pad name of bubble. The natural way to do this would be to keep chaining the paths like this. So double underscore paths, double underscore zero for the index. of the object double underscore name for the path here. And it works the same way like the previous one, but on Postgres, instead of using the arrow operator, you replace the dash with hash.

Speaker 1: And then the right hand side of the extraction operator would be an array of keys or indexes. that compose the path that you want to extract. Whereas on the SQLite you just specify the path in the second argument. This is the JSON path notation. The dollar sign stands for the root of the JSON document and dot means that you access the key and accessing arrays works pretty much the same way as we know. No, there's still something about empty values. Let's say we have the following JSON object. And then we also have this JSON object. What would the following query return?

Speaker 1: What we do with this query is that we would extract the value at the path partner. Now if the object has the path partner even if it's null it should return some information to indicate that the path is there and the value is null. However, if the path is not there at all, then maybe it should return something. to indicate that the path is not even there. So that's exactly the case with the extract functions. When the path is not available, the function will return the SQL null However, for JSON null, the function would still return the SQL string null.

Speaker 1: So I think it would make sense that this query would return the second one because we don't normally use none as a lookup value. If we were querying for SQL nulls, we would use the istnal lookup and that's exactly the case here. So if we want to query for missing keys, we use the istnall lookup. The exact lookup with the non-right hand side value is used to query JSON node values. Now the thing is the JSON extract function behaves differently on SQLite. It returns SQL null when the path exists but the value is JSON null.

Speaker 1: And it also returns SQL null if the path is not there. So So there's no way to determine whether the path is there or not. However, there's one other function called JSONType that returns the type of the JSON value at a given path. We can utilize this. to determine whether the value is json null or the path does not exist, which should return the sql null. We handle this by changing the function that is used on SQLite in If the right hand side value is none other than the key and path transforms chained with the exact loop There are also other lookups implemented for

Speaker 1: JSON field. The first ones are the containment lookups. For example, let's use this JSON object again but let's add some more data here and then let's say I want to query all the JSON objects that have the value of H21 and also a PAP goldfish in one of the PETs. Well the contains lookup lets you do this. It looks for the supersats of the right hand side value that you provide in the query. This is implemented on Postgres using the containment operator and on MySQL and MariaDB there's the JSON contains operator function that provides the same functionality. Sadly there is no such function on SQLite and Oracle

Speaker 1: so We cannot support this. We can actually implement custom functions, but it's kinda tricky to get the subset checking correctly. Therefore we decided to drop the support for the contains lookup on SQLite and Oracle. And then there's also the contained by lookup which is basically the inverse of the contains lookup. So instead of looking for supersets we look for subsets and this is implemented by using the reverse containment operator on Postgres. On MySQL and MariaDB you just flip the arguments to the JSON contains function. And the last lookups are the key existence lookups.

Speaker 1: These lookups let you query for JSON objects that have certain keys. The first one is the Haskell lookup, which lets you query the objects that have one specific key. On Postgres, this is implemented by using the question mark operator. On MySQL and MariaDB, there's the function jsonContainsPath that provides the same functionality. On Oracle, there's the function JSONX. On SQLite we reuse the function JSON type to determine whether the path is there or not, because it only returns SQL null if the path does not exist. So we just add is not null condition. to the return value of JSON type. And then there's the has

Speaker 1: keys lookup, which requires the objects to have all of the keys that you specify in the right hand side. This is implemented on Postgres user using the question mark and ampersand operator on MySQL and Maria DV. This is implemented by supplying multiple paths and using using the all argument instead of one, which means that the objects should match all of the paths that we specify. On Oracle and SQLite, we just chain the function calls with the An operator. However, we can see that the Oracle and SQLite implementations are very similar. To maximize code reuse, we instead unpack the JSONContains path function.

Speaker 1: to multiple json contains path calls and chain them with the AND operator. Now I don't know if there's any performance impacts from this and it was not my call to change it to this implementation, so If you find out that there is some performance impacts, feel free to let me know and I will try to fix it. And then the last lookup is the has any keys lookup which is basically like has keys but you only need the JSON objects to match at least one of the keys that you specify in the right hand side. On Postgres this is implemented using the question mark and the vertical bar operator on MySQL and MariaDB.

Speaker 1: It's like has keys lookup but we use the one argument instead of all. And on Oracle and SQLite we just chained the function calls with the OR operator. We did the same thing with the MySQL implementation so that they all use the same code with different function templates. And that's it. So where to go from here? Well if you want to improve the JSON field implementation you can find room for optimizations or you can also implement the currently unsupported lookups on SQLite and Oracle database. As for the usage of JSON field itself, if you still for some reason want to validate your JSON

Speaker 1: data with a JSON schema, you can do so using Django validators. For those of you who only use LTS versions and could not yet try the JSON field in Django 3. 1, well don't be sad. you can use the Django JSONFi Backport package on PyPI that I have made. It supports Django 2. 2 and 3. 0 so if for some reason you are still on 3. 0 you can also check that out and there's nothing left for me to say other than thank you for listening to me. This is my first ever talk at a conference so I really appreciate the opportunity. The slides are available hosted on my website or you can also find them on my GitHub. And as a bonus, here's a photo of Buggle.

Speaker 1: So thank you very much. So um Adam asked what was the hardest part of the Distance Gold project? Well Um to be honest, it was the lookups and the transforms because there are a lot of different functions on each database. They behave differently. So I kind of have to find some tricks like the JSON type thing on SQLite and also the JSON contains function is not available on

Speaker 1: SQLite and Proticle. So yeah, that was kind of hard to figure out

Speaker 2: because I need to think which ones to support and if they're actually possible to be supported at at all. And Pedro asks um If the update of fields is also supported. Oh. Okay, so for the if you mean the update using the dot update method. There's a ticket for that for because currently it's not supported, but I think someone else was working on it, but I'm not sure if there's any progress on that. I will have to chat Also, uh Magda here asked whether there

Speaker 2: is any performance differences between um using JSON field and querying at the path age or just you know like using an integer field at the model itself To be honest, I haven't done any benchmarks on that, so I cannot tell you um if there is any performance differences, but there 's I think there there's Yeah it's very likely that there is um performance difference because with JSON field you have to call the functions that extract the value at the path that you specify. So yeah, there's gonna be some

Speaker 2: um performance differences, I think. But that's a good question. Maybe I will explore that in my bicellar thesis. So, um do we have any other questions? How long did it take to build the package? Um yeah, someone messaged me privately here. Um What do you mean with package if you mean the Django JSON field backboard package? Okay, yeah, that package did not take me very long because I've already done most of the work in the

Speaker 2: pull request to Django before. So it kind of just take me a few days to um kind of extract all the patches that were made to Django code base and incorporate them into the separate package because I have limited access to the um to the Django APIs. I cannot modify a lot of them from third-party package. But it didn't take very much time because I have a lot of these things figured out when I was working on the Google Saber of Code project. So, um any other questions?

Speaker 3: I guess not. Thank you.

Speaker 2: Okay. Someone asked me um what were the research materials used? Um Uh I'm not sure what you mean by research materials, but uh I did definitely use Docker to spin up multiple databases on my machine because It's um it's kind of hard to install them manually and there's no point in doing that anyway. So I just um I use the Django Docker box project on GitHub which lets you kind of run the tests for the Django project itself

Speaker 2: on multiple database backends. um with Docker. That was very helpful because it saved me a lot of time rather than having to set up all the databases separately But Oracle did take more time though, as usual. So, any other questions? Um, let's see. Looks like there's no more questions Well, I will stay here for uh a few minutes before the next talk.

Speaker 2: Okay, there's one question. Um Are there any plans to support index in JSON fields? Uh actually I think you already can use indexes on JSON fields using the uh on Postgres at least uh using the I believe it was gin index. No, but it's not the usual index like um that you that we usually use. But I have to check on that because I personally haven't tried that. Okay.

Speaker 4: Hi, Sage, love the talk.

Speaker 2: Thank you.

Speaker 4: Just to extend on the indexes, there's also the ability to index like a single key out of your JSON. But this is with functional indexes, which we don't have support for in Django.

Speaker 2: Okay. Does that work on all database backends?

Speaker 4: Uh with some with some in some way or another, yeah.

Speaker 2: Okay.

Speaker 4: You can basically index a function of one of your columns so you just have run that JSNX track function you're

Speaker 2: Thank you.

Speaker 4: I was wondering how you edited your talk.

Speaker 2: Um I used a trial of Aduby Premiere. And that did take a lot of time because uh I was recording it at like midnight and then I have to spend Two days editing that video.

Speaker 4: Oh wow. And and did that do the subtitles as well? Was that the subtitled tool?

Speaker 2: Uh f yeah, for these subtitles, um I used a third-party service. Um It's kind of related to the place where I had my internship. It's called Iconics. So I just put the video there and the uh they generate automatic caps captions but then I have to edit it to match because for example Jason was used with the A Jason like the name So yeah, I have to edit that kind of things.

Speaker 4: I used to build an API and on the team we had two people called Jason. So it was very confusing.

Speaker 2: Um there's one more question and I think uh after this we should um continue for the next talk. Um what's your choice for the Postgres fields Whether the oh contour. postgres. Um that one is already deprecated, actually replaced by this one. The implementation is actually now a subclass of the cross-database JSON field now. So um you should not use that anymore starting from Django 3. 1. How stressful was it respecting all the best practices Um if you if you mean that um the best practices for contributing to Django, um

Speaker 2: it was not really that stressful because I have such great mentors here. Thank you very much. They helped me during the project, so um it was not stressful at all. I think it was more about how I have to think a lot of how figuring out the queries work on each database backend. Okay So I think that's it. Let's uh proceed for the next talk. I will stay here probably after the end of the conference. So thank you. Thank you. Thanks. Thank you. Thank you so much. Thank you.

Speaker 4: Thank you.

Speaker 3: Thank you.

Speaker 2: Thank you. Thank you.