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.
Learn how to manage unstructured data through JSON and JSONB columns: supabase.com/docs/guides/data...
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):
• FASTEST way to host a ...
▶ Generate column values in Postgres with Functions, Triggers, default values and RLS
: • Generate column values...
▶ Create PostgreSQL Functions with Supabase: • Create PostgreSQL Func...
👇 Learn more about Supabase 👇
🕸 Website: supabase.com/
🏁 Get started: app.supabase.com/
📄 Docs: supabase.com/docs
🔔 Subscribe for more tutorials and feature updates from Supabase: / @supabase
📱 Connect with Us:
🐙 Github: www.github.com/supabase
💬 Discord: www.discord.supabase.com/
🐦 Twitter: / supabase
▶ Instagram (follow for memes): / supabasecom
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 👇

[@user-df1zf9ti2w]

Clear explanation, appropriate examples, looking forward to an example explaining the use of JSONB in a chat session application.

[@Jebusankel]

Where I think supabase really shines is using postgres json functions like json_agg() and row_to_json() to create nested structures.

[@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.

[@dshukertjr]

User preferences are a great use case for using JSONB columns!

[@planetmall2]

This was awesome!

[@jaany]

Great video!

[@xberna8156]

Nice explanation, thank you!

[@dshukertjr]

Glad to hear you found it helpful!

[@cariyaputta]

Thanks.

[@thestereokid6073]

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?

[@vengateshm2122]

Seems we can wrap no sql into sql world.

[@Supabase]

Best of both ❤️

[@jannickpedersen4620]

NextJS14 + Supabase + ShadCN + TailwindCSS = The Big Guy

[@nicolascalderon9366]

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

[@Supabase]

With proper indexes, the difference in speed of querying data from a joined table and jsonb column are negligible. Writing is the same, generally the performance difference should be negligible. You should choose whether to use JSONB or not depending on wha tthe data could look like. If you want to be a bit more flexible with the schema of the data, you can choose to use JSONB.

[@jonathangamble]

can you fo joins?

[@dshukertjr]

Yes, you can join a table with another table using JSON values too!

[@emmanuelchucks]

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

[@dshukertjr]

I swear nothing was AI generated 😂 Maybe the bad sound quality at the intro and outro are making you feel that way. I will try to improve it on the next recording!

[@indiechatofficial]

@@dshukertjr The sound quality is really good. I think he just means your voice is so peaceful and well-measured that it seems as if an A.I. super intelligence is responsible for it.

[@tylershukert3621]

@@indiechatofficial That is so sweet of you!