Postgres by Example: UUID and JSONB

Two of PostgreSQL's most popular non-trivial types: uuid for opaque, globally unique identifiers, and jsonb for binary-encoded JSON that can be indexed and queried. UUIDs are the standard alternative to sequential integer IDs when you need IDs that are unguessable, generatable on the client, or globally unique across systems. jsonb is what makes PostgreSQL competitive with document stores for semi-structured data.

What you'll learn:

• Generating UUIDs with gen_random_uuid()
• Storing and querying JSON with jsonb
• ->, ->>, #>, #>> operators and what they return
• The @> containment operator (and how it uses GIN indexes)
• The difference between json and jsonb

-- UUID generation (built-in from PostgreSQL 13)
SELECT gen_random_uuid() AS id;

-- jsonb literal
SELECT '{"name": "Alice", "age": 30, "tags": ["admin", "beta"]}'::jsonb AS j;

-- -> returns jsonb (preserves type); ->> returns text
SELECT '{"name": "Alice"}'::jsonb -> 'name'  AS as_jsonb,
       '{"name": "Alice"}'::jsonb ->> 'name' AS as_text;

-- Array indexing (0-based)
SELECT '["a", "b", "c"]'::jsonb -> 1 AS second;

-- Path operators: #> returns jsonb, #>> returns text
SELECT '{"user": {"address": {"city": "Paris"}}}'::jsonb #>> '{user, address, city}' AS city;

-- Containment: @> checks if the right side is contained in the left
SELECT '{"name": "Alice", "age": 30}'::jsonb @> '{"name": "Alice"}'::jsonb AS contains_alice;

-- Querying jsonb columns in a real table
DROP TABLE IF EXISTS docs_example;
CREATE TABLE docs_example (id integer PRIMARY KEY, data jsonb);
INSERT INTO docs_example VALUES
  (1, '{"name": "Alice", "tags": ["admin"]}'),
  (2, '{"name": "Bob",   "tags": ["beta"]}');

SELECT id, data ->> 'name' AS name
FROM docs_example
WHERE data @> '{"tags": ["admin"]}';

gen_random_uuid() produces a random version-4 UUID. It is built in from PostgreSQL 13 onward — no pgcrypto extension needed. For sortable, time-ordered IDs consider UUIDv7 (a community-supported pattern; native support is on the roadmap), or simply use bigint with an IDENTITY column.

For JSON, prefer jsonb over json. json stores the text exactly as you give it (including whitespace and key order); jsonb parses, normalizes, and stores in a binary form. jsonb is what you index, what you query efficiently, and what almost every application uses. Reach for plain json only when you need to preserve the input bytes verbatim (rare).

The -> operator extracts and returns jsonb. ->> extracts and returns text (stripping JSON quoting). #> and #>> take a path (an array of keys/indexes). @> ("contains") tests whether the left value contains the right — it is the foundation of JSON queries.

To run:

$ psql -f source/uuid-jsonb.sql postgres
                  id
--------------------------------------
 xxxxxxxx-xxxx-4xxx-xxxx-xxxxxxxxxxxx
(1 row)
 ...
 contains_alice
----------------
 t
(1 row)
 ...

Common pitfalls:

• Comparing data -> 'name' to a string: data -> 'name' = '"Alice"' works (note the JSON-quoted string), but data ->> 'name' = 'Alice' is what you usually want.
• Forgetting an index: filtering on a jsonb column without a GIN index does a full table scan. CREATE INDEX ON docs_example USING gin (data); enables @> and similar queries to use the index.
• Mutating jsonb in place: UPDATE t SET data = data || '{"k": "v"}'::jsonb adds or replaces a top-level key; jsonb_set lets you change nested paths.
• Schema drift inside jsonb: it is flexible, but you still need to know what is in there. Document the expected shape, and validate at the application layer or with a CHECK constraint.

Tip: Index just the parts of JSON you query. Instead of indexing the whole data column with GIN, index a single path: CREATE INDEX ON docs_example ((data ->> 'name'));. This is a B-tree expression index — small, fast, and exact.

Try it: Use ->> to get the name as text: SELECT '{"name": "Alice"}'::jsonb ->> 'name';. Insert a row with nested data and query a nested field with #>>. Then add a GIN index on data and use @> for containment lookups.

Source: uuid-jsonb.sql

Next: Arrays and ENUM

Home: Postgres by Example