Managing JSON and unstructured data

Postgres supports storing and querying unstructured data.

JSON vs JSONB

Postgres supports two types of JSON columns: json (stored as a string) and jsonb (stored as a binary). The recommended type is jsonb for almost all cases.

json stores an exact copy of the input text. Database functions must reparse the content on each execution.
jsonb stores database in a decomposed binary format. While this makes it slightly slower to input due to added conversion overhead, it is significantly faster to process, since no reparsing is needed.

When to use JSON/JSONB

Generally you should use a jsonb column when you have data that is unstructured or has a variable schema. For example, if you wanted to store responses for various webhooks, you might not know the format of the response when creating the table. Instead, you could store the payload as a jsonb object in a single column.

Don't go overboard with json/jsonb columns. They are a useful tool, but most of the benefits of a relational database come from the ability to query and join structured data, and the referential integrity that brings.

Create JSONB columns

json/jsonb is another "data type" for Postgres columns. You can create a jsonb column in the same way you would create a text or int column:

SQL

Dashboard

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

Go to the Table Editor page in the Dashboard.
Click New Table and create a table called books.
Include a primary key with the following properties and click save:

Name: id
- Type: int8
- Default value: Automatically generate as identity
title column
- Name: title
- Type: text
author column
- Name: author
- Type: text
metadata column
- Name: metadata
- Type: jsonb

Inserting JSON data

You can insert JSON data in the same way that you insert any other data. The data must be valid JSON.

SQL

Dashboard

JavaScript

Dart

Swift

Kotlin

Python

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]}'
  );

Go to the Table Editor page in the Dashboard.
Select the books table in the sidebar.
Click + Insert row and add 5 rows with the following properties:

