Introduction to Postgres JSON support

jsonb is a PostgreSQL data type that stores JSON data in a binary format, allowing for efficient storage and retrieval. Unlike the json data type, jsonb supports indexing on specific elements, enabling faster query performance.

Table with JSON column

drop table if exists player_list;
create table player_list (
	id serial not null primary key,
    player_info jsonb not null
);

Insert some sample data

insert into player_list (player_info) values ('{
        "identifier": 1003,
        "first_name": "Hector",
        "last_name": "Bellerin",
        "team": "Arsenal",
        "position": "Defender",
        "image": "hectorbellerin.jpg"
    }');

insert into player_list (player_info) values ('    {
        "identifier": 1404,
        "first_name": "Marcos",
        "last_name": "Alonso",
        "team": "Chelsea",
        "position": "Defender",
        "image": "marcosalonso.jpg"
    }');

Query JSON data

Select all data

select player_info from player_list;

Get a single field from JSON value

Fields from JSON object can be accessed using the special operator ->>.

select player_info->>'first_name' from player_list;

select player_info->>'first_name' from player_list where player_info->>'team'='Chelsea';

Try some more queries for practice. How about find number of players in Chelsea football club?

Head over to the SQL editor tab Editor