intro
Learn everything you need to know about JSONB, the recommended PostgreSQL data type for storing JSON data.
When relational databases added the ability to store and manage JSON data, a new era began. This opened up many new opportunities, and PostgreSQL was one of the first database systems to add JSON support. Here we will focus on the Postres jsonb data type, the most efficient way to represent JSON in PostgreSQL.
In this article, you will learn what jsonb is, why it was added to PostgreSQL, how it works, and what features it offers.
Become a jsonb PostgreSQL expert!
How We Got to JSONB
Before jumping into the history of jsonb in PostgreSQL, why should you even consider storing JSON data in a relational database? Well, for at least three good reasons:
These are the main reasons why the json data type was introduced in PostgreSQL 9.2 in 2012. Originally, JSON support was quite limited and involved nothing more than validating JSON before storing it in the database.
In 2014, with version 9.4, the jsonb data type was finally added to PostgreSQL. This introduced a new way of storing JSON data, which leads to efficient parsing and opened the door to indexing and JSON querying.
Lastly, PostgreSQL 12 released in 2019 added support for the SQL/JSON standard and JSONPath queries. To learn more, see our PostgreSQL JSONPath guide.
What Is the PostgreSQL JSONB Data Type?
In PostgreSQL, jsonb is a data type for storing JSON data in an optimized, decomposed binary format. The name "JSONB" stands for "JSON Binary," or "JSON Better" according to others. But why should it be "better?" Because the binary representation used by jsonb tends to be more efficient than the raw textual data used by the original PostgreSQL json data type.
How JSONB Works in Postgres
The Postgres jsonb data type accepts JSON strings in the format specified in RFC 7159. When converting a JSON string to jsonb, PostgreSQL must convert the input data to the destination binary format. This introduces a minor overhead compared to storing JSON data directly in raw text. During this conversion process, jsonb does not preserve whitespace characters, object key order, or keep duplicate object keys.
While RFC 7159 allows JSON strings to contain Unicode escape sequences (in the \uXXXX format), the Postgres jsonb data type is stricter. In detail, it does not support Unicode escapes for characters that cannot be represented in database encoding. In addition, jsonb rejects \u0000, as this cannot be represented in the PostgreSQL text type either.
When converting textual JSON input to jsonb, the JSON primitive types are mapped to native PostgreSQL types, as shown in the table below:
| JSON Primitive Input Type | PostgreSQL Destination Type | Notes |
|---|---|---|
string | text | \u0000 is not allowed |
number | numeric | NaN and infinity values are not allowed |
boolean | boolean | Only lowercase true and false strings are accepted |
null | (none) | JSON null and SQL NULL are two different concepts |
Because of this conversion, input JSON primitives become subject to the limitations of the destination PostgreSQL data types. For example, jsonb will reject numbers that fall outside the range of the numeric data type.
Now, suppose you have the following JSON string:
1
{
2
"name": "John Doe",
3
"age": 30,
4
"email": "john.doe@example.com",
5
"address": {
6
"street": "123 Main Street",
7
"city": "Anytown",
8
"state": "CA",
9
"zipcode": "12345"
10
},
11
"hobbies": ["reading", "hiking", "cooking"],
12
"is_student": false
13
}
This is how you can convert it to jsonb in PostgreSQL with a simple cast:
1
SELECT '{"name": "John Doe", "age": 30, "email": "john.doe@example.com", "address": {"street": "123 Main Street", "city": "Anytown", "state": "CA", "zipcode": "12345"}, "hobbies": ["reading", "hiking", "cooking"], "is_student": false}'::jsonb AS jsonb_data;
If duplicate keys are specified in the input, only the last value will be kept:
1
SELECT '{"name": "John Doe", "age": 30, "age": 35}'::jsonb AS jsonb_data;
Thus, this will produce:
Executing the JSONB Postgre query in DbVisualizer
Note that the resulting jsonb data only contains a single age key. Also, the output key order does not respect the input key order because of the binary data conversion performed by PostgreSQL under the hood.
Thanks to the special data format jsonb stores JSON data in, users have access to a lot of efficient features, functions, and operators. Let’s explore dig into some of them!
Main Features of JSONB
Explore the four most important jsonb Postgres features.
Data Validation
The jsonb data types natively enforce that the input JSON string is valid JSON according to RFC 7159. If you try to store invalid JSON to jsonb as in the query below:
1
SELECT ('{age: 31'::jsonb) AS jsonb_data;
You will get the following error:
1
ERROR: invalid input syntax for type json
This means the Postgres jsonb data type performs integrity fand validation checks behind the scenes before attempting to store the JSON data in the database.
Containment and Existence
jsonb provides powerful capabilities for verifying containment and existence within JSON documents. Containment tests whether one jsonb document contains another, considering both structure and data. On the other hand, existence determines whether a specific string exists as a key or array element in the JSON document.
Indexing
PostgreSQL supports GIN indexes for efficient searching of keys or key/value pairs within jsonb documents. In particular, it provides two GIN operator classes, offering different trade-offs between performance and flexibility. These indexes enable fast retrieval of PostgreSQL jsonb data based on specific criteria.
Subscripting
jsonb subscripting enables elements within JSONB values to be extracted and modified using array-like expressions. Consider this Postgres jsonb query example:
1
SELECT ('{"name": "Maria"}'::jsonb)['name'];
That will return:
1
"Maria"
That feature supports accessing nested values by key path or index, as well as updating values dynamically. Subscripting expressions can be used in SELECT and UPDATE statements to retrieve or modify jsonb data efficiently.
Postgres JSONB Operators
PostgreSQL supports a lot of JSON operators and functions to simplify and enhance JSON data management in SQL. While most of the work with both the json and jsonb data types, some are only available with jsonb. These are:
Find out more in the official documentation.
Pros and Cons of JSONB
See the main advantages and disadvantages of storing JSON data in PostgreSQL using jsonb.
👍 Pros:
👎 Cons:
Conclusion
In this guide, you learned what the PostgreSQL jsonb data type is, how it works, what features and operators it offers, and its pros and cons. You now know that jsonb is the recommended data type for JSON storage in a Postgres database. This is because it stores JSON strings in a binary format specifically designed for efficient data retrieval and manipulation.
When dealing with data management, it is essential to choose a solution that supports all the data types of your DBMS. Enter DbVisualizer, a top-rated database client that is considered one of the best PostgreSQL clients on the market. Some of the features it offers are ERD-like schema generation and advanced query optimization. Try DbVisualizer for free today!
FAQ
Does JSONB support GIN indexes?
Yes, jsonb supports GIN (Generalized Inverted Index) indexes. These indexes enable efficient searching of keys or key/value pairs within JSON documents.
What is the difference between JSON and JSONB in PostgreSQL?
In PostgreSQL, json and jsonb are both data types to store JSON data. The main difference is that json stores data as plain text, while the Postgres jsonb data type stores JSON data in a special binary format. jsonb offers faster access and more efficient storage while json is better for raw JSON representation. Find out more in our json vs jsonb comparison article.
What is the size limit of the Postgres JSONB data type?
In PostregSQL 9.2, the maximum size of a jsonb document was 255 MB. As of PostgreSQL 9.3.5, jsonb has the same maximum size as text, which is 1 GB. Therefore, the current size limit of the Postgres jsonb data type is 1 GB. You can verify that in the source code of PostgreSQL.
Why store JSON data in JSONB and not in TEXT in PostgreSQL?
Storing JSON data in jsonb format rather than text in PostgreSQL offers several advantages:
Does PostgreSQL support some JSONB extensions?
Yes, PostgreSQL supports jsonb extensions like jsonb_plperl, jsonb_plperlu, and jsonb_plpython3u. These map jsonb values to appropriate data structures in Perl and Python. jsonb_plperl is considered "trusted" and can be installed by all users with CREATE privileges, while the other two require superuser privileges.
