How to store and query JSON data in Postgres

JSON and JSONB type allows you to store unstructured data in Postgres. In this video, Tyler Shukert walks you through how to store and query JSON data from your Postgres database.

Postgres is a relational database, but it also has support for JSON and JSONB-type columns, which allows developers to store unstructured data. Not only can they be flexible with the data to be stored, but there are many unique operators for querying JSON data flexibly. We take a deeper look into what the possibilities are with JSON columns on Postgres.

00:00 Intro
00:26 Querying a JSON value
00:50 Arrow notation to access the properties
01:57 Create a table with JSONB type column
02:54 JSONB-operator deep dive
03:19 ? filter for contains
03:42 ?& for array contains all
03:51 ?| for array contains any
03:57 JSON contains filter
04:49 Outro

💻 Videos to watch next:
▶ FASTEST way to host a Postgres database (2024):
▶ Generate column values in Postgres with Functions, Triggers, default values and RLS

👇 Learn more about Supabase 👇

📱 Connect with Us:

ABOUT SUPABASE:
Supabase is the open source Firebase alternative. Supabase provides a full Postgres database for every project with pgvector, backups, realtime, and more. Add and manage email and password, passwordless, OAuth, and mobile logins to your project through a suite of identity providers and APIs.

Build in a weekend, scale to millions.

#Supabase #AppDevelopment #RealtimeApps #DeveloperTools

[dshukertjr]

JSON columns in Postgres are so useful, but so are other data types. What is the next data type that you want us to go deep on? Let us know in the comment below 👇

[EnesKab]

A great use case for JSON in the SQL database can be a column which stores set of settings for something like custom styles. Let's say you have 3 styles that your user can customize and you want to store them in the database. For example, custom background color, font-size and font-weight. You can create a JSON object to store all these settings without having to create columns for each in a new table and create a relationship between the user. Also, if you create a JSON object for your user's settings, you can manipulate it way easier, add new styles in the future even if they are 20 more and do not need to worry about the performance, and best of all 👉🏻 All is possible without creating a mess in your db.

[jannickpedersen]

NextJS14 + Supabase + ShadCN + TailwindCSS = The Big Guy

[thestereokid]

how big can these objects go and still be performant?

I'm working on a system that has some really big json documents tracking interactions but we have migrated to a postgreSQL DB from a mongo DB and now we aren't sure if we can handle the big documents in our collections.

Any advice?

[vengateshm]

Seems we can wrap no sql into sql world.

[nicolascalderon]

How would performance impact the search and database writing when using jsonb vs regular columns?

[emmanuelchucks]

Is it just me or does the voice sound AI generated?