JSON in Supabase: JSONB Columns, Queries, and Edge Functions

Written and reviewed by the Jsonic editorial team — every guide is verified against the official spec or runtime before publication.

Last updated: May 27, 2026

Supabase stores JSON in PostgreSQL JSONB columns — binary-indexed, faster to query than text json columns, and supporting GIN indexes for efficient containment searches. In the Supabase JavaScript client, filter on JSONB fields with .filter('metadata->role', 'eq', 'admin') or .contains('metadata', { verified: true }). For Edge Functions, return new Response(JSON.stringify(data), { headers: { 'Content-Type': 'application/json' } }). The Supabase CLI generates TypeScript types from your database schema: supabase gen types typescript --local creates a Database type with JSONB columns typed as Json. This guide covers 5 topics: defining JSONB columns in Supabase, filtering on JSON fields with the JS client, querying JSONB with RPC and raw SQL, Edge Functions JSON responses, and TypeScript types from the CLI.

Define JSONB Columns in Supabase

Bottom line: use jsonb (not json) for every new JSON column in Supabase. JSONB stores data in binary, enabling GIN indexes and making reads faster. Define columns in a SQL migration under supabase/migrations/ and run supabase db push to apply.

The difference between json and jsonb matters at query time. PostgreSQL must re-parse the raw text string on every json column access, while jsonb is already decomposed into an efficient binary form. JSONB also normalizes key order and removes duplicate keys on write — which means if you need to preserve the exact original byte sequence of a JSON document, json is the correct type. For all other use cases, jsonb is strictly better. You can also create JSONB columns in Supabase Studio: go to Table Editor, open the table, click Add column, and select jsonb as the type.

-- Migration: supabase/migrations/20260527_add_metadata.sql

-- Add a JSONB column to an existing table
ALTER TABLE profiles
  ADD COLUMN metadata jsonb;

-- Or define it at table creation
CREATE TABLE profiles (
  id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id     uuid REFERENCES auth.users NOT NULL,
  metadata    jsonb,           -- arbitrary JSON payload
  created_at  timestamptz DEFAULT now()
);

-- jsonb vs json: use jsonb for all new columns
-- json  → stored as verbatim text, no indexing support, slower reads
-- jsonb → binary format, GIN-indexable, faster reads, normalizes key order

-- Set a default empty object (prevents NULL JSONB from causing filter issues)
ALTER TABLE profiles
  ALTER COLUMN metadata SET DEFAULT '{}'::jsonb;

-- Insert via JavaScript client (object is auto-serialized)
-- const { error } = await supabase
//   .from('profiles')
//   .insert({ user_id: userId, metadata: { plan: 'pro', verified: true } })

Query JSON Fields with the JavaScript Client

Bottom line: the Supabase JS client exposes three main ways to filter JSONB columns: .filter() with arrow notation for scalar comparisons, .contains() for subset containment, and .overlaps() for array intersection. Each maps to a specific PostgreSQL JSONB operator under the hood.

The -> arrow operator returns a JSON value; ->> returns text. Use ->> when comparing with string values in .filter()— otherwise PostgreSQL compares a JSON value to a text literal and may return no rows. The .contains() method accepts a plain JavaScript object or array and generates the PostgreSQL @> containment check. For arrays inside JSONB, use .overlaps() to find rows where the column array shares at least one element. All these filter methods compose with .select(), .order(), and .limit() as usual.

import { createClient } from '@supabase/supabase-js'

const supabase = createClient(process.env.SUPABASE_URL!, process.env.SUPABASE_ANON_KEY!)

// .filter() with ->> (text extraction) for scalar equality
// Maps to: WHERE metadata->>'role' = 'admin'
const { data: admins } = await supabase
  .from('profiles')
  .filter('metadata->>role', 'eq', 'admin')
  .select('id, user_id, metadata')

// .contains() — JSONB containment (@>)
// Maps to: WHERE metadata @> '{"verified": true}'
const { data: verified } = await supabase
  .from('profiles')
  .contains('metadata', { verified: true })

// Multiple containment conditions (AND)
const { data: proPlan } = await supabase
  .from('profiles')
  .contains('metadata', { plan: 'pro', verified: true })

// .filter() with -> (JSON value extraction) for numeric comparison
// Maps to: WHERE (metadata->'score')::int > 80
const { data: highScore } = await supabase
  .from('profiles')
  .filter('metadata->score', 'gt', 80)

// .overlaps() — for JSONB array columns
// Maps to: WHERE metadata->'tags' && '["design","billing"]'
const { data: tagged } = await supabase
  .from('profiles')
  .overlaps('metadata->tags', ['design', 'billing'])

// Combine with .select() JSON path extraction
// Returns only a nested field from JSONB (PostgreSQL 12+)
const { data: roles } = await supabase
  .from('profiles')
  .select('id, metadata->role')
  .not('metadata', 'is', null)

Use RPC Functions for Complex JSON Queries

