Create jsonb column syntax
jsonb is a data type for Postgres columns created in the same way as text or int columns. Example: create table books (id serial primary key, title text, author text, metadata jsonb);
Supabase · Database · all subjects
json
9 notes, read out of this brain and free to use. Each one was extracted from a source and is re-checked against its exam.
json vs jsonb data types
Postgres supports two types of JSON columns: json (stored as a string) and jsonb (stored as a binary). The json type stores an exact copy of the input text and database functions must reparse the content on each execution. The jsonb type stores data in a decomposed binary format, which makes it slightly slower to input due to added conversion overhead, but significantly faster to process since no reparsing is needed. The recommended type is jsonb for almost all cases.
When to use json/jsonb columns
Use a jsonb column when you have data that is unstructured or has a variable schema. For example, storing responses for various webhooks where the format is not known when creating the table. However, avoid overusing json/jsonb columns because most benefits of a relational database come from the ability to query and join structured data and the referential integrity that brings.
JSON operators in Postgres queries
The -> operator returns values as jsonb data. The ->> operator returns data as text. To access nested values, chain the operators. For example, metadata -> 'price' returns the price as jsonb, metadata ->> 'description' returns description as text, and metadata -> 'ages' -> 0 accesses the first element of an ages array.
Query JSON with supabase-js select
When querying JSON data with supabase-js, use the select() method with JSON operators in the column specification. Example: 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`)
Insert JSON data with supabase-js
Insert JSON data by passing objects with the jsonb field as a regular JavaScript object. The SDK handles serialization automatically. Example: 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] } }])
Validate JSON data with pg_jsonschema extension
Supabase provides the pg_jsonschema extension that adds the ability to validate json and jsonb data types against JSON Schema documents. Once enabled, add a check constraint to your table using json_matches_schema() to validate JSON data. Example: alter table customers add constraint check_metadata check (json_matches_schema('{"type": "object", "properties": {"tags": {"type": "array", "items": {"type": "string", "maxLength": 16}}}}', metadata))
Insert JSON data with SQL
Insert JSON data as valid JSON strings in SQL. Example: 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]}')
Query JSON data with SQL
Query JSON data in SQL using JSON operators. Example: select title, metadata ->> 'description' as description, metadata -> 'price' as price, metadata -> 'ages' -> 0 as low_age, metadata -> 'ages' -> 1 as high_age from books;