How to Use the JSON Data Type in PostgreSQL: A Brief Manual
Database is a topic that any programmer has undoubtedly encountered in school and work life. Even if you cannot get a definitive answer when you ask a random person, “What is a database?”, it is still a familiar word for everyone in the 21st century. So what is a database?
Database is the total area of stored information that has a relationship with each other and is usually kept in electronic environments. It is the storage system where the information or data flow is controlled on the computer. It is an alternative to the traditional storage method and was developed for usage instead of a common file-process system.
As programmers, we don’t settle with the definition of “database”; instead, we focus on how to use it. MS Access, MySQL, Oracle and PostgreSQL are example database programs most used by back-end programmers. These databases contain rows and columns as a basis, where rows are the model of recorded data, and columns have a name and a data type.
I will give JSON as the data type in the PostgreSQL server column and discuss how to read and write in this data type. So, first, let’s look at what JSON format is and what we use it for.
JSON (JavaScript Object Notation) is a lightweight data-interchange format. It is easy for humans to read and write and for machines to parse and generate. JSON is based on a subset of the JavaScript Programming Language Standard ECMA-262 3rd Edition — December 1999. JSON is a text format that is completely language independent but uses conventions that are familiar to programmers of the C-family of languages, including C, C++, C#, Java, JavaScript, Perl, Python, and many others. These properties make JSON an ideal data-interchange language.
This article will specifically discuss how to save data in JSON format in the PostgreSQL database and how to read the saved JSON data.
PostgreSQL is a SQL language that enables us to work with JSON format. We can keep data in JSON format and write queries, record, update these data, and delete them through the database. For example, when creating a table in the database, we can choose the data type coming to the table as JSON. When we run the code below, the data coming to the account_data column from the related PostgreSQL server table should be in JSON format.
If you try to create a record different from the JSON format, you will get the error message below.
In this way, a record other than the JSON format will not be created while saving the data in the relevant column. We’ve created this column, but how will we create a record in JSON format in the related place? In the script code below, we’ve created a record in JSON format in the account_data column and saved it in the database.
As is seen below, the record is created in the related column and is written in the database.
Let’s use colour information for filtering, SQL code to bring the ones with the colour red:
One more JSON object is held in the recorded value in JSON data type with format key. This JSON object contains a value with a string key. If we want to filter the values with “JSON”, we run the script below and do filtering in the database.
As a result of this query, all of the data coming from the database are the ones with the string value of JSON. Let’s do filtering with a more nested key value. As seen in the JSON data, the format key holds a JSON object. The colour key in the JSON object also contains a JSON object. It is seen that this JSON object has key values like r, g, and b. As an example, when we do filtration on an r value and want to filter the r values equal to 100, we can use a query line like below:
Another example is that we can write a query line like below when filtering the data of colours g and b with values bigger than 120.
The example above can be reproduced infinitely; it depends on our imagination. As the last example, I want to show how to update the corresponding row in the PostgreSQL server. The update logic is the same as the other SQLs. You can update the line in the related server with the script below.
If you try to update in a different format than JSON, you will get the error message below.
