Before We Begin
JSONB is a way to store JSON data in PostgreSQL databases. This format has several important differences from how JSON data is handled in PostgreSQL and MySQL, each with their own advantages and disadvantages. When I first used PostgreSQL and encountered JSONB, I simply thought this unfamiliar type was the same as JSON and used it without much consideration. However, when problems arose during use, I organized the content I had to investigate.
JSON
JSON format is a text-based lightweight data exchange format. As a side note, when I first started developing about 10 years ago, data formats like SOAP and XML could often be seen, but now JSON has become commonplace.
JSON in Databases
Multiple databases have introduced JSON types. This is because, as mentioned above, the JSON format has become widespread, and demand for the ability to directly store and process JSON data has grown.
JSON type in MySQL: MySQL stores JSON data in text format. This has the advantage of storing data in its original form, allowing you to get an exact replica of the stored document. However, query processing speed for values inside JSON can be slower than JSONB. Because it's text, it needs to be parsed every time it's used.
JSON in PostgreSQL: PostgreSQL added the JSON type starting from version 9.4. Instead of storing JSON as text, PostgreSQL stores data in binary format to use space efficiently and improve search and processing speed.
- Comparison Table
| JSON | JSONB | |
|---|---|---|
| Storage | Preserves the format | Changes it to binary format |
| Parsing time | Less | More |
| Reparsing required | No | Yes |
| Indexing | Doesn't support directly | Supported |
| Data processing | Slow | Fast |
| Whitespaces | Preserves whitespaces | Removes whitespaces |
| Ordering of keys | Preserved | Ordered by keys |
| Duplicates | Preserved | Not allowed (Last assigned value will be stored in the key) |
The advantage is that indexing can be created on JSONB data, speeding up searches in large datasets. Additionally, data is internally optimized for better space efficiency.
The disadvantage is that JSONB may incur slight overhead during data storage due to internal conversion processes. Also, data may be stored in a slightly modified form, so it may not exactly match the original document.
Data Comparison in JSONB
Unlike MySQL's JSON, JSONB is stored after being parsed, so when comparing with original data, it must be converted to JSONB.
SELECT *
FROM table_name
WHERE jsonb_column_name = 'JSON_string_value'::jsonb;To check if it matches the stored value, you need to compare after type conversion with ::jsonb as shown above.
More guides related to JSONB queries can be found at the link below: Querying JSON (JSONB) data types in PostgreSQL
Understanding how to handle JSON types in databases allows you to predict how they work and their performance differences.