PostgreSQL is a powerful Open-Source database management system and a migration target for Unstructured databases like MongoDB. as well as Relational databases like Oracle and SQL Server. One of the feature supporting the transition from MongoDB to PostgreSQL is its support for JSONB (Binary JSON). JSONB is a data type that allows efficient storage, indexing, and querying of JSON (JavaScript Object Notation) documents. In this blog post, we shall discuss the internals of JSONB in PostgreSQL, explore some of its features, use cases, and best practices.
What is JSONB?
JSONB is a binary representation of JSON data, providing a more efficient storage format compared to the traditional JSON type. While both JSON and JSONB support the same set of JSON operators and functions, JSONB has several advantages, including faster indexing, reduced storage space, and improved query performance.
Key Features of JSONB:
-
Binary Storage Format : JSONB stores data in a binary format, allowing for more efficient space utilisation and faster processing. MongoDB’s BSON (Binary JSON) also stores the JSON in a binary format.
-
Indexing Support : PostgreSQL provides indexing support for JSONB data, enabling faster searches and retrievals.
-
Data Integrity : JSONB enforces well-formed JSON data, ensuring that stored documents adhere to the JSON specifications.
-
Querying Capabilities : JSONB supports a wide range of operators and functions for querying and manipulating JSON data directly within the database.
Difference between JSON and JSONB
The JSON and JSONB data types in PostgreSQL serve as containers for storing JSON data, but they differ in how they handle and optimize the storage and querying of that data.
JSON, being a basic data type, stores JSON data in a raw format resembling a blob. It preserves every detail, including white spaces, the order of keys in objects, and even duplicate keys. However, JSON comes with limited querying capabilities and can be slower as it loads and parses the entire JSON blob each time a query is executed.
On the other hand, JSONB stores JSON data in a custom, optimized format enabling efficient querying capabilities. Unlike JSON, it doesn’t need to re-parse the entire JSON blob with each query, and thus resulting in faster and more efficient operations.
To decide between JSON and JSONB, consider your specific use case. If you anticipate not performing many JSON querying operations and value preserving every detail in the data, the JSON data type might be suitable. However, for scenarios where optimized querying is essential, JSONB is the preferred choice.
Let’s illustrate the difference with an example:
postgres=# SELECT '{"product": "Laptop", "price": 999.99, "brand": "ABC"}'::json,'{"product": "Laptop", "price": 999.99, "brand": "ABC"}'::jsonb;
json | jsonb
--------------------------------------------------------+--------------------------------------------------------
{"product": "Laptop", "price": 999.99, "brand": "ABC"} | {"brand": "ABC", "price": 999.99, "product": "Laptop"}
(1 row)In this example, both JSON and JSONB representations contain the same data, but notice how JSONB optimizes the storage for efficient querying, altering the key order without affecting the data’s integrity.
Use Cases for JSONB in PostgreSQL
-
Flexible Schema: JSONB is ideal for scenarios where the data structure may evolve over time. Its flexible schema allows you to store and query data without strict constraints.
-
Configurable Settings : Settings or configurations that might change frequently can be stored in JSONB, allowing for easy updates without altering the table schema.
-
Nested Structures : JSONB supports nested structures, making it suitable for scenarios where data has a hierarchical or nested format.
-
Document Storage : Storing documents, such as blog posts, comments, or user profiles, where the structure varies, benefits from JSONB’s flexibility.
Querying JSONB Data
Querying JSONB data in PostgreSQL involves using various operators and functions designed to navigate and filter through the JSONB structure. Lets look into accessing JSONB column data using some of the operators.
Creating a Table with JSONB Column :
CREATE TABLE linkedin_profiles ( id SERIAL PRIMARY KEY, profile_data JSONB );Inserting JSONB Data :
INSERT INTO linkedin_profiles (profile_data) VALUES
('{"name": "John Doe", "title": "Software Engineer", "company": "Tech Solutions Inc.", "skills": ["Java", "Python", "Web Development", {"languages":["English","Telugu"]}],"exp":"11"}'),
('{"name": "Alice Smith", "title": "Data Scientist", "company": "Data Insights Co.", "skills": ["Machine Learning", "Data Analysis", "Python"],"exp":"6"}');Accessing JSONB Fields
Use the -> operator to access the value of a specific key:
-- Retrieve the names from JSONB columns using the name key
SELECT id, profile_data->>'name' as name FROM linkedin_profiles;use the ->> operator for text extraction:
-- Retrieve the experience as text
SELECT profile_data->>'exp' as experience_in_years FROM linkedin_profiles;Checking for Key Existence
Use the ? operator to check if a specific string exists as a top-level key within a JSONB value:
-- Retrieve products with a 'brand' key
SELECT profile_data->>'name' as name, profile_data->'skills' as skills
FROM linkedin_profiles
WHERE profile_data->'skills' ? 'Python';Filtering Based on Key and Value :
Combine the -> and = operators to filter based on a specific key and value
-- Retrieve products with a specific price
SELECT * FROM products WHERE product_data->>'price' = '999.99';jsonb_path_query Function
The jsonb_path_query function allows traversing through nested JSON objects by using a path expression. It is useful when you need to extract specific elements or values from deep within a JSONB structure.
--Retrieve the language at the first index from skills field
SELECT jsonb_path_query(profile_data, '$.skills.languages[1]')
FROM linkedin_profiles;These examples showcase how to query JSONB data in the products table using various operators. You can adapt and modify these queries based on your specific use case and data structure. For a more detailed exploration on operators/functions, you might want to visit JSON Functions and Operators.
Indexing JSONB Columns in PostgreSQL
PostgreSQL offers powerful mechanisms to improve the performance of queries involving JSONB data through indexing. Indexing becomes crucial when working with large datasets or when specific fields within the JSONB data are frequently queried.
Consider a scenario where you have a table named employees storing JSON documents , each adhering to a defined schema. An example JSON document looks like this:
{
"employee_id": "12345",
"full_name": "John Doe",
"is_active": true,
"department": "Engineering",
"address": "456 Tech Street, Cityville, CA, 12345",
"joined_date": "2022-05-10T09:30:00Z",
"latitude": 37.7749,
"longitude": -122.4194,
"skills": [
"JavaScript",
"React",
"Node.js"
]
}These documents are stored in a table named employees and the JSONB column is named emp_data. By creating a [GIN index](https://www.postgresql.org/docs/current/gin-intro.html “GIN index”) on this column, you can optimize queries like the following.
-- Find employees whose "department" is "Engineering"
SELECT emp_data->'employee_id', emp_data->'full_name'
FROM employees
WHERE emp_data @> '{"department": "Engineering"}';However, the index may not be utilized for queries like the following, as the ? operator is not directly applied to the indexed column : emp_data.
-- Find employees whose "skills" array contains the value "Node.js"
SELECT emp_data->'employee_id', emp_data->'full_name'
FROM employees
WHERE emp_data -> 'skills' ? 'Node.js';If searching for specific items within the skills array is a common use case, it may be beneficial to define an index like following.
CREATE INDEX idx_gin_skills ON employees USING GIN ((emp_data -> 'skills'));Now, the WHERE clause emp_data -> 'skills' ? 'Node.js' will be recognized as an application of the indexable operator "?" to the indexed expression emp_data -> 'skills'.
Best Practices for JSONB Usage
Optimize Indexing : Consider using GIN (Generalized Inverted Index) indexes for efficient JSONB queries. Index only the specific keys that are frequently queried.
Use Constraints : Leverage JSONB constraints to ensure data integrity and well-formed JSON documents.
Careful with Updates: Be cautious when updating JSONB data, as in-place updates may lead to storage fragmentation.
Normalize When Needed: For highly structured and frequently queried data, consider normalising the schema rather than using JSONB.
Query Optimization: Utilize the full range of JSONB operators and functions for optimized querying.
Conclusion
PostgreSQL’s support for JSONB adds a powerful dimension to the relational database system, enabling the storage and retrieval of semi-structured and flexible data. By understanding JSONB’s features, use cases, and best practices, developers can make informed decisions when implementing solutions that involve JSONB data in PostgreSQL. Whether you’re dealing with evolving data structures or nested documents, JSONB provides a versatile and efficient solution within the robust PostgreSQL ecosystem.
Seeking specialized PostgreSQL support or Database Migrations assistance ? Get experts advice for effective data management, query tuning, database migrations and database optimization. Click here for personalized expertise to elevate your database performance and meet your unique business needs.
Subscribe to our Newsletters and Stay tuned for more interesting topics.
