SQL's Complex Types

Concept. SQL extends beyond INT and TEXT. A relational engine can store and query geographic points, documents, arrays, DNA strings, and even LLM calls through the same SELECT / FROM / WHERE.

Intuition. Spotify can filter users within 5 km of a venue, store per-order JSON in an orders table, and tag songs as "energetic" with an LLM. In Postgres or BigQuery, each task becomes a function call inside an ordinary query: ST_DWITHIN(...), a JSONB lookup, AI.CLASSIFY(...).

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. Domain-specific types plug into the same SELECT / FROM / WHERE / ORDER BY shape through functions. A query can place a distance predicate in WHERE, extract a JSON path in SELECT, or call an LLM as a computed column.

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.