Using JSONB in PostgreSQL

#postgres #beginners #datascience #data

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.

Defining a Column

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

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:

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:

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:

-- 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:

-- 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.