mirror of
https://github.com/supabase/supabase.git
synced 2026-09-09 11:30:17 +08:00
94bc3f8d07
<!-- This is an auto-generated comment: release notes by coderabbit.ai --> ## Summary by CodeRabbit * **Documentation** * Added C# examples across API, authentication, database, Realtime, and Storage guides. * Expanded authentication coverage for passwordless, phone, anonymous, identity linking, SSO, sign-out, and social login providers. * Added database examples for queries, functions, joins, JSON, arrays, search, PostGIS, and custom schemas. * Added Realtime examples for broadcasts, presence, subscriptions, and database changes. * Added Storage examples for buckets, uploads, downloads, transformations, CDN purging, and resumable transfers. * Included C# error-handling guidance and relevant reference links. <!-- end of auto-generated comment: release notes by coderabbit.ai -->
593 lines
17 KiB
Plaintext
593 lines
17 KiB
Plaintext
---
|
|
id: 'json'
|
|
title: 'Managing JSON and unstructured data'
|
|
description: 'Using the JSON data type in Postgres.'
|
|
subtitle: 'Using the JSON data type in Postgres.'
|
|
tocVideo: 'nxeUiRz4G-M'
|
|
---
|
|
|
|
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:
|
|
|
|
<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](/dashboard/project/_/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](/dashboard/project/_/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:
|
|
|
|
{/* supa-mdx-lint-disable Rule003Spelling */}
|
|
|
|
| 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."}` |
|
|
|
|
{/* supa-mdx-lint-enable Rule003Spelling */}
|
|
|
|
</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>
|
|
<$Show if="sdk:csharp">
|
|
<TabPanel id="csharp" label="C#">
|
|
|
|
```c#
|
|
[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; }
|
|
}
|
|
```
|
|
|
|
```c#
|
|
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);
|
|
```
|
|
|
|
</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](https://www.postgresql.org/docs/current/functions-json.html). 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>
|
|
<$Show if="sdk:csharp">
|
|
<TabPanel id="csharp" label="C#">
|
|
|
|
```c#
|
|
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();
|
|
```
|
|
|
|
</TabPanel>
|
|
</$Show>
|
|
<TabPanel id="result" label="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 |
|
|
|
|
</TabPanel>
|
|
</Tabs>
|
|
|
|
## Validating JSON data
|
|
|
|
Supabase provides the [`pg_jsonschema` extension](/docs/guides/database/extensions/pg_jsonschema) that adds the ability to validate `json` and `jsonb` data types against [JSON Schema](https://json-schema.org/) 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
|
|
|
|
- [Postgres: JSON Functions and Operators](https://www.postgresql.org/docs/current/functions-json.html)
|
|
- [Postgres JSON types](https://www.postgresql.org/docs/current/datatype-json.html)
|