How to Query JSON Fields in Supabase

1-- Create a table with a jsonb metadata column
2CREATE TABLE products (
3  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
4  name text NOT NULL,
5  metadata jsonb DEFAULT '{}',
6  created_at timestamptz DEFAULT now()
7);
8
9-- Enable RLS
10ALTER TABLE products ENABLE ROW LEVEL SECURITY;
11
12-- Allow authenticated users to read
13CREATE POLICY "Authenticated users can read products"
14  ON products FOR SELECT TO authenticated USING (true);
15
16-- Insert sample data with nested JSON
17INSERT INTO products (name, metadata) VALUES
18  ('Widget A', '{"color": "red", "weight": 1.5, "tags": ["sale", "new"], "dimensions": {"width": 10, "height": 5}}'),
19  ('Widget B', '{"color": "blue", "weight": 2.0, "tags": ["popular"], "dimensions": {"width": 15, "height": 8}}'),
20  ('Widget C', '{"color": "red", "weight": 0.8, "tags": ["sale", "popular"], "dimensions": {"width": 8, "height": 3}}');

1import { createClient } from '@supabase/supabase-js'
2
3const supabase = createClient(
4  process.env.NEXT_PUBLIC_SUPABASE_URL!,
5  process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!
6)
7
8// Select specific JSON fields
9const { data } = await supabase
10  .from('products')
11  .select('name, metadata->color, metadata->weight')
12
13// Select nested fields (dimensions.width)
14const { data: nested } = await supabase
15  .from('products')
16  .select('name, metadata->dimensions->width')
17
18// Result: [{ name: 'Widget A', width: 10 }, ...]

1// Filter by a top-level JSON field
2const { data: redProducts } = await supabase
3  .from('products')
4  .select('*')
5  .eq('metadata->color', '"red"') // Note: JSON string values need quotes
6
7// Filter by a nested JSON field
8const { data: wideProducts } = await supabase
9  .from('products')
10  .select('*')
11  .gt('metadata->weight', 1.0)
12
13// Check if a JSON array contains a value
14const { data: saleProducts } = await supabase
15  .from('products')
16  .select('*')
17  .contains('metadata->tags', '["sale"]')
18
19// Check if JSON contains a key-value pair
20const { data: matched } = await supabase
21  .from('products')
22  .select('*')
23  .contains('metadata', '{"color": "red"}')

1-- Select a JSON field as text (no quotes)
2SELECT name, metadata->>'color' AS color
3FROM products;
4
5-- Filter by nested value
6SELECT * FROM products
7WHERE metadata->'dimensions'->>'width' > '10';
8
9-- Check containment (does metadata contain this object?)
10SELECT * FROM products
11WHERE metadata @> '{"color": "red"}';
12
13-- Check if a key exists
14SELECT * FROM products
15WHERE metadata ? 'weight';
16
17-- Check if any of these keys exist
18SELECT * FROM products
19WHERE metadata ?| array['color', 'size'];
20
21-- Access array element by index
22SELECT name, metadata->'tags'->>0 AS first_tag
23FROM products;

1-- General-purpose GIN index for @> and ? operators
2CREATE INDEX idx_products_metadata ON products USING gin (metadata);
3
4-- Targeted btree index for a specific field
5-- Best for .eq() and comparison filters on one path
6CREATE INDEX idx_products_color ON products
7  USING btree ((metadata->>'color'));
8
9-- Index for numeric comparisons on a JSON field
10CREATE INDEX idx_products_weight ON products
11  USING btree (((metadata->>'weight')::numeric));
12
13-- Verify the index is being used
14EXPLAIN (ANALYZE)
15SELECT * FROM products
16WHERE metadata @> '{"color": "red"}';