Managing JSON and unstructured data
Using the JSON data type in Postgres.
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:
_10create table books (_10 id serial primary key,_10 title text,_10 author text,_10 metadata jsonb_10);
Inserting JSON data
You can insert JSON data in the same way that you insert any other data. The data must be valid JSON.
_28insert into books_28 (title, author, metadata)_28values_28 (_28 'The Poky Little Puppy',_28 'Janette Sebring Lowrey',_28 '{"description":"Puppy is slower than other, bigger animals.","price":5.95,"ages":[3,6]}'_28 ),_28 (_28 'The Tale of Peter Rabbit',_28 'Beatrix Potter',_28 '{"description":"Rabbit eats some vegetables.","price":4.49,"ages":[2,5]}'_28 ),_28 (_28 'Tootle',_28 'Gertrude Crampton',_28 '{"description":"Little toy train has big dreams.","price":3.99,"ages":[2,5]}'_28 ),_28 (_28 'Green Eggs and Ham',_28 'Dr. Seuss',_28 '{"description":"Sam has changing food preferences and eats unusually colored food.","price":7.49,"ages":[4,8]}'_28 ),_28 (_28 'Harry Potter and the Goblet of Fire',_28 'J.K. Rowling',_28 '{"description":"Fourth year of school starts, big drama ensues.","price":24.95,"ages":[10,99]}'_28 );
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.
_10select_10 title,_10 metadata ->> 'description' as description, -- returned as text_10 metadata -> 'price' as price,_10 metadata -> 'ages' -> 0 as low_age,_10 metadata -> 'ages' -> 1 as high_age_10from books;
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:
_23create table customers (_23 id serial primary key,_23 metadata json_23);_23_23alter table customers_23add constraint check_metadata check (_23 json_matches_schema(_23 '{_23 "type": "object",_23 "properties": {_23 "tags": {_23 "type": "array",_23 "items": {_23 "type": "string",_23 "maxLength": 16_23 }_23 }_23 }_23 }',_23 metadata_23 )_23);