JSON and Messy Data

Textbook data is tidy. Real data arrives from CSV exports, web forms, partner APIs and spreadsheets that someone edited by hand. It has stray spaces, three spellings of the same country, dates in two formats and an age of n/a.

Two skills cover most of it: storing flexible structures without giving up the database, and cleaning bad input without losing track of what you threw away.

JSONB#

PostgreSQL stores JSON natively. JSONB is the binary form: it is parsed once on write, supports indexing, and is what you want almost always. (The plain JSON type keeps the exact text and is slow to query.)

CREATE TABLE product_specs (
    product_id INTEGER PRIMARY KEY REFERENCES products (product_id),
    specs      JSONB NOT NULL DEFAULT '{}'
);

INSERT INTO product_specs (product_id, specs) VALUES
 (4, '{"screen_in": 27, "resolution": "2560x1440", "ports": ["HDMI", "DisplayPort", "USB-C"]}'),
 (7, '{"noise_cancelling": true, "battery_hours": 30, "colors": ["black", "silver"]}');

Reading values#

SELECT product_id,
       specs ->> 'resolution'   AS resolution,
       specs -> 'ports' ->> 0   AS first_port
FROM product_specs
WHERE specs @> '{"screen_in": 27}';

 product_id | resolution | first_port
------------+------------+------------
          4 | 2560x1440  | HDMI

-> keeps the JSON type. ->> gives you text, so compare numbers by casting: (specs ->> 'battery_hours')::int > 20.

Turn a JSON array into rows with jsonb_array_elements_text:

SELECT p.name, port
FROM product_specs s
JOIN products p ON p.product_id = s.product_id,
     jsonb_array_elements_text(s.specs -> 'ports') AS port;

Update a single key without rewriting the application's whole document:

UPDATE product_specs
SET specs = jsonb_set(specs, '{battery_hours}', '35')
WHERE product_id = 7;

Indexing JSONB#

A GIN index makes containment queries fast across the whole document:

CREATE INDEX idx_product_specs_gin ON product_specs USING GIN (specs);

If you always filter on one key, an expression index is smaller and faster:

CREATE INDEX idx_specs_resolution ON product_specs ((specs ->> 'resolution'));

When to use JSONB, and when not to#

Good fits: attributes that genuinely vary per row (product specs across categories), payloads from external systems that you store as received, sparse optional settings.

Poor fits: values you join on, aggregate, sort by or constrain constantly. Those want real columns. You lose foreign keys, NOT NULL, types and cheap statistics inside a JSON blob. A common healthy design puts the stable, queried fields in columns and the long tail in one JSONB column.

Cleaning messy data#

Here is the kind of input an import receives.

CREATE TABLE raw_signups (
    raw_id      INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email       TEXT,
    signup_date TEXT,
    age         TEXT,
    country     TEXT
);

INSERT INTO raw_signups (email, signup_date, age, country) VALUES
 ('  Ana@Example.com ', '2024-03-05', '29',  'india'),
 ('ana@example.com',    '05/03/2024', '29',  'India '),
 ('ben@example.com',    '2024-13-45', 'n/a', 'USA'),
 ('',                   '2024-04-01', '41',  'usa'),
 ('cy@example.com',     '2024-04-02', '-5',  NULL);

Everything is TEXT, which is how you load unknown data safely. Converting types on the way in means a single bad value aborts the load.

Step 1: normalize text#

NULLIF(LOWER(TRIM(email)), '')                                    -- empty string becomes NULL
CASE WHEN LOWER(TRIM(country)) = 'usa' THEN 'USA'
     ELSE INITCAP(TRIM(country)) END                              -- one spelling per country

TRIM removes the stray spaces, LOWER makes emails comparable, and NULLIF(x, '') turns blanks into real NULLs so later checks treat them as missing. A blank string and NULL are different values, and mixing them breaks IS NULL filters.

Step 2: convert types without crashing#

A plain cast stops at the first bad value:

SELECT '2024-13-45'::date;
ERROR:  date/time field value out of range: "2024-13-45"

Write a guarded converter that returns NULL instead of raising:

CREATE OR REPLACE FUNCTION try_to_date(p_text TEXT, p_fmt TEXT)
RETURNS DATE
LANGUAGE plpgsql
STABLE
AS $$
BEGIN
    RETURN to_date(p_text, p_fmt);
EXCEPTION WHEN others THEN
    RETURN NULL;
END;
$$;