Bottom line: for queries that go beyond simple containment — such as expanding JSONB arrays into rows, aggregating JSON values, or computing derived fields from nested JSON — write a PostgreSQL function and call it with supabase.rpc(). This keeps complex logic server-side and avoids shipping large result sets to the client just to filter them in JavaScript.

PostgreSQL functions that return JSON can use jsonb_agg() to collect rows into a JSON array, jsonb_build_object() to construct JSON from column values, and jsonb_array_elements() to expand a JSONB array column into individual rows (similar to UNNEST for arrays). Define these functions in a migration or directly in the SQL editor in Supabase Studio. Functions must have SECURITY DEFINER or appropriate Row Level Security (RLS) configured — using SECURITY INVOKER with the anon role is safer for public-facing functions. Call them from the client with supabase.rpc('function_name', { param: value }).

-- SQL: create function that searches inside a JSONB array field
-- supabase/migrations/20260527_rpc_search_by_tag.sql

CREATE OR REPLACE FUNCTION search_profiles_by_tag(tag text)
RETURNS TABLE (
  id        uuid,
  user_id   uuid,
  metadata  jsonb
)
LANGUAGE sql
SECURITY INVOKER
AS $$
  SELECT p.id, p.user_id, p.metadata
  FROM   profiles p,
         jsonb_array_elements_text(p.metadata->'tags') AS t(tag_value)
  WHERE  t.tag_value = search_profiles_by_tag.tag;
$$;

-- Aggregate function: return summary JSON per plan tier
CREATE OR REPLACE FUNCTION profile_plan_summary()
RETURNS jsonb
LANGUAGE sql
SECURITY INVOKER
AS $$
  SELECT jsonb_agg(
    jsonb_build_object(
      'plan',  metadata->>'plan',
      'count', plan_count
    )
  )
  FROM (
    SELECT metadata->>'plan' AS plan, COUNT(*) AS plan_count
    FROM   profiles
    WHERE  metadata->>'plan' IS NOT NULL
    GROUP  BY metadata->>'plan'
  ) sub;
$$;

// Call RPC functions from the JavaScript client

// Search by tag inside a JSONB array
const { data: designers, error } = await supabase
  .rpc('search_profiles_by_tag', { tag: 'design' })

// Aggregate query — returns a single JSON value
const { data: summary } = await supabase
  .rpc('profile_plan_summary')
// summary: [{ plan: 'pro', count: 42 }, { plan: 'free', count: 130 }]

// RPC with additional client-side filters
const { data: filtered } = await supabase
  .rpc('search_profiles_by_tag', { tag: 'billing' })
  .filter('metadata->>role', 'eq', 'admin')
  .limit(10)

Return JSON from Supabase Edge Functions

Bottom line: Supabase Edge Functions run on Deno and use the Web Fetch API — return JSON with new Response(JSON.stringify(data), headers). Always set Content-Type: application/json and handle CORS preflight (OPTIONS) requests to allow browser clients to call the function.

Edge Functions are deployed at https://<project-ref>.supabase.co/functions/v1/<name>and can be invoked from the browser, from the JS client with supabase.functions.invoke(), or via a plain fetch() call with the Authorization: Bearer <anon-key>header. The function file lives at supabase/functions/<name>/index.ts. Deploy with supabase functions deploy <name>. For local development, run supabase functions serve to start a local Deno server — functions are hot-reloaded on file save.

// supabase/functions/profile-summary/index.ts
import { createClient } from 'https://esm.sh/@supabase/supabase-js@2'

const corsHeaders = {
  'Access-Control-Allow-Origin': '*',
  'Access-Control-Allow-Headers': 'authorization, x-client-info, apikey, content-type',
  'Access-Control-Allow-Methods': 'POST, GET, OPTIONS',
}

