English

postgresql
jsonb
json
database

Mastering PostgreSQL JSONB type in one article

Learn how to use PostgreSQL's JSONB type to efficiently store, query, and manipulate JSON data.

Yijun

Developer

6/6/20244 min read

Stop wasting weeks on user auth

Launch secure apps faster with Logto. Integrate user auth in minutes, and focus on your core product.

Get started

In modern application development, handling and storing unstructured data is becoming increasingly common. PostgreSQL's JSONB type provides a powerful tool for developers to efficiently store and query JSON data. In this article, we will delve into the concept and usage of the JSONB type and demonstrate its powerful features through specific code examples.

What is JSONB?

JSONB is a PostgreSQL data type that stores JSON data in a binary format. Unlike the regular JSON type, JSONB is more efficient in querying and manipulation. It supports a rich set of operators and functions that simplify JSON data handling. Additionally, JSONB supports multiple index types, including B-tree, Hash, GIN, and GiST indexes, further enhancing query performance.

Creating JSONB type

Creating a table with JSONB type

First, let's create a table with a JSONB column. Suppose we have a products table to store product information, with product details stored using the JSONB type.

Inserting JSONB data

We can insert JSON data into a JSONB field using a simple INSERT statement.

Querying JSONB data

Extracting values from JSONB fields

We can use the ->> operator to extract text values from a JSONB field. The following example shows how to extract the brand information of a product.

Extracting nested values with JSONB

We can use a combination of -> and ->> operators to extract values nested within a JSON structure. The following example shows how to extract the CPU information of a product.

Extracting values with JSONB paths

Using the #>> operator, we can extract values from specific paths within the JSON data. The following example shows how to extract storage information.

Using `@>` operator for containment queries

The @> operator checks if a JSONB object contains another JSONB object. The following example shows how to query products of a specific brand.

Modifying JSONB data

Updating JSONB data

We can use the jsonb_set function to update JSONB data. This function updates the value at a specified path. The following example shows how to update the storage information of a product.

Deleting top-level fields in JSONB data

We can use the - operator to remove top-level fields from JSONB data. This operator deletes the specified key from a JSONB object. The following example shows how to remove a top-level field from product details.

Deleting nested fields in JSONB data

We can use the #- operator to remove specific path elements from JSONB data. This operator deletes the key at the specified path. The following example shows how to delete the CPU information from product specifications.

Advanced JSONB queries

Using JSONB arrays

JSONB data can include arrays, and we can use array-related operators to handle these data. The following example shows how to store and query products with multiple tags.

In the above example, you can also use the ? operator to check if a JSONB array contains a specific element:

Merging JSONB data

We can use the || operator to merge two JSONB objects. This operator combines two JSONB objects into one. The following example shows how to merge new specifications into existing product details.

Aggregating JSONB data

We can use aggregation functions to process JSONB data, such as counting the number of products for each brand.

JSONB indexing and performance Optimization

To improve query performance with JSONB data, we can create indexes on specific keys within the JSONB field. Choosing the right type of index is crucial for different query needs.

GIN Index: Suitable for complex queries involving multivalued data, arrays, and full-text search.
B-tree Index: Suitable for simple key-value pair queries, range queries, and sorting operations.

Properly selecting and creating indexes can significantly boost the performance of JSONB data queries.

Creating GIN index

GIN indexes are ideal for speeding up queries involving multivalued data and arrays. For example, we can create a GIN index for the tags array mentioned earlier to accelerate queries on array elements.

Creating B-tree index

For simple key-value pair queries, a B-tree index can also improve performance significantly. B-tree indexes are suitable for range queries and sorting operations. The following example shows how to create a B-tree index for the model key in the details

field.

Conclusion

PostgreSQL's JSONB type provides a powerful and flexible solution for handling JSON data. With the introduction and examples in this article, you should now have a comprehensive understanding of how to use the JSONB type. By mastering these techniques, you can efficiently handle and query unstructured data, enhancing the performance and flexibility of your applications.

Try Logto Cloud for free

What is JSONB?#

Creating JSONB type#

Creating a table with JSONB type#

Inserting JSONB data#

Querying JSONB data#

Extracting values from JSONB fields#

Extracting nested values with JSONB#

Extracting values with JSONB paths#

Using @> operator for containment queries#

Modifying JSONB data#

Updating JSONB data#

Deleting top-level fields in JSONB data#

Deleting nested fields in JSONB data#

Advanced JSONB queries#

Using JSONB arrays#

Merging JSONB data#

Aggregating JSONB data#

JSONB indexing and performance Optimization#

Creating GIN index#

Creating B-tree index#

Conclusion#

What is JSONB?#

Creating JSONB type#

Creating a table with JSONB type#

Inserting JSONB data#

Querying JSONB data#

Extracting values from JSONB fields#

Extracting nested values with JSONB#

Extracting values with JSONB paths#

Using @> operator for containment queries#

Modifying JSONB data#

Updating JSONB data#

Deleting top-level fields in JSONB data#

Deleting nested fields in JSONB data#

Advanced JSONB queries#

Using JSONB arrays#

Merging JSONB data#

Aggregating JSONB data#

JSONB indexing and performance Optimization#

Creating GIN index#

Creating B-tree index#

Conclusion#

What is JSONB?

Creating JSONB type

Creating a table with JSONB type

Inserting JSONB data

Querying JSONB data

Extracting values from JSONB fields

Extracting nested values with JSONB

Extracting values with JSONB paths

Using `@>` operator for containment queries

Modifying JSONB data

Updating JSONB data

Deleting top-level fields in JSONB data

Deleting nested fields in JSONB data

Advanced JSONB queries

Using JSONB arrays

Merging JSONB data

Aggregating JSONB data

JSONB indexing and performance Optimization

Creating GIN index

Creating B-tree index

Conclusion

What is JSONB?

Creating JSONB type

Creating a table with JSONB type

Inserting JSONB data

Querying JSONB data

Extracting values from JSONB fields

Extracting nested values with JSONB

Extracting values with JSONB paths

Using `@>` operator for containment queries

Modifying JSONB data

Updating JSONB data

Deleting top-level fields in JSONB data

Deleting nested fields in JSONB data

Advanced JSONB queries

Using JSONB arrays

Merging JSONB data

Aggregating JSONB data

JSONB indexing and performance Optimization

Creating GIN index

Creating B-tree index

Conclusion