Skip to main content

JSON

PostgreSQL supports JSON functions and operators which gives flexibility when storing data inside a database column.

PostgreSQL supports two types of JSON columns: JSON and JSONB.

The recommended type is JSONB for almost all cases. When you use the JSONB format, the data is parsed when it's put into the database so it's faster when querying and also it can be indexed.

Steps#

Create a table with a JSON column.#

create table books (
id serial primary key,
title text,
author text,
metadata jsonb
);

Insert data into the table.#

idtitleauthormetadata
1The Poky Little PuppyJanette Sebring Lowrey{"ages":[3,6],"price":5.95,"description":"Puppy is slower than other, bigger animals."}
2The Tale of Peter RabbitBeatrix Potter{"ages":[2,5],"price":4.49,"description":"Rabbit eats some vegetables."}
3TootleGertrude Crampton{"ages":[2,5],"price":3.99,"description":"Little toy train has big dreams."}
4Green Eggs and HamDr. Seuss{"ages":[4,8],"price":7.49,"description":"Sam has changing food preferences and eats unusually colored food."}
5Harry Potter and the Goblet of FireJ.K. Rowling{"ages":[10,99],"price":24.95,"description":"Fourth year of school starts, big drama ensues."}

View the data.#

select *
from books;

Query the JSONB data.#

Select title, description, price, and age range for each book.#

select
title,
metadata -> 'description' AS description,
metadata -> 'price' as price,
metadata -> 'ages' -> 0 as low_age,
metadata -> 'ages' -> 1 as high_age
from
books;

Data Types#

One last thing to note: the -> operator returns JSONB data. If you want TEXT/STRING data returned, you need to use the ->> operator.

  • metadata -> 'description' (this returns a JSON object)
  • metadata ->> 'description' (this returns STRING/TEXT data)

Resources#