Common Mistakes When Working with JSON Data and How to Avoid Them

Working with JSON data in PostgreSQL is a super powerful tool, but just like any tool, you gotta use it carefully. Even small mistakes can turn your query into a real headache. Today, we're gonna focus again on the typical mistakes that pop up when dealing with JSON and JSONB in PostgreSQL, and how you can avoid them.

Problem 1: Using JSON Instead of JSONB

A lot of newbies mistakenly use the JSON data type, thinking it's the best choice for storing JSON data. But JSON in PostgreSQL stores stuff as plain text, which can slow things down big time when you're searching or filtering.

Example of a mistake:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    details JSON
);

INSERT INTO products (details) VALUES ('{"name": "Laptop", "price": 1000}');

If you try to filter by the key (price), it's gonna be way slower compared to JSONB.

How to fix it: use JSONB if you plan to actively filter or access the data.

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

Problem 2: No Indexes for JSONB

JSONB is insanely powerful, but without indexes, its performance on complex queries can really tank.

Example of a mistake: let's say we've got a table with a details column where we store a ton of JSON objects:

SELECT * FROM products WHERE details->>'name' = 'Laptop';

If your data isn't indexed, the server will do a full table scan, wasting a lot more time.

How to fix it: create a GIN index to speed up key lookups:

CREATE INDEX idx_details_name ON products USING gin (details jsonb_path_ops);

Problem 3: Mistakes When Extracting Nested Data

Pulling data from nested objects or arrays can get confusing, especially if you don't know the difference between the -> and ->> operators.

Example of a mistake:

SELECT details->'price' FROM products;

This query will return the value as JSON, not as a string ("1000" instead of 1000). If you want the actual value, you need to use ->>:

SELECT details->>'price' FROM products;

Problem 4: Using Operators the Wrong Way

You might've seen the @> operator and thought, "Sounds cool, let's use it everywhere!" But if you don't get how it works, you'll get some weird results.

Example of a mistake:

SELECT * FROM products WHERE details @> '{"price": 1000}';

This query only works if price is a number in the JSON. If the value is saved as a string "1000", the query won't return anything.

How to fix it: pay close attention to data types in your JSON:

SELECT * FROM products WHERE details->>'price' = '1000';

Problem 5: Huge JSON Objects

Storing big JSON objects without optimizing can really slow down your queries. Plus, reading or changing even a tiny part inside JSONB means the whole object has to be processed.

How to fix it: if certain keys are used a lot, break them out into separate table columns. For example:

ALTER TABLE products ADD COLUMN price NUMERIC;
UPDATE products SET price = (details->>'price')::NUMERIC;

Now you can filter and sort efficiently without having to parse the whole JSONB.

Problem 6: Full Object Rebuilds on Updates

When you use functions like jsonb_set() or jsonb_insert(), PostgreSQL creates a whole new JSONB object, which can be expensive performance-wise.

How to fix it: minimize how often you update JSONB. For example, instead of updating one object a bunch of times, combine all your changes into a single query:

UPDATE products
SET details = jsonb_set(details, '{price}', '1500'::jsonb);

Problem 7: Not Understanding Array Structure

Arrays in JSONB need careful handling too. Let's say you've got an array:

{
    "tags": ["electronics", "laptop", "sale"]
}

You want to check if the tag "laptop" is there. If you mess up and use the @> operator wrong, you might not get any results, since it expects an array, not a string.

Example of a mistake:

SELECT * FROM products WHERE details->'tags' @> '"laptop"';

How to fix it: Use the right format with the @> operator:

SELECT * FROM products WHERE details->'tags' @> '["laptop"]';

Tips to Avoid Mistakes

To dodge a bunch of problems when working with JSONB, follow these tips:

Pick the right data type. If you're working with big data and filtering a lot, always use JSONB instead of JSON.

Index your data. If your queries often hit certain keys, create a proper index (like GIN).

Check your data before inserting. Use validation functions to check your data structure:

DO $$
BEGIN
    IF jsonb_typeof('{"price": 1000}'::jsonb->'price') IS DISTINCT FROM 'number' THEN
        RAISE EXCEPTION 'Price must be a number';
    END IF;
END $$;

Optimize your data structure. If some keys are used more than others, pull them out into separate table columns.

Learn the operators and functions. Read the official PostgreSQL docs carefully to really get the differences between ->, ->>, @>, ?|, and other functions.

JSON and JSONB can totally be your best friends when working with flexible and complex data. The main thing is to be careful with your tools and avoid common mistakes, so your code stays fast and easy to maintain.