Working with the JSONB Data Type

JSON (JavaScript Object Notation) is a super popular data format for exchanging info between systems. It's lightweight, human-readable, and perfect for representing structured data like objects or arrays.

PostgreSQL supports two data types for working with JSON:

• JSON: stores data as a string. It's just a plain text representation of JSON, with no internal optimization.
• JSONB: binary representation of JSON. It's way more efficient for reading, filtering, and processing, since PostgreSQL parses and optimizes it ahead of time.

Why do people usually pick JSONB? Because:

• It's faster for queries thanks to binary storage.
• You can index it, which is awesome for big datasets.
• Keeps keys ordered and removes duplicates, making processing easier.

Example of a JSON structure:

{
  "name": "Alice",
  "age": 25,
  "skills": ["SQL", "PostgreSQL", "JSONB"]
}

Why is JSONB useful?

Storing semi-structured data. In the real world, data often comes as complex objects (like metadata, settings, user profiles). JSONB lets you store them without creating a ton of tables and relationships.

Modeling data that changes a lot. When an object's structure changes often (like fields being added or removed), JSONB gives you the flexibility to store that data.

Working with APIs. Lots of web apps send data as JSON. Instead of converting requests, you can just store them "as is" and work with them directly.

Creating a Table with JSONB

Let's get practical! Imagine we're building a database to store user profiles.

The profile column will store all the extra info (like age, interests, contacts) in JSON format. This is handy if the data structure varies from user to user.

You can insert JSON data as strings. PostgreSQL will automatically convert them to JSONB format.

Extracting Data from JSONB

Now that we've got some data, let's see how to pull it out. PostgreSQL gives you a bunch of operators for working with JSONB.

Accessing a field's value

Use the -> operator to get a field's value:

-- Show the user's age
SELECT profile->'age' AS age FROM users;

Converting a value to text

The ->> operator lets you extract a value as a string:

-- Show the user's location
SELECT profile->>'location' AS location FROM users;

Filtering Data with JSONB

The real power of JSONB comes out in queries with filtering. You can use standard SQL operators to work with JSON.

Example: filtering by key:

-- Find users whose location is "New York"
SELECT * FROM users
WHERE profile->>'location' = 'New York';

Searching in an array

JSON supports arrays, and PostgreSQL can search for values inside them:

-- Find users who know SQL
SELECT * FROM users
WHERE profile->'skills' @> '["SQL"]';

The @> operator (“contains”) checks that the left-hand JSON contains the right-hand JSON — a clean idiomatic way to look up a value inside a JSON array.

If you really need to iterate through the array, use EXISTS with jsonb_array_elements_text in a subquery — set-returning functions like jsonb_array_elements_text aren’t allowed directly in WHERE:

SELECT * FROM users
WHERE EXISTS (
    SELECT 1 FROM jsonb_array_elements_text(profile->'skills') AS skill
    WHERE skill = 'SQL'
);

We'll talk more about functions and ways to work with JSON later, when the time comes :P

Quick Recap: When to Use JSONB

JSONB is awesome for:

• Storing complex structured data.
• Handling data from external APIs.
• Situations where the object's structure changes.

But don't forget, overusing JSONB can make indexing and managing your database trickier. If your data structure is stable, it's usually better to stick with the relational model.

2

Task

SQL SELF, level 16, lesson 1

Locked

Extracting Data from JSONB

Extracting Data from JSONB

Open

2

Task

SQL SELF, level 16, lesson 1

Locked

Filtering Data Using JSONB

Filtering Data Using JSONB

Open