Palomino for Logto

Posted on Jun 14 • Originally published at blog.logto.io

Mastering PostgreSQL JSONB type in one article

#webdev #programming #postgressql #database

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

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.

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    details JSONB
);

Inserting JSONB data

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

INSERT INTO products (name, details) VALUES (
    'Laptop',
    '{
        "brand": "Dell",
        "model": "XPS 13",
        "specs": {
            "cpu": "i7",
            "ram": "16GB",
            "storage": "512GB SSD"
        }
    }'
);

-- Query result
-- id |   name   |                             details
-- ---+----------+----------------------------------------------------------------
--  1 | Laptop   | {"brand": "Dell", "model": "XPS 13", "specs": {"cpu": "i7", "ram": "16GB", "storage": "512GB SSD"}}

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.

SELECT
    name,
    details->>'brand' AS brand
FROM
    products;

-- Query result
--   name  | brand
-- --------+-------
--  Laptop | Dell

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.

SELECT
    name,
    details->'specs'->>'cpu' AS cpu
FROM
    products;

-- Query result
--   name  | cpu
-- --------+-----
--  Laptop | i7

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.

SELECT
    name,
    details#>>'{specs,storage}' AS storage
FROM
    products;

-- Query result
--   name  |  storage
-- --------+------------
--  Laptop | 512GB SSD

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.

SELECT
    name,
    details
FROM
    products
WHERE
    details @> '{"brand": "Dell"}';

-- Query result
--   name  |                             details
-- --------+----------------------------------------------------------------
--  Laptop | {"brand": "Dell", "model": "XPS 13", "specs": {"cpu": "i7", "ram": "16GB", "storage": "512GB SSD"}}

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.

UPDATE products
SET details = jsonb_set(details, '{specs,storage}', '"1TB SSD"')
WHERE name = 'Laptop';

-- Query updated data
SELECT
    name,
    details
FROM
    products;

-- Query result
--   name  |                             details
-- --------+----------------------------------------------------------------
--  Laptop | {"brand": "Dell", "model": "XPS 13", "specs": {"cpu": "i7", "ram": "16GB", "storage": "1TB SSD"}}

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.

UPDATE products
SET details = details - 'model'
WHERE name = 'Laptop';

-- Query updated data
SELECT
    name,
    details
FROM
    products;

-- Query result
--   name  |                             details
-- --------+----------------------------------------------------------------
--  Laptop | {"brand": "Dell", "specs": {"cpu": "i7", "ram": "16GB", "storage": "1TB SSD"}}

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.

UPDATE products
SET details = details #- '{specs,cpu}'
WHERE name = 'Laptop';

-- Query updated data
SELECT
    name,
    details
FROM
    products;

-- Query result
--   name  |                             details
-- --------+----------------------------------------------------------------
--  Laptop | {"brand": "Dell", "model": "XPS 13", "specs": {"ram": "16GB", "storage": "1TB SSD"}}

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.

INSERT INTO products (name, details) VALUES (
    'Smartphone',
    '{
        "brand": "Apple",
        "model": "iPhone 12",
        "tags": ["electronics", "mobile", "new"]
    }'
);

-- Query products with specific tags
SELECT
    name,
    details
FROM
    products
WHERE
    details @> '{"tags": ["mobile"]}';

-- Query result
--     name     |                                    details
-- ------------+----------------------------------------------------------------------------
--  Smartphone | {"brand": "Apple", "model": "iPhone 12", "tags": ["electronics", "mobile", "new"]}

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

SELECT
    name,
    details
FROM
    products
WHERE
    details->'tags' ? 'mobile';

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.

UPDATE products
SET details = details || '{"warranty": "2 years"}'
WHERE name = 'Laptop';

-- Query updated data
SELECT
    name,
    details
FROM
    products;

-- Query result
--   name  |                                   details
-- --------+----------------------------------------------------------------------------
--  Laptop | {"brand": "Dell", "model": "XPS 13", "specs": {"ram": "16GB", "storage": "1TB SSD"}, "warranty": "2 years"}

Aggregating JSONB data

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

SELECT
    details->>'brand' AS brand,
    COUNT(*) AS count
FROM
    products
GROUP BY
    details->>'brand';

-- Query result
--  brand  | count
-- --------+-------
--  Dell   |     1
--  Apple  |     1

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.

-- Create GIN index
CREATE INDEX idx_products_details_features ON products USING GIN ((details->'tags'));

-- Query products with specific features using the index
SELECT
    name,
    details
FROM
    products
WHERE
    details->'tags' ? 'electronics';

-- Query result
--   name      |                                   details
-- ------------+----------------------------------------------------------------------------
--  Smartphone | {"brand": "Apple", "model": "iPhone 12", "tags": ["electronics", "mobile", "new"]}

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.

-- Create B-tree index
CREATE INDEX idx_products_details_model ON products ((details->>'model'));

-- Query using the index
SELECT
    name,
    details
FROM
    products
WHERE
    details->>'model' = 'XPS 13';

-- Query result
--   name  |                                   details
-- --------+----------------------------------------------------------------------------
--  Laptop | {"brand": "Dell", "model": "XPS 13", "features": ["Touchscreen", "Backlit Keyboard"], "specs": {"ram": "16GB", "storage": "1TB SSD"}, "warranty": "2 years"}

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

DEV Community

Mastering PostgreSQL JSONB type in one article

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

Top comments (0)

Read next

In Excel, Align Every Two Columns to Present Them While Setting Missing Values as 0

Asynchronous JavaScript: Practical Tips for Better Code

Data API for Amazon Aurora Serverless v2 with AWS SDK for Java - Part 7 Data API meets SnapStart

Understanding the Importance of Code Plagiarism Checkers

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

Read next

In Excel, Align Every Two Columns to Present Them While Setting Missing Values as 0

Asynchronous JavaScript: Practical Tips for Better Code

Data API for Amazon Aurora Serverless v2 with AWS SDK for Java - Part 7 Data API meets SnapStart

Understanding the Importance of Code Plagiarism Checkers

Using `@>` operator for containment queries