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.
- SQL
- UI
create table books (
id serial primary key,
title text,
author text,
metadata jsonb
);
Create the table:
1. Go to the Table Editor
2. Click "New Table"
3. Name the table "books"
4. Include a primary key with the following properties:
- Name: "id"
- Type: "int8"
- Default value: "Automatically generate as indentity"
5. Click "Save"
Click "New Column" and 3 new columns with the following properties:
1. title column:
- Name: "title"
- Type: "text"
2. author column:
- Name: "author"
- Type: "text"
3. metadata column:
- Name: "metadata"
- Type: "jsonb"
Insert data into the table.
- Data
- SQL
- UI
- JS
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."} |
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]}'
);
- click `Table Editor`
- click `books`
- click `+ Insert Row`
- `title`: `The Poky Little Puppy`
- `author`: 'Janette Sebring Lowrey'
- `metadata`: {"description":"Puppy is slower than other, bigger animals.","price":5.95,"ages":[3,6]}
- click `Save`
- click `+ Insert Row`
- `title`: `The Tale of Peter Rabbit`
- `author`: 'Beatrix Potter'
- `metadata`: {"ages":[2,5],"price":4.49,"description":"Rabbit eats some vegetables."}
- click `Save`
- click `+ Insert Row`
- `title`: `Tootle`
- `author`: 'Gertrude Crampton'
- `metadata`: {"ages":[2,5],"price":3.99,"description":"Little toy train has big dreams."}
- click `Save`
- click `+ Insert Row`
- `title`: `Green Eggs and Ham`
- `author`: 'Dr. Seuss'
- `metadata`: {"ages":[4,8],"price":7.49,"description":"Sam has changing food preferences and eats unusually colored food."}
- click `Save`
- click `+ Insert Row`
- `title`: `Harry Potter and the Goblet of Fire`
- `author`: 'J.K. Rowling'
- `metadata`: {"ages":[10,99],"price":24.95,"description":"Fourth year of school starts, big drama ensues."}
- click `Save`
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
- JS
- Data
select *
from books;
const { data, error } = await supabase.from('books').select('*')
console.log(JSON.stringify(data, null, 2))
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."} |
NOTICE: The data as it appears here will have the JSONB
fields in a different order than when we inserted them.
As we said earlier about the differences between the JSON
and JSONB
fields:
When you use the
JSONB
format, the data is parsed when it's put into the database...
Query the JSONB
data.
Select title, description, price, and age range for each book.
- SQL
- JS
- Results
select
title,
metadata -> 'description' AS description,
metadata -> 'price' as price,
metadata -> 'ages' -> 0 as low_age,
metadata -> 'ages' -> 1 as high_age
from
books;
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))
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 |
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)