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

Dashboard

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

SQL

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

Insert data into the table

Dashboard

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:

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

SQL

insert into books
  (title, author, metadata)
values
  (
    'The Poky Little Puppy',
    'Janette Sebring Lowrey',
    '{"description":"Puppy is slower than other, bigger animals.","price":5.95,"ages":[3,6]}'
  ),
  (
    'The Tale of Peter Rabbit',
    'Beatrix Potter',
    '{"description":"Rabbit eats some vegetables.","price":4.49,"ages":[2,5]}'
  ),
  (
    'Tootle',
    'Gertrude Crampton',
    '{"description":"Little toy train has big dreams.","price":3.99,"ages":[2,5]}'
  ),
  (
    'Green Eggs and Ham',
    'Dr. Seuss',
    '{"description":"Sam has changing food preferences and eats unusually colored food.","price":7.49,"ages":[4,8]}'
  ),
  (
    'Harry Potter and the Goblet of Fire',
    'J.K. Rowling',
    '{"description":"Fourth year of school starts, big drama ensues.","price":24.95,"ages":[10,99]}'
  );

JavaScript

const { data, error } = await supabase.from('books').insert([
  {
    title: 'The Poky Little Puppy',
    author: 'Janette Sebring Lowrey',
    metadata: {
      description: 'Puppy is slower than other, bigger animals.',
      price: 5.95,
      ages: [3, 6],
    },
  },
  {
    title: 'The Tale of Peter Rabbit',
    author: 'Beatrix Potter',
    metadata: {
      description: 'Rabbit eats some vegetables.',
      price: 4.49,
      ages: [2, 5],
    },
  },
  {
    title: 'Tootle',
    author: 'Gertrude Crampton',
    metadata: {
      description: 'Little toy train has big dreams.',
      price: 3.99,
      ages: [2, 5],
    },
  },
  {
    title: 'Green Eggs and Ham',
    author: 'Dr. Seuss',
    metadata: {
      description:
        'Sam has changing food preferences and eats unusually colored food.',
      price: 7.49,
      ages: [4, 8],
    },
  },
  {
    title: 'Harry Potter and the Goblet of Fire',
    author: 'J.K. Rowling',
    metadata: {
      description: 'Fourth year of school starts, big drama ensues.',
      price: 24.95,
      ages: [10, 99],
    },
  },
])

View the data

SQL

select *
from books;

JavaScript

const { data, error } = await supabase.from('books').select('*')
console.log(JSON.stringify(data, null, 2))

Result

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

The data as it appears here has the JSONB fields in a different order than when inserted. As mentioned earlier, data is parsed as its inserted when using the JSONB format.

Query the JSONB data

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

SQL

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

JavaScript

const { data, error } = await supabase
  .from('books')
  .select(
    'title,description:metadata->description,price:metadata->price,low_age:metadata->ages->0,high_age:metadata->ages->1'
  )
console.log(JSON.stringify(data, null, 2))

Result

title	description	price	low_age	high_age
The Poky Little Puppy	Puppy is slower than other, bigger animals.	5.95	3	6
The Tale of Peter Rabbit	Rabbit eats some vegetables.	4.49	2	5
Tootle	Little toy train has big dreams.	3.99	2	5
Green Eggs and Ham	Sam has changing food preferences and eats unusually colored food.	7.49	4	8
Harry Potter and the Goblet of Fire	Fourth year of school starts, big drama ensues.	24.95	10	99

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

• Supabase JS Client
• PostgreSQL: JSON Functions and Operators
• PostgreSQL JSON types