Using JSONB in PostgreSQL
Tags: postgres, beginners, datascience, data
Published: 2024-06-30 03:00:00

Introduction
------------

**JSONB**, short for **JSON Binary**, is a data type developed from the **JSON** data type and supported by **PostgreSQL** since version 9.2.

The key difference between **JSON** and **JSONB** lies in how they are stored. **JSONB** supports binary storage and resolves the limitations of the **JSON** data type by optimizing the insert process and supporting indexing.

If you want to know how to install PostgreSQL and learn some basic knowledge about it, check out [this article](https://howtodevez.blogspot.com/2024/03/installing-postgresql-with-docker.html).

Defining a Column
-----------------

The query below will create a table with a column of the JSONB data type, which is very simple:

```sql
CREATE TABLE table_name (
  id int,
  name text,
  info jsonb
);
```

Inserting Data
--------------

To insert data into a table with a JSONB column, enclose the content within single quotes ('') like this:

```sql
INSERT INTO table_name VALUES (1, 'name', '{"text": "text value", "boolean_vaule": true, "array_value": [1, 2, 3]}');
```

We can also insert into an array of objects in a similar way:

```sql
INSERT INTO table_name VALUES (1, 'name', '[1, "text", false]');
```

Query data
----------

To query data from a column with the JSONB data type, there are a few ways:

```sql
-- get all field data
SELECT info FROM table_name;

-- get specific field for json object
SELECT info->>'field_name' AS field FROM table_name;

-- select element from array (start from 1)
SELECT info[2] FROM table_name

-- compare value must be inside ''
SELECT * FROM table_name WHERE info->>'field_name' >= '20';

-- get rows have field exists value
SELECT count(*) FROM table_name WHERE info ? 'field_name';
```

Creating an Index
-----------------

As mentioned earlier, one of the key differences between JSON and JSONB is that JSONB supports creating indexes, which allows for faster data access when dealing with large amounts of data. Here's how you can create an index:

```sql
-- create index for info with field 'value'
CREATE INDEX index_name ON table_name ((info->>'value'));
```

To check the effectiveness of the Index, you should insert a large amount of data (around 10,000 records) to see the improvement in query speed before and after indexing.

Conclusion
----------

Through this article, I hope you have gained more understanding about JSONB and how to create, insert, and query JSONB data in PostgreSQL. 

**_See you again in the next articles. Happy coding!_**

**_If you found this content helpful, please visit [the original article on my blog](https://howtodevez.blogspot.com/2024/04/using-jsonb-in-postgresql.html) to support the author and explore more interesting content._**

<a href="https://howtodevez.blogspot.com/2024/03/sitemap.html" target="_blank" rel="noreferrer"><img src="https://img.shields.io/badge/Blogger-FF5722?style=for-the-badge&logo=blogger&logoColor=white" width="36" height="36" alt="Blogspot" /></a><a href="https://dev.to/chauhoangminhnguyen" target="_blank" rel="noreferrer"><img src="https://img.shields.io/badge/dev.to-0A0A0A?style=for-the-badge&logo=dev.to&logoColor=white" width="36" height="36" alt="Dev.to" /></a><a href="https://www.facebook.com/profile.php?id=61557154776384" target="_blank" rel="noreferrer"><img src="https://img.shields.io/badge/Facebook-1877F2?style=for-the-badge&logo=facebook&logoColor=white" width="36" height="36" alt="Facebook" /></a><a href="https://x.com/DavidNguyenSE" target="_blank" rel="noreferrer"><img src="https://img.shields.io/badge/X-000000?style=for-the-badge&logo=x&logoColor=white" width="36" height="36" alt="X" /></a>