One of the many data types that PostgreSQL support is JSON. Since most of the web APIs communication uses JSON payload immensely, this feature is rather important. Rather than using the plaintext data type to store JSON objects, Postgres has a different data type which is optimized for JSON payloads, verifies that data stored in these fields confirms to the<\/p>\n
RFC specification<\/a><\/p>\n . Also in a classic Postgres manner, it allows you to fine-tune your JSON fields for maximum performance.<\/p>\n While creating a table, you will have two options for your JSON column. Plain json data type and jsonb data type, both have their own advantages and disadvantages. We shall go through each of them, by creating a simple table with just 2 columns an ID and a JSON value. Following this we will query data from the table and get a feel for how to manage JSON formatted data inside Postgres.<\/p>\n Let\u2019s create a simple two column table named users:<\/p>\n CREATE TABLE users ( Here the column id acts as the primary key, and it will increase in an incremental fashion thanks to the pseudotype serial so we won\u2019t have to worry about manually entering values for id as we go along.<\/p>\n The second column is of json type and is forced to be NOT NULL. Let\u2019s enter a few rows of data to this table, consisting of JSON values.<\/p>\n INSERT INTO users (info) VALUES ( INSERT INTO users (info) VALUES ( You can use your prefered JSON beautifier\/minifier<\/a> to convert the JSON payloads above into a single line. So you can paste it at a go into your psql prompt.<\/p>\n SELECT<\/p>\n *<\/p>\n FROM<\/p>\n users;<\/p>\n id<\/p>\n |<\/p>\n info<\/p>\n —-+——————————————————1 | {“name”: “John Doe”, “email”: “johndoe@example.com”…}<\/p>\n 2 | {“name”<\/p>\n :<\/p>\n “Jane Doe”<\/p>\n ,<\/p>\n “email”<\/p>\n :<\/p>\n “janedoe@example.com”<\/p>\n …<\/p>\n } The SELECT command at the end showed us that the rows were successfully inserted into the users table.<\/p>\n Postgres allows you to dig into the JSON payload itself and retrieve a particular value out of it, if you reference it using the corresponding value. We can use the -> operator after the json column\u2019s name, followed by the key inside the JSON object. Doing so<\/p>\n For example, in the table we created above:<\/p>\n SELECT<\/p>\n info –<\/p>\n ><\/p>\n \u2018email\u2019<\/p>\n FROM<\/p>\n users;<\/p>\n —-+—————————————- —-+—————————————-1 | “johndoe@example.com”<\/p>\n 2 | “janedoe@example.com”<\/p>\n You may have noticed the double quotes in the column containing emails. This is because the -> operator returns a JSON object, as present in the value of key \u201cemail\u201d. Of course, you can return just text, but you will have to use the ->> operator instead.<\/p>\n SELECT<\/p>\n info –<\/p>\n >><\/p>\n \u2018email\u2019<\/p>\n FROM<\/p>\n users;<\/p>\n id<\/p>\n |<\/p>\n ?<\/p>\n column<\/p>\n ?<\/p>\n —-+—————————————-1 | johndoe@example.com<\/p>\n 2 |<\/p>\n janedoe@example.com<\/p>\n The difference between returning a JSON object and a string becomes clear once we start working with JSON objects nested inside other JSON objects. For example, I chose the \u201cpersonalDetails\u201d key to intentionally hold another JSON object. We can dig into this object too, if we want:<\/p>\n SELECT<\/p>\n info –<\/p>\n > ‘personalDetails’<\/p>\n –<\/p>\n > ‘gender’ FROM<\/p>\n users;<\/p>\n <\/p>\n ?<\/p>\n column<\/p>\n ?<\/p>\n ———-“M””F”(2 rows)<\/p>\n This can let you go as deep into the JSON object as you would want to. Let\u2019s drop this table and create a new one (with the same name) but with JSONB type.<\/p>\n Except for the fact that during creation of the table we mention jsonb data type instead of json, all else looks<\/em> the same.<\/p>\n CREATE TABLE users ( Even the insertion of data and retrieval using the -> operator behaves the same way. What has changed is all under the hood and noticeable in the table\u2019s performance. When converting JSON text into a jsonb, Postgres actually turns the various JSON value types into native Postgres type, so not all valid json objects can be saved as valid jsonb value.<\/p>\n Moreover, jsonb doesn\u2019t preserve the whitespaces, order of json keys as supplied by the INSERT statement. Jsonb actually converts the payload into native postgres binary, hence the term jsonb. <\/em><\/p>\n Of course, insertion of jsonb datum has a performance overhead because of all these additional work that postgres needs to do. However, the advantage that you gain is in terms of faster processing of the already stored data, since your application would not have the need to parse a JSON payload everytime it retrieves one from the database.<\/p>\n The decision between json and jsonb sole depends on your use case. When in doubt use jsonb, since most applications tend to have more frequent read operations that write operations. On the other hand, if you are sure that your application is expected to do more synchronous write operations than read, then you may want to consider json as an alternative.<\/p>\n People working with JSON payloads and designing interfaces for Postgres storage will benefit immensely from this particular section <\/a>of their official documentation. The developers were kind enough to furnish us with jsonb indexing and other cool features which can be leveraged to improve the performance and simplicity of your application. I implore you to investigate these as well.<\/p>\n Hopefully, you found this brief introduction of the matter helpful and inspiring.<\/p>\nJSON Data Type<\/h2>\n
1. Creating a Table with JSON Data Type<\/h3>\n
\nid serial NOT NULL PRIMARY KEY,
\ninfo json NOT NULL
\n);<\/p>\n
\n\u2018{
\n“name”: “Jane Doe”,
\n“email”: “janedoe@example.com”,
\n“personalDetails”: {“age”:33, “gender”:”F”}
\n}\u2019);<\/p>\n
\n\u2018{
\n“name”: “Jane Doe”,
\n“email”: “janedoe@example.com”,
\n“personalDetails”: {“age”:33, “gender”:”F”}
\n}\u2019);<\/p>\n
\n(2 rows)<\/p>\n2. Querying JSON Data Type<\/h3>\n
\nid | ?column?<\/p>\nJSONB Data Type<\/h3>\n
\nid serial NOT NULL PRIMARY KEY,
\ninfo jsonb NOT NULL
\n);<\/p>\nJSON vs JSONB<\/h3>\n
Conclusion<\/h4>\n