Deno.serve(async (req: Request): Promise<Response> => {
  // Handle CORS preflight
  if (req.method === 'OPTIONS') {
    return new Response(null, { status: 204, headers: corsHeaders })
  }

  try {
    // Read JSON body from request
    const { userId } = await req.json() as { userId: string }
    if (!userId) {
      return new Response(
        JSON.stringify({ error: 'userId is required' }),
        { status: 400, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
      )
    }

    // Initialize Supabase client with service role key for server-side access
    const supabase = createClient(
      Deno.env.get('SUPABASE_URL')!,
      Deno.env.get('SUPABASE_SERVICE_ROLE_KEY')!
    )

    const { data, error } = await supabase
      .from('profiles')
      .select('metadata')
      .eq('user_id', userId)
      .single()

    if (error) throw error

    // Return JSON response
    return new Response(
      JSON.stringify({ userId, metadata: data.metadata }),
      { status: 200, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
    )
  } catch (err) {
    return new Response(
      JSON.stringify({ error: (err as Error).message }),
      { status: 500, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
    )
  }
})

// Call an Edge Function from the JavaScript client
// Option 1: supabase.functions.invoke()
const { data, error } = await supabase.functions.invoke('profile-summary', {
  body: { userId: 'abc-123' },
})

// Option 2: plain fetch with authorization header
const res = await fetch(
  `${process.env.SUPABASE_URL}/functions/v1/profile-summary`,
  {
    method: 'POST',
    headers: {
      'Content-Type': 'application/json',
      'Authorization': `Bearer ${process.env.SUPABASE_ANON_KEY}`,
    },
    body: JSON.stringify({ userId: 'abc-123' }),
  }
)
const result = await res.json()

TypeScript Types from the Supabase CLI

Bottom line: supabase gen types typescript --local inspects your PostgreSQL schema and generates a Database TypeScript type. Table rows are typed with the correct column types — JSONB columns become the broad Json union type, which you should narrow in application code.

The generated Database type is used by passing it as a generic to createClient<Database>(url, key). From that point, the client's .from(), .select(), and .insert() calls are all type-checked against your actual schema. JSONB columns are typed as Json | null (or Json if the column has a NOT NULL constraint). Because Json is a recursive union, TypeScript cannot narrow it automatically — you must cast to a more specific interface or use Zod's parse() after fetching. Re-run the gen types command whenever you apply a new migration to keep types in sync.

# Generate types from local Supabase instance (after supabase start)
supabase gen types typescript --local > src/types/database.types.ts

# Or generate from a remote project (production/staging)
supabase gen types typescript --project-id <your-project-ref> > src/types/database.types.ts

// src/types/database.types.ts (excerpt — auto-generated)
export type Json =
  | string
  | number
  | boolean
  | null
  | { [key: string]: Json | undefined }
  | Json[]

export type Database = {
  public: {
    Tables: {
      profiles: {
        Row: {
          id: string
          user_id: string
          metadata: Json | null    // JSONB column
          created_at: string
        }
        Insert: {
          id?: string
          user_id: string
          metadata?: Json | null
          created_at?: string
        }
        Update: {
          id?: string
          user_id?: string
          metadata?: Json | null
          created_at?: string
        }
      }
    }
  }
}

import { createClient } from '@supabase/supabase-js'
import type { Database } from '@/types/database.types'
import { z } from 'zod'

// Pass Database as a generic for fully typed client
const supabase = createClient<Database>(
  process.env.SUPABASE_URL!,
  process.env.SUPABASE_ANON_KEY!
)

// Define your app-level interface for the JSONB payload
interface ProfileMetadata {
  plan: 'free' | 'pro' | 'enterprise'
  verified: boolean
  role: string
  tags: string[]
}

// Zod schema for runtime validation of JSONB data
const ProfileMetadataSchema = z.object({
  plan: z.enum(['free', 'pro', 'enterprise']),
  verified: z.boolean(),
  role: z.string(),
  tags: z.array(z.string()),
})

async function getProfileMetadata(userId: string): Promise<ProfileMetadata> {
  const { data, error } = await supabase
    .from('profiles')
    .select('metadata')
    .eq('user_id', userId)
    .single()

  if (error) throw error

  // Narrow the Json type with Zod
  return ProfileMetadataSchema.parse(data.metadata)
}

GIN Indexes for JSON Search Performance

Bottom line: without a GIN index, JSONB containment queries (@>) and key-existence queries (?) perform a full sequential scan — reading every row in the table. Add CREATE INDEX ON table USING gin(column) to turn those into fast index lookups. For single-path equality queries, an expression index on the specific path is even smaller and faster.

GIN (Generalized Inverted Index) works by decomposing each JSONB document into a set of key-path tokens and building an inverted index over them — similar to how a full-text search index works. This makes it efficient for any query that asks "does this document contain X?" regardless of where X appears in the document. The trade-off is that GIN indexes are larger than B-tree indexes and slower to update on write-heavy workloads. For tables under ~5,000 rows, sequential scans are typically fast enough without any index. Add GIN when you start to see slow containment queries in the Supabase dashboard's query performance advisor, or when the table exceeds ~50,000 rows.

-- supabase/migrations/20260527_jsonb_indexes.sql

-- Full GIN index: speeds up @> (containment) and ? (key existence) on any path
CREATE INDEX idx_profiles_metadata_gin
  ON profiles
  USING gin(metadata);

-- When to use full GIN:
--   .contains('metadata', { plan: 'pro' })
--   .filter('metadata', 'cs', '{"verified":true}')
--   Any containment or key-existence query on arbitrary paths

-- Expression index: speeds up equality on a specific path
-- Smaller and faster than GIN for single-field queries
CREATE INDEX idx_profiles_metadata_plan
  ON profiles ((metadata->>'plan'));

-- Expression index for nested path
CREATE INDEX idx_profiles_metadata_role
  ON profiles ((metadata->>'role'));

-- When to use expression indexes:
--   .filter('metadata->>plan', 'eq', 'pro')  ← uses the expression index above
--   High-cardinality fields where most queries filter on a known path

-- You can have both GIN + expression indexes on the same column
-- PostgreSQL query planner picks the most selective one

-- Check index usage in Supabase Studio → Database → Query Performance
-- Or run: EXPLAIN ANALYZE SELECT * FROM profiles WHERE metadata @> '{"plan":"pro"}'

FAQ