Extracting Data from JSON Objects

JSONB is a powerful tool that lets you store complex data structures, like nested objects or arrays. But just storing stuff in JSONB isn’t enough — you gotta know how to get it out. For example, imagine you have a data column in your users table, where all user settings are stored as JSONB. Wanna know which theme a user picked? You’ll have to extract it from the JSONB object.

If JSONB is a treasure chest, then the ->, ->>, #>> operators and functions like jsonb_extract_path() are your keys. Let’s figure out how to use them.

Core Operators for Working with JSONB

PostgreSQL has a few key operators for working with JSONB. They let you pull values from keys, nested objects, and arrays. Here are the main ones:

The -> Operator

The -> operator grabs an object or array by the given key. If you want the value in the same format as in JSON, this is your go-to.

Example:

-- Example data
SELECT '{"name": "Alice", "age": 25}'::jsonb -> 'name';
-- Result: "Alice"

The ->> Operator

The ->> operator is like ->, but it returns the extracted value as text. Super handy when you want a simple text version of your data.

Example:

-- Example data
SELECT '{"name": "Alice", "age": 25}'::jsonb ->> 'age';
-- Result: "25" (string)

The #>> Operator

The #>> operator pulls data from nested objects by a specified path. The path is passed as an array of keys.

Example:

-- Example data
SELECT '{"user": {"name": "Bob", "details": {"age": 30}}}'::jsonb #>> '{user, details, age}';
-- Result: "30" (string)

Difference between -> and ->>:

If you care about keeping the data type (like an array or object), use ->. If you just want text, go with ->>.

Using Functions with JSONB

The jsonb_extract_path() function pulls a value from a JSONB object by the given path. It’s basically a functional version of the #>> operator, but a bit more expressive.

Example:

SELECT jsonb_extract_path('{"user": {"name": "Alice", "settings": {"theme": "dark"}}}'::jsonb, 'user', 'settings', 'theme');
-- Result: "dark"

If you want to get the text value right away, use jsonb_extract_path_text(). It works just like jsonb_extract_path(), but returns a string.

Example:

SELECT jsonb_extract_path_text('{"user": {"name": "Alice", "settings": {"theme": "dark"}}}'::jsonb, 'user', 'settings', 'theme');
-- Result: dark

Practical Examples

Extracting a value by key. Let’s say we have a products table, where the details column stores data as JSONB:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    details JSONB
);

INSERT INTO products (name, details) VALUES
    ('Laptop', '{"brand": "Dell", "price": 1200, "specs": {"ram": "16GB", "cpu": "Intel i7"}}'),
    ('Phone', '{"brand": "Apple", "price": 1000, "specs": {"ram": "4GB", "cpu": "A13"}}');

Result:

Let’s extract the brands of all products.

SELECT name, details->'brand' AS brand FROM products;

Result:

Extracting a text value. If you want the brand without quotes, use the ->> operator:

SELECT name, details->>'brand' AS brand FROM products;

Result:

Extracting nested data. Let’s pull the RAM amount (ram) for each product:

SELECT name, details#>>'{specs, ram}' AS ram FROM products;

Result:

Extracting data by path. You can do the same thing with the jsonb_extract_path_text() function:

SELECT name, jsonb_extract_path_text(details, 'specs', 'ram') AS ram FROM products;

Result:

Common Mistakes and How to Avoid Them

Mistakes pop up a lot when you:

• Try to extract data by a wrong path. For example, if the key doesn’t exist, you’ll get null as a result.
• Use the wrong operator for the job. -> is for objects and arrays, but for text you need ->>.

Error example:

-- Error: key 'nonexistent' doesn’t exist
SELECT details->>'nonexistent' FROM products;
-- Result: null

Tip: always check your data structure before writing queries to avoid mistakes.

Real-World Use Cases

Extracting data from JSONB is used in tons of real apps:

• In e-commerce for handling product features.
• In web apps for storing user settings.
• In analytics for processing structured data like events and logs.

Here’s another example. Let’s say we have an orders table with order data:

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_name TEXT,
    items JSONB
);

INSERT INTO orders (customer_name, items) VALUES
    ('John', '[{"product": "Laptop", "quantity": 1}, {"product": "Mouse", "quantity": 2}]'),
    ('Alice', '[{"product": "Phone", "quantity": 1}]');

Let’s extract the names of all products from the orders:

SELECT customer_name, jsonb_array_elements(items)->>'product' AS product FROM orders;

Result:

Next, we’ll dig deeper into working with JSONB, exploring nested data and transforming it into a more analysis-friendly format. Get ready for even cooler discoveries!