PostgreSQL 16 also has a built-in check, pg_input_is_valid('2024-13-45', 'date'), which returns false without an error.

Dates are the classic trap. '05/03/2024' is 5 March in much of the world and 3 May in the US. Casting with ::date guesses based on the DateStyle setting. Always state the format you expect with to_date(text, 'DD/MM/YYYY'), and check the shape with a regular expression first, because to_date is lenient about separators.

CASE WHEN signup_date ~ '^\d{4}-\d{2}-\d{2}$' THEN try_to_date(signup_date, 'YYYY-MM-DD')
     WHEN signup_date ~ '^\d{2}/\d{2}/\d{4}$' THEN try_to_date(signup_date, 'DD/MM/YYYY')
END

For numbers, test the shape before casting:

CASE WHEN TRIM(age) ~ '^\d{1,3}$' THEN TRIM(age)::int END

Put it together:

SELECT raw_id,
       NULLIF(LOWER(TRIM(email)), '') AS email,
       CASE WHEN signup_date ~ '^\d{4}-\d{2}-\d{2}$' THEN try_to_date(signup_date, 'YYYY-MM-DD')
            WHEN signup_date ~ '^\d{2}/\d{2}/\d{4}$' THEN try_to_date(signup_date, 'DD/MM/YYYY')
       END AS signup_date,
       CASE WHEN TRIM(age) ~ '^\d{1,3}$' THEN TRIM(age)::int END AS age,
       CASE WHEN LOWER(TRIM(country)) = 'usa' THEN 'USA'
            ELSE INITCAP(TRIM(country)) END AS country
FROM raw_signups;

 raw_id |      email      | signup_date | age | country
--------+-----------------+-------------+-----+---------
      1 | ana@example.com | 2024-03-05  |  29 | India
      2 | ana@example.com | 2024-03-05  |  29 | India
      3 | ben@example.com |             |     | USA
      4 |                 | 2024-04-01  |  41 | USA
      5 | cy@example.com  | 2024-04-02  |     |

Rows 1 and 2 now show the same person. Row 3 lost its date and age to NULL instead of killing the import. Row 5's age of -5 failed the digit pattern and became NULL.

Step 3: deduplicate#

Rows 1 and 2 are the same signup written two ways. Keep one with the window-function pattern from Module 4:

WITH cleaned AS (
    SELECT raw_id, NULLIF(LOWER(TRIM(email)), '') AS email, ... -- as above
    FROM raw_signups
),
ranked AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY raw_id) AS rn
    FROM cleaned
    WHERE email IS NOT NULL
)
SELECT * FROM ranked WHERE rn = 1;

Decide the survivor rule on purpose: lowest ID, latest date, most complete row.

Step 4: quarantine, never silently drop#

A row that fails validation should go somewhere you can look at it.

CREATE TABLE signup_rejects AS
SELECT raw_id, email, signup_date, age, country, 'missing email' AS reason
FROM raw_signups
WHERE NULLIF(TRIM(email), '') IS NULL;

Report counts at the end: rows read, rows loaded, rows rejected by reason. When those three numbers add up, you can trust the load. When they do not, you know before your users do.

More tools#

REGEXP_REPLACE(phone, '[^0-9+]', '', 'g')     -- keep only digits and plus signs
SPLIT_PART(full_name, ' ', 1)                 -- first token
COALESCE(NULLIF(TRIM(city), ''), 'Unknown')   -- blank or missing to a default

Common mistakes#

Casting directly on dirty data. The first bad row stops everything. Load as TEXT, then convert with guards.

Trusting date strings. Ambiguous formats silently swap day and month.

Mixing NULL and empty string. Pick one for "missing" and normalize to it.

Dropping bad rows without a trace. You never learn why the counts do not match.

Putting everything into JSONB. You give up constraints, types and fast aggregation.

Treating ->> output as numbers. It is text, and '9' > '10' as strings.

Practice#

1. Create product_specs and query every product whose specs contain "noise_cancelling": true. Then add a GIN index and check that the plan can use it on a larger generated table.
2. Use jsonb_array_elements_text to list one row per color for product 7.
3. Load the raw_signups rows, run the cleaning query, and confirm it matches the table above. Then add a sixth messy row of your own and extend the query to handle it.
4. Write a deduplication query that keeps, per email, the row with the most non-null fields. (Hint: count non-nulls with num_nonnulls.)
5. Build the three-number reconciliation (rows read, loaded, rejected) for the import and make a deliberate mistake to see it fail to add up.