SQL from zero · Chapter 26 of 37
Flexible data
By the end of this chapter you can read, filter, change and build JSON inside Postgres: one key, a key inside a key, or a whole API response. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: Postgres powers
- 5 tasks
- SQL
- In the all labs plan
The first 2 chapters of SQL from zero are free. This one comes with the all labs plan.
Why you need this
Meera's next slide asks "Who pays us?", and Rohan is planning a push campaign. Both answers live in users.metadata, a jsonb column that holds each member's settings: plan, theme, notifications, the bot flag. Every member has a different mix of keys, and tara has none at all.
With what you know so far, metadata is one value you can only compare whole, and no member's object is exactly {"plan": "pro"}. You could cast it to text and search with LIKE '%pro%', but that matches the letters anywhere, in any key or value, and the pattern breaks the day the text is printed differently. You need to ask about one key.
What you build: 5 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
pro-members.sql: members on the pro plan
List the members whose metadata says their plan is "pro". Every member counts, including members who left.
Return one column: username. Any order.
- Task 2
push-on.sql: members with push notifications on
List the members who have push notifications turned on: in their metadata, notifications is an object and its push key is true. Members whose push is false, who have notifications without a push key, or who have no notifications settings at all are not on. Every member counts, including members who left.
Return two columns: username and theme (the theme key from their metadata, as text; NULL when they never picked one). Any order.
- Task 3
media-sizes.sql: posts with a width, and their size
List every post whose metadata has a width key, with its author and its size. Every such post counts, deleted or not, and so do posts by members who left.
Return four columns: username (the post's author), type, width and height. width and height must be integers, not text. Any order.
- Task 4
meera-dark.sql: dark theme and the beta for meera
Change meera's metadata so that theme is "dark" and there is a new key beta set to true (the JSON value true, not the text "true"). Every other key she has stays exactly as it is, and no other member changes.
- Task 5 · interview question
Interview: anya-json.sql, anya and her posts as one JSON array
Return one row for anya, the way an API would send her and her posts. Leave out her deleted posts.
Return two columns: username and posts. posts is a JSON array with one object per post, oldest post first. Each object has exactly two keys: content (the post's text) and likes (the post's likes_count, as a number).
What you will learn
- ->, ->> and #>>: fetch one key as JSON, or as text, or follow a path of keys.
- @> and ?: ask whether the JSON contains something, or has a key.
- jsonb_set and ||: change one key, or merge in new ones, and keep the rest.
- jsonb_build_object and jsonb_agg: turn rows into JSON objects and arrays.
More in Postgres powers
Build "Flexible data" yourself
MingleSphereQL, a social app, needs answers from its data every day. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.
The first 2 chapters of SQL from zero are free. This one comes with the all labs plan.