id	title	author	metadata
1	The Poky Little Puppy	Janette Sebring Lowrey	`json {"ages":[3,6],"price":5.95,"description":"Puppy is slower than other, bigger animals."}`
2	The Tale of Peter Rabbit	Beatrix Potter	`json {"ages":[2,5],"price":4.49,"description":"Rabbit eats some vegetables."}`
3	Tootle	Gertrude Crampton	`json {"ages":[2,5],"price":3.99,"description":"Little toy train has big dreams."}`
4	Green Eggs and Ham	Dr. Seuss	`json {"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	`json {"ages":[10,99],"price":24.95,"description":"Fourth year of school starts, big drama ensues."}`

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],
    },
  },
])

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],
    },
  },
]);

Supabase Swift provides a convenience AnyJSON type.

struct Book {
    val title: String,
    val author: String,
    val metadata: [String: AnyJSON]
}

try await supabase.from("books")
  .insert(
    [
      Book(
        title: "The Poky Little Puppy",
        author: "Janette Sebring Lowrey",
        metadata: [
          "description": "Puppy is slower than other, bigger animals.",
          "price": 5.95,
          "ages": [3, 6]
        ]
      ),
      Book(
        title: "Tale of Peter Rabbit",
        author: "Beatrix Potter",
        metadata: [
          "description": "Rabbit eats some vegetables.",
          "price": 4.49,
          "ages": [2, 5]
        ]
      ),
      Book(
        title: "Tootle",
        author: "Gertrude Crampton",
        metadata: [
          "description": "Little toy train has big dreams.",
          "price": 3.99,
          "ages": [2, 5]
        ]
      ),
      Book(
        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]
        ]
      ),
      Book(
        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]
        ]
      )
    ]
  )

@Serializable
data class BookMetadata(
    val description: String,
    val price: Double,
    val ages: List<Int>
)

@Serializable
data class Book(
    val title: String,
    val author: String,
    val metadata: BookMetadata
)

val data = supabase.from("books").insert(listOf(
    Book("The Poky Little Puppy", "Janette Sebring Lowrey", BookMetadata("Puppy is slower than other, bigger animals.", 5.95, listOf(3, 6))),
    Book("Tale of Peter Rabbit", "Beatrix Potter", BookMetadata("Rabbit eats some vegetables.", 4.49, listOf(2, 5))),
    Book("Tootle", "Gertrude Crampton", BookMetadata("Little toy train has big dreams.", 3.99, listOf(2, 5))),
    Book("Green Eggs and Ham", "Dr. Seuss", BookMetadata("Sam has changing food preferences and eats unusually colored food.", 7.49, listOf(4, 8))),
    Book("Harry Potter and the Goblet of Fire", "J.K. Rowling", BookMetadata("Fourth year of school starts, big drama ensues.", 24.95, listOf(10, 99)))
))

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],
    },
  },
]).execute()

[Table("books")]
class Book : BaseModel
{
    [Column("title")]
    public string Title { get; set; }

    [Column("author")]
    public string Author { get; set; }

    [Column("metadata")]
    public BookMetadata Metadata { get; set; }
}

class BookMetadata
{
    [JsonProperty("description")]
    public string Description { get; set; }

    [JsonProperty("price")]
    public double Price { get; set; }

    [JsonProperty("ages")]
    public List<int> Ages { get; set; }
}

var books = new List<Book>
{
    new Book { Title = "The Poky Little Puppy", Author = "Janette Sebring Lowrey", Metadata = new BookMetadata { Description = "Puppy is slower than other, bigger animals.", Price = 5.95, Ages = new List<int> { 3, 6 } } },
    new Book { Title = "The Tale of Peter Rabbit", Author = "Beatrix Potter", Metadata = new BookMetadata { Description = "Rabbit eats some vegetables.", Price = 4.49, Ages = new List<int> { 2, 5 } } },
    new Book { Title = "Tootle", Author = "Gertrude Crampton", Metadata = new BookMetadata { Description = "Little toy train has big dreams.", Price = 3.99, Ages = new List<int> { 2, 5 } } },
    new Book { Title = "Green Eggs and Ham", Author = "Dr. Seuss", Metadata = new BookMetadata { Description = "Sam has changing food preferences and eats unusually colored food.", Price = 7.49, Ages = new List<int> { 4, 8 } } },
    new Book { Title = "Harry Potter and the Goblet of Fire", Author = "J.K. Rowling", Metadata = new BookMetadata { Description = "Fourth year of school starts, big drama ensues.", Price = 24.95, Ages = new List<int> { 10, 99 } } }
};

await supabase.From<Book>().Insert(books);

Query JSON data

Querying JSON data is similar to querying other data, with a few other features to access nested values.

Postgres support a range of JSON functions and operators. For example, the -> operator returns values as jsonb data. If you want the data returned as text, use the ->> operator.

SQL

JavaScript

Swift

Kotlin

Python

Result

select
  title,
  metadata ->> 'description' as description, -- returned as text
  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
  `)

try await supabase
  .from("books")
  .select(
    """
      title,
      description:  metadata->>description,
      price:        metadata->price,
      low_age:      metadata->ages->0,
      high_age:     metadata->ages->1
    """
  )
  .execute()

val data = supabase.from("books").select(Columns.raw("""
    title,
    description: metadata->>description,
    price: metadata->price,
    low_age: metadata->ages->0,
    high_age: metadata->ages->1
""".trimIndent()))

data = supabase.from_('books').select("""
  title,
  description: metadata->>description,
  price: metadata->price,
  low_age: metadata->ages->0,
  high_age: metadata->ages->1
"""
).execute()

var result = await supabase
  .From<Book>()
  .Select("title, description:metadata->>description, price:metadata->price, low_age:metadata->ages->0, high_age:metadata->ages->1")
  .Get();

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

Validating JSON data

Supabase provides the pg_jsonschema extension that adds the ability to validate json and jsonb data types against JSON Schema documents.

Once you have enabled the extension, you can add a "check constraint" to your table to validate the JSON data:

create table customers (
  id serial primary key,
  metadata json
);

alter table customers
add constraint check_metadata check (
  json_matches_schema(
    '{
        "type": "object",
        "properties": {
            "tags": {
                "type": "array",
                "items": {
                    "type": "string",
                    "maxLength": 16
                }
            }
        }
    }',
    metadata
  )
);