rails, postgres, database,

json and jsonb - Postgresql

Swapnil Gourshete Swapnil Gourshete Follow Dec 10, 2019 · 1 min read
Share this

Postgresql provides a data-type jsonb to save data from JSON format. There are two ways for it - using json and jsonb. This article will clarify difference in short terms.

json and jsonb are very similar to each other. The key difference is - jsonb is binary representation of json - as per postgresql documentation.

  • Pros - jsonb
  1. Improved efficiency
  2. Postgresql provides query interface for these types. So a direct query for any key in column can be made.
  3. Simple database schema
  • Cons - jsonb
  1. Slight overhead to convert into binary form.
  2. Aggregate queries are slower (due to lack of statistics).
  3. Due to large table footprints may take large disk space.

Querying json field

  1. Creating table
    create table foo (id serial NOT NULL primary key, metadata json)
    
  2. inserting test data
    insert into foo(metadata) values ('{ "property": "bar1" }'), ('{ "property": "bar2" }')
    
  3. fetching data from table foo
    select * from foo
    

  4. selecting json field
    select id, metadata ->'property' as property from foo
    

Cheers!!!


References -

  • Official postgresql doc - link

  • postgresql tutorial blog - link


Swapnil Gourshete
Written by Swapnil Gourshete Follow
Hi I am Swapnil, a Software Engineer and computer science enthusiastic