Supabase JSON and JSONB: Working with JSON Data

-- Simple JSONB column
create table products (
  id uuid default gen_random_uuid() primary key,
  name text not null,
  price numeric(10,2) not null,
  metadata jsonb, -- Store flexible product attributes
  created_at timestamp with time zone default now()
);

-- Multiple JSONB columns with different purposes
create table user_profiles (
  id uuid references auth.users on delete cascade primary key,
  username text unique not null,
  settings jsonb default '{}', -- User preferences
  profile_data jsonb default '{}', -- Bio, social links, etc.
  feature_flags jsonb default '{}', -- Feature access control
  created_at timestamp with time zone default now()
);

-- JSONB with default values
create table projects (
  id uuid default gen_random_uuid() primary key,
  name text not null,
  config jsonb default '{
    "notifications": true,
    "theme": "light",
    "language": "en"
  }'::jsonb,
  custom_fields jsonb default '[]'::jsonb, -- Array of custom fields
  created_at timestamp with time zone default now()
);

-- JSONB with constraints
create table events (
  id uuid default gen_random_uuid() primary key,
  event_type text not null,
  event_data jsonb not null,
  user_id uuid references auth.users(id),
  created_at timestamp with time zone default now(),
  
  -- Constraint: event_data must have 'action' key
  constraint event_data_has_action check (event_data ? 'action'),
  
  -- Constraint: event_data.action must be one of allowed values
  constraint valid_action check (
    event_data->>'action' in ('click', 'view', 'submit', 'delete')
  )
);

-- Insert JSONB data
insert into products (name, price, metadata) values
  ('T-Shirt', 29.99, '{
    "color": "blue",
    "size": "M",
    "material": "cotton",
    "tags": ["casual", "summer"]
  }'),
  ('Laptop', 999.99, '{
    "brand": "TechCo",
    "specs": {
      "cpu": "Intel i7",
      "ram": "16GB",
      "storage": "512GB SSD"
    },
    "warranty_years": 2
  }');

insert into user_profiles (id, username, settings, profile_data) values
  (gen_random_uuid(), 'john_doe', 
   '{"theme": "dark", "notifications": {"email": true, "push": false}}',
   '{"bio": "Software Developer", "location": "San Francisco", "website": "example.com"}'
  );

-- Extract top-level field (returns JSON)
select
  name,
  metadata->'color' as color_json,
  metadata->>'color' as color_text  -- Returns text
from products;

-- Extract nested field
select
  name,
  metadata#>'{specs,cpu}' as cpu_json,
  metadata#>>'{specs,cpu}' as cpu_text
from products;

-- Filter by JSONB field
select * from products
where metadata->>'color' = 'blue';

-- Filter by nested field
select * from products
where metadata#>>'{specs,ram}' = '16GB';

-- Check if JSONB contains data
select * from products
where metadata @> '{"color": "blue"}';

-- Check if key exists
select * from products
where metadata ? 'warranty_years';

-- Check multiple keys exist
select * from products
where metadata ?& array['color', 'size'];

-- Check any key exists
select * from products
where metadata ?| array['color', 'brand'];

-- Query JSONB array elements
select
  name,
  jsonb_array_elements_text(metadata->'tags') as tag
from products
where metadata ? 'tags';

-- Filter by array contains
select * from products
where metadata->'tags' @> '"summer"';

-- Aggregate JSONB data
select
  metadata->>'color' as color,
  count(*) as count,
  avg(price) as avg_price
from products
where metadata ? 'color'
group by metadata->>'color';

-- Complex nested query
select
  u.username,
  u.settings->>'theme' as theme,
  u.settings#>>'{notifications,email}' as email_notifications,
  u.profile_data->>'bio' as bio
from user_profiles u
where
  u.settings @> '{"theme": "dark"}'
  and u.settings#>>'{notifications,email}' = 'true';

-- Search in JSONB text content
select * from products
where metadata::text ilike '%cotton%';

-- Convert JSONB to rows
select
  p.name,
  kv.key as attribute,
  kv.value as value
from products p,
lateral jsonb_each(p.metadata) kv;

-- Expand nested objects
select
  p.name,
  specs.key as spec_name,
  specs.value as spec_value
from products p,
lateral jsonb_each(p.metadata->'specs') specs
where p.metadata ? 'specs';

-- JSON path queries (PostgreSQL 12+)
select
  name,
  jsonb_path_query(metadata, '$.specs.cpu') as cpu,
  jsonb_path_exists(metadata, '$.tags[*] ? (@ == "summer")') as has_summer_tag
from products;

-- Replace entire JSONB column
update products
set metadata = '{"color": "red", "size": "L"}'
where id = 'some-uuid';

-- Set specific field (top-level)
update products
set metadata = jsonb_set(
  metadata,
  '{color}',
  '"red"'
)
where id = 'some-uuid';

-- Set nested field
update products
set metadata = jsonb_set(
  metadata,
  '{specs,ram}',
  '"32GB"'
)
where id = 'some-uuid';

-- Add new field
update products
set metadata = metadata || '{"new_field": "value"}';

-- Remove field
update products
set metadata = metadata - 'old_field';

-- Remove nested field
update products
set metadata = metadata #- '{specs,old_spec}';

-- Merge objects
update user_profiles
set settings = settings || '{
  "language": "es",
  "timezone": "PST"
}'::jsonb
where id = auth.uid();

-- Update array element
update products
set metadata = jsonb_set(
  metadata,
  '{tags,0}',  -- Update first element
  '"winter"'
)
where id = 'some-uuid';

-- Append to array
update products
set metadata = jsonb_set(
  metadata,
  '{tags}',
  (metadata->'tags') || '"new-tag"'::jsonb
)
where id = 'some-uuid';

-- Remove from array (keep elements != value)
update products
set metadata = jsonb_set(
  metadata,
  '{tags}',
  (
    select jsonb_agg(elem)
    from jsonb_array_elements(metadata->'tags') elem
    where elem::text != '"summer"'
  )
)
where id = 'some-uuid';

-- Conditional update based on JSONB value
update user_profiles
set settings = jsonb_set(
  settings,
  '{notifications,email}',
  'true'
)
where settings#>>'{notifications,email}' = 'false';

-- Increment numeric value in JSONB
update products
set metadata = jsonb_set(
  metadata,
  '{warranty_years}',
  to_jsonb((metadata->>'warranty_years')::int + 1)
)
where metadata ? 'warranty_years';

-- Function to update nested JSONB
create or replace function update_user_setting(
  p_user_id uuid,
  p_path text[],
  p_value jsonb
)
returns void as $$
begin
  update user_profiles
  set settings = jsonb_set(settings, p_path, p_value)
  where id = p_user_id;
end;
$$ language plpgsql security definer;

-- Usage:
select update_user_setting(
  auth.uid(),
  '{theme}',
  '"dark"'::jsonb
);

-- GIN index on entire JSONB column (default operator class)
create index idx_products_metadata on products using gin(metadata);

-- GIN index with jsonb_path_ops (faster, but only supports @>, @?, @@)
create index idx_products_metadata_path on products using gin(metadata jsonb_path_ops);

-- Index specific JSONB field
create index idx_products_color on products((metadata->>'color'));

-- Index nested field
create index idx_products_cpu on products((metadata#>>'{specs,cpu}'));

-- Partial index for specific conditions
create index idx_active_users on user_profiles((settings->>'status'))
where settings @> '{"status": "active"}';

-- Expression index for case-insensitive search
create index idx_products_color_lower on products(lower(metadata->>'color'));

-- Index for array contains queries
create index idx_products_tags on products using gin((metadata->'tags'));

-- Analyze query performance
explain analyze
select * from products
where metadata @> '{"color": "blue"}';

-- Compare different index strategies
-- Without index:
explain analyze
select * from products
where metadata->>'color' = 'blue';

-- With expression index:
explain analyze
select * from products
where metadata->>'color' = 'blue';
-- Should use idx_products_color

-- Statistics for JSONB columns
select
  metadata->>'color' as color,
  count(*) as count
from products
group by metadata->>'color'
order by count desc;

-- Validate index usage
select
  schemaname,
  tablename,
  indexname,
  idx_scan,
  idx_tup_read,
  idx_tup_fetch
from pg_stat_user_indexes
where indexname like '%metadata%';

-- Constraint: Required keys
alter table products
add constraint metadata_required_keys
check (metadata ?& array['color', 'size']);

-- Constraint: Specific value types
alter table products
add constraint metadata_price_numeric
check (
  not metadata ? 'price' or
  (metadata->>'price')::numeric > 0
);

-- Constraint: Enum values
alter table user_profiles
add constraint valid_theme
check (
  settings->>'theme' in ('light', 'dark', 'auto')
);

-- Constraint: Array length
alter table products
add constraint tags_limit
check (
  not metadata ? 'tags' or
  jsonb_array_length(metadata->'tags') <= 10
);

-- Function to validate JSONB schema
create or replace function validate_metadata_schema(data jsonb)
returns boolean as $$
begin
  -- Check required fields
  if not (data ?& array['color', 'size']) then
    raise exception 'Missing required fields: color, size';
  end if;

  -- Check data types
  if data ? 'price' then
    begin
      perform (data->>'price')::numeric;
    exception when others then
      raise exception 'Invalid price format';
    end;
  end if;

  -- Check nested structure
  if data ? 'specs' then
    if jsonb_typeof(data->'specs') != 'object' then
      raise exception 'specs must be an object';
    end if;
  end if;

  return true;
end;
$$ language plpgsql;

-- Trigger to validate on insert/update
create trigger validate_metadata
  before insert or update on products
  for each row
  execute function validate_metadata_schema_trigger();

create or replace function validate_metadata_schema_trigger()
returns trigger as $$
begin
  perform validate_metadata_schema(new.metadata);
  return new;
end;
$$ language plpgsql;

-- JSON Schema validation (requires json-schema extension)
-- Install extension
create extension if not exists jsonschema;

-- Define schema
create table json_schemas (
  id serial primary key,
  name text unique not null,
  schema jsonb not null
);

insert into json_schemas (name, schema) values
('product_metadata', '{
  "type": "object",
  "required": ["color", "size"],
  "properties": {
    "color": {"type": "string"},
    "size": {"type": "string", "enum": ["S", "M", "L", "XL"]},
    "price": {"type": "number", "minimum": 0},
    "tags": {
      "type": "array",
      "items": {"type": "string"},
      "maxItems": 10
    }
  }
}'::jsonb);

-- Validate function
create or replace function validate_against_schema(
  data jsonb,
  schema_name text
)
returns boolean as $$
declare
  schema_def jsonb;
begin
  select schema into schema_def
  from json_schemas
  where name = schema_name;

  if schema_def is null then
    raise exception 'Schema % not found', schema_name;
  end if;

  -- Validate using json-schema
  return jsonb_matches_schema(schema_def, data);
end;
$$ language plpgsql;

// Querying JSONB in Supabase client
import { createClient } from '@supabase/supabase-js'

const supabase = createClient(url, key)

// Query by JSONB field
const { data: products } = await supabase
  .from('products')
  .select('*')
  .eq('metadata->>color', 'blue')

// Query nested field
const { data: laptops } = await supabase
  .from('products')
  .select('*')
  .eq('metadata->specs->>cpu', 'Intel i7')

// Contains query
const { data: activeUsers } = await supabase
  .from('user_profiles')
  .select('*')
  .contains('settings', { theme: 'dark' })

// Update JSONB field
await supabase
  .from('user_profiles')
  .update({
    settings: {
      theme: 'dark',
      notifications: {
        email: true,
        push: false
      }
    }
  })
  .eq('id', userId)

// Update specific nested field with RPC
await supabase.rpc('update_user_setting', {
  p_user_id: userId,
  p_path: ['theme'],
  p_value: 'dark'
})

// hooks/useUserSettings.ts
import { useState, useEffect } from 'react'
import { createClient } from '@/lib/supabase/client'

interface UserSettings {
  theme: 'light' | 'dark' | 'auto'
  notifications: {
    email: boolean
    push: boolean
  }
  language: string
}

export function useUserSettings() {
  const [settings, setSettings] = useState<UserSettings | null>(null)
  const [loading, setLoading] = useState(true)
  const supabase = createClient()

  useEffect(() => {
    loadSettings()
  }, [])

  async function loadSettings() {
    const { data: { user } } = await supabase.auth.getUser()
    if (!user) return

    const { data } = await supabase
      .from('user_profiles')
      .select('settings')
      .eq('id', user.id)
      .single()

    if (data) {
      setSettings(data.settings as UserSettings)
    }
    setLoading(false)
  }

  async function updateSetting(key: keyof UserSettings, value: any) {
    const { data: { user } } = await supabase.auth.getUser()
    if (!user) return

    const newSettings = { ...settings, [key]: value }

    const { error } = await supabase
      .from('user_profiles')
      .update({ settings: newSettings })
      .eq('id', user.id)

    if (!error) {
      setSettings(newSettings)
    }
  }

  async function updateNestedSetting(path: string[], value: any) {
    const { data: { user } } = await supabase.auth.getUser()
    if (!user) return

    await supabase.rpc('update_user_setting', {
      p_user_id: user.id,
      p_path: path,
      p_value: JSON.stringify(value)
    })

    await loadSettings()
  }

  return {
    settings,
    loading,
    updateSetting,
    updateNestedSetting,
  }
}

// components/SettingsForm.tsx
import { useUserSettings } from '@/hooks/useUserSettings'

export function SettingsForm() {
  const { settings, updateSetting, updateNestedSetting } = useUserSettings()

  if (!settings) return <div>Loading...</div>

  return (
    <div className="space-y-4">
      <div>
        <label className="block mb-2">Theme</label>
        <select
          value={settings.theme}
          onChange={(e) => updateSetting('theme', e.target.value)}
          className="px-4 py-2 border rounded"
        >
          <option value="light">Light</option>
          <option value="dark">Dark</option>
          <option value="auto">Auto</option>
        </select>
      </div>

      <div>
        <label className="flex items-center gap-2">
          <input
            type="checkbox"
            checked={settings.notifications.email}
            onChange={(e) =>
              updateNestedSetting(
                ['notifications', 'email'],
                e.target.checked
              )
            }
          />
          Email Notifications
        </label>
      </div>
    </div>
  )
}