14 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

Example

-- 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. PostgreSQL 17 has no built-in UUIDv7 generator; use an application/library or extension if you need time-ordered UUIDs. PostgreSQL 18 adds uuidv7(). A bigint identity column is another option.

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.

Run the example

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.

Downloads

SQL file: uuid-jsonb.sql · All lesson files · Setup guide