SQL's Complex Types

Concept. SQL is not limited to INT and TEXT. One relational engine can store and query geographic points, documents, arrays, DNA strings, and even LLM calls, all through the same SELECT / FROM / WHERE, so you reach for a specialized database far later than you think.

Intuition. Spotify wants users within 5 km of a venue, an orders table with a flexible per-order JSON column, and songs an LLM tags "energetic". In modern Postgres or BigQuery each is one more function inside an ordinary query: ST_DWITHIN(...), a JSONB lookup, AI.CLASSIFY(...). None of them is a new database.

Course note: good to know for interviews; exact syntax is optional for tests.

A central cyan box holds the unchanging query shape (SELECT, FROM, WHERE, ORDER BY); four grey domain chips below keep that shape and only swap in a function: ST_DWITHIN for geographic, EDIT_DISTANCE for genomic, a JSONB lookup for semi-structured, AI.GENERATE for generative AI.

Figure 1. The query shape never changes; only the function does. Every example on this page is the same SELECT / FROM / WHERE / ORDER BY you have written all course, with a domain-specific function dropped into a clause: a distance test in the WHERE, a JSON path in the SELECT, an LLM call as a column. Learning a new data type means learning one function, not a new query language.

JSONB Before NoSQL

A little JSON in your data is not a reason to stand up a NoSQL cluster. One PostgreSQL node with a JSONB column gives you a flexible document schema and ACID transactions and joins, which carries an MVP well past the point most teams think they need a custom NoSQL database.

-- One Postgres node with JSONB: ACID and joins from the relational side,
-- a flexible document schema from the JSONB column.
CREATE TABLE orders (
  id         SERIAL PRIMARY KEY,
  user_id    INTEGER REFERENCES users(id),
  items      JSONB,        -- Flexible per-order document
  created_at TIMESTAMP
);

Modern Postgres added JSONB, vectors, and geo-spatial types fast enough that it absorbed much of the demand that drove specialized document and vector databases in the 2024 to 2025 AI wave.

Geographic: Finding Things Nearby

-- Spotify users within 5 km of Stanford (BigQuery)
SELECT
  u.name,
  u.email,
  ST_DISTANCE(u.location, ST_GEOGPOINT(-122.1697, 37.4275)) AS distance_meters
FROM Users u
WHERE ST_DWITHIN(
    u.location,
    ST_GEOGPOINT(-122.1697, 37.4275),  -- longitude, latitude
    5000                               -- meters
  )
ORDER BY distance_meters;

ST_DWITHIN is a WHERE predicate like any other; the engine handles the spherical geometry.

Generative AI: Semantic Understanding

LLM functions turn a column of free text into a column of structured output, in the same query that filters and sorts it.

-- Tag, summarize, and translate songs with an LLM (BigQuery)
SELECT
  title,
  artist,
  AI.CLASSIFY(lyrics, ["Happy", "Melancholic", "Energetic", "Chill"]) AS vibe,
  AI.GENERATE("Summarize the emotional arc IN one sentence", lyrics) AS ai_summary,
  AI.TRANSLATE(title, "Spanish") AS title_es
FROM Songs
WHERE AI.IF("The song IS about a summer romance", lyrics)
  AND play_count > 100000;

User-Defined Functions: Custom Logic

A UDF puts your business logic inside SQL: define it once, call it like any built-in. Use SQL for simple math, JavaScript when SQL alone is not expressive enough.

Define Once

-- SQL UDF: simple, fast, inlined
CREATE TEMP FUNCTION viralScore(plays INT64, shares INT64)
RETURNS FLOAT64
AS (plays * 0.3 + shares * 10);

-- JavaScript UDF: when SQL alone falls short
CREATE TEMP FUNCTION normalizeTitle(t STRING)
RETURNS STRING
LANGUAGE js AS """
  return t.toLowerCase().trim();
""";

Use Anywhere

SELECT
  normalizeTitle(s.title) AS title,
  s.artist,
  viralScore(s.play_count, s.share_count) AS viral_potential
FROM Songs s
WHERE s.release_year = 2024
  AND s.play_count > 1000
ORDER BY viral_potential DESC
LIMIT 100;

A UDF (viralScore) runs per row; a UDAF runs over a group for custom aggregations like a weighted average. Either way, you define it once and reuse it across queries.

Try It Yourself: BigQuery Public Datasets

Optional. If you want to run something now, these work today on the free tier. Skip without losing the thread.

-- Cafes named Starbucks within 10 km of downtown San Francisco
SELECT name, address, geom
FROM `bigquery-public-data.geo_openstreetmap.planet_features`
WHERE feature_type = 'amenity'
  AND tags['amenity'] = 'cafe'
  AND tags['name'] LIKE '%Starbucks%'
  AND ST_DWITHIN(geom, ST_GEOGPOINT(-122.4194, 37.7749), 10000);

-- COVID variants with more than 10 mutations
SELECT variant_name, country, date, mutation_count
FROM `bigquery-public-data.covid19_genome_sequence.variants`
WHERE mutation_count > 10
ORDER BY date DESC;

The Pattern Holds

SQL has absorbed new data types every decade, and the query shape has not changed.

1970s: numbers and text
1990s: + dates and binary
2000s: + XML and spatial
2010s: + JSON and arrays
2020s: + vectors, geo, and LLM calls

Each decade added functions, not a new language. The SELECT / FROM / WHERE you know already reaches the next data type.

Database Design at Big-Tech Scale → One node stretches far, but not forever. When analytics start to choke the live database, the stack splits into OLTP, OLAP, and a pipeline between them.