How to Query a Postgres JSONB Column

Introduction

PostgreSQL allows you to store and query both JSON and JSONB data in tables. When you use the JSONB data type, you’re actually using the binary representation of JSON data. Postgres can process JSONB data much faster than standard JSON data, which translates to big gains in performance. In this article, we’ll talk about how to query a Postgres JSONB column and provide some examples to show you how it’s done.

The PostgreSQL JSONB data type

As we mentioned above, the JSONB data type is the binary form of the JSON data type. The two data types used for storing JSON data come with a number of handy built-in functions that make it easy to query and manipulate data.

Create a table with a JSONB column in PostgreSQL

Before we begin our discussion of the functions and operators available for the PostgreSQL JSONB data type, let’s create a table that we can use in our examples:

1
2
3
CREATE TABLE cars(
    id SERIAL PRIMARY KEY,
    cars_info JSONB NOT NULL);

Inserting JSON data in the table

To insert data into our PostgreSQL table, we’ll use the following SQL statement:

1
2
3
4
INSERT INTO cars(cars_info)
VALUES('{"brand": "Toyota", "color": ["red", "black"], "price": 285000, "sold": true}'),
      ('{"brand": "Honda", "color": ["blue", "pink"], "price": 25000, "sold": false}'),
      ('{"brand": "Mitsubishi", "color": ["black", "gray"], "price": 604520, "sold": true}');

If we use a SELECT statement to view the contents of this table, the result will look like this:

1
2
3
4
5
 id |                                     cars_info                                      
----+------------------------------------------------------------------------------------
  1 | {"sold": true, "brand": "Toyota", "color": ["red", "black"], "price": 285000}
  2 | {"sold": false, "brand": "Honda", "color": ["blue", "pink"], "price": 25000}
  3 | {"sold": true, "brand": "Mitsubishi", "color": ["black", "gray"], "price": 604520}

Using the JSONB native operators

Using the -> operator in a query returns a JSONB value:

1
SELECT cars_info -> 'brand' AS car_name FROM cars;

The result will look like the following:

1
2
3
4
5
   car_name  
--------------
 "Toyota"
 "Honda"
 "Mitsubishi"

NOTE: Keep in mind that there’s also a ->> operator that can be used with JSON data. The -> operator will return a JSON object while the ->> operator returns the JSONB in the form of text.

PostgreSQL JSONB query

We can use the WHERE clause in a query to filter JSONB values. Here’s an example:

1
SELECT * FROM cars WHERE cars_info -> 'sold' = 'true';

The results are filtered based on the value of a specific JSON key:

1
2
3
4
 id |                                     cars_info                                      
----+------------------------------------------------------------------------------------
  1 | {"sold": true, "brand": "Toyota", "color": ["red", "black"], "price": 285000}
  3 | {"sold": true, "brand": "Mitsubishi", "color": ["black", "gray"], "price": 604520}

PostgreSQL JSONB functions

There are a number of built-in functions that can be used with JSONB data. Let’s look at an example of a query that makes use of the jsonb_each function:

1
SELECT jsonb_each('{"brand": "Toyota", "sold": "true"}'::jsonb);

The output takes the highest-level JSON document and expands it into a set of key-value pairs:

1
2
3
4
      jsonb_each      
----------------------
 (sold,"""true""")
 (brand,"""Toyota""")

Next, let’s look at an example using the jsonb_object_keys function:

1
SELECT jsonb_object_keys( '{"brand": "Mitsubishi", "sold": true}'::jsonb );

This function retrieves just the keys of the JSON document:

1
2
3
4
 jsonb_object_keys
-------------------
 sold
 brand

The following example uses the jsonb_extract_path function:

1
SELECT jsonb_extract_path('{"brand": "Honda", "sold": false}'::jsonb, 'brand');

The output contains the JSON object extracted by path:

1
2
3
 jsonb_extract_path
--------------------
 "Honda"

Our final example demonstrates the jsonb_pretty function:

1
SELECT jsonb_pretty('{"brand": "Honda", "sold": false}'::jsonb);

We can see that the output retrieves a simplified description that works for computer use. This easy-to-read format is a good choice if you want your JSON documents to be printed for human consumption.

1
2
3
4
5
6
     jsonb_pretty    
----------------------
 {                   +
     "sold": false,  +
     "brand": "Honda"+
 }

Conclusion

Storing JSONB data in your PostgreSQL tables offers you the best of both worlds: the flexibility and efficiency of a NoSQL database paired with all the benefits of a relational database. In this article, we showed you the different ways you can query a Postgres JSONB column using various operators and functions. With our examples to guide you, you’ll be able to work with JSONB data in your own PostgreSQL database.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.