Home

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.

Create a table with a JSON column#

  1. Go to the Table Editor page in the Dashboard.
  2. Click New Table and create a table called books.
  3. Include a primary key with the following properties:
    • Name: id
    • Type: int8
    • Default value: Automatically generate as indentity
  4. Click Save.
  5. Click New Column and add 3 columns with the following properties:
    • title column
      • Name: title
      • Type: text
    • author column
      • Name: author
      • Type: text
    • metadata column
      • Name: metadata
      • Type: jsonb

Insert data into the table#

  1. Go to the Table Editor page in the Dashboard.
  2. Select the books table in the sidebar.
  3. Click + Insert row and add 5 rows with the following properties:
idtitleauthormetadata
1The Poky Little PuppyJanette Sebring Lowreyjson {"ages":[3,6],"price":5.95,"description":"Puppy is slower than other, bigger animals."}
2The Tale of Peter RabbitBeatrix Potterjson {"ages":[2,5],"price":4.49,"description":"Rabbit eats some vegetables."}
3TootleGertrude Cramptonjson {"ages":[2,5],"price":3.99,"description":"Little toy train has big dreams."}
4Green Eggs and HamDr. Seussjson {"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. Rowlingjson {"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 the 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;

Note that the -> operator returns JSONB data. If you want TEXT/STRING data returned, use the ->> operator.

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

Resources#

Need some help?

Not to worry, our specialist engineers are here to help. Submit a support ticket through the Dashboard.