ContextQMD
Back to Supabase

Managing JSON and unstructured data

apps/docs/content/guides/database/json.mdx

1.26.0414.7 KB
Original Source

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 just another "data type" for Postgres columns. You can create a jsonb column in the same way you would create a text or int column:

<Tabs scrollable size="small" type="underlined" defaultActiveId="sql" queryGroup="database-method"

<TabPanel id="sql" label="SQL">
sql
create table books (
  id serial primary key,
  title text,
  author text,
  metadata jsonb
);
</TabPanel> <TabPanel id="dashboard" label="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 and click save:
  • Name: id
    • Type: int8
    • Default value: Automatically generate as indentity
  • title column
    • Name: title
    • Type: text
  • author column
    • Name: author
    • Type: text
  • metadata column
    • Name: metadata
    • Type: jsonb
</TabPanel> </Tabs>

Inserting JSON data

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

<Tabs scrollable size="small" type="underlined" defaultActiveId="sql" queryGroup="database-method"

<TabPanel id="sql" label="SQL">
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]}'
  );
</TabPanel> <TabPanel id="dashboard" label="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:
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."}
</TabPanel> <TabPanel id="js" label="JavaScript">
js
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],
    },
  },
])
</TabPanel> <$Show if="sdk:dart"> <TabPanel id="dart" label="Dart">
dart
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],
    },
  },
]);
</TabPanel> </$Show> <$Show if="sdk:swift"> <TabPanel id="swift" label="Swift">

Supabase Swift provides a convenience AnyJSON type.

swift
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]
        ]
      )
    ]
  )
</TabPanel> </$Show> <$Show if="sdk:kotlin"> <TabPanel id="kotlin" label="Kotlin">
kotlin
@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
)
kotlin
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)))
))
</TabPanel> </$Show> <$Show if="sdk:python"> <TabPanel id="python" label="Python">
python
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()
</TabPanel> </$Show> </Tabs>

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.

<Tabs scrollable size="small" type="underlined" defaultActiveId="sql" queryGroup="language"

<TabPanel id="sql" label="SQL">
sql
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;
</TabPanel> <TabPanel id="js" label="JavaScript">
js
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
  `)
</TabPanel> <$Show if="sdk:swift"> <TabPanel id="swift" label="Swift">
swift
try await supabase
  .from("books")
  .select(
    """
      title,
      description:  metadata->>description,
      price:        metadata->price,
      low_age:      metadata->ages->0,
      high_age:     metadata->ages->1
    """
  )
  .execute()
</TabPanel> </$Show> <$Show if="sdk:kotlin"> <TabPanel id="kotlin" label="Kotlin">
kotlin
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()))
</TabPanel> </$Show> <$Show if="sdk:python"> <TabPanel id="python" label="Python">
python
data = supabase.from_('books').select("""
  title,
  description: metadata->>description,
  price: metadata->price,
  low_age: metadata->ages->0,
  high_age: metadata->ages->1
"""
).execute()
</TabPanel> </$Show> <TabPanel id="result" label="Result">
titledescriptionpricelow_agehigh_age
The Poky Little PuppyPuppy is slower than other, bigger animals.5.9536
The Tale of Peter RabbitRabbit eats some vegetables.4.4925
TootleLittle toy train has big dreams.3.9925
Green Eggs and HamSam has changing food preferences and eats unusually colored food.7.4948
Harry Potter and the Goblet of FireFourth year of school starts, big drama ensues.24.951099
</TabPanel> </Tabs>

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:

sql
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
  )
);

Resources