# How to Query JSON Fields in Supabase

- Tool: Supabase
- Difficulty: Beginner
- Time required: 10-15 min
- Compatibility: Supabase (all plans), @supabase/supabase-js v2+, PostgreSQL 14+
- Last updated: March 2026

## TL;DR

Supabase stores JSON data in PostgreSQL jsonb columns, which you can query using both the JavaScript client and raw SQL. The JS client uses arrow notation in select() to extract nested values, and filter methods like .eq() and .contains() to query inside JSON objects. In SQL, use -> to get a JSON element and ->> to get a text value. Add GIN indexes on jsonb columns for fast lookups on nested fields.

## Querying JSONB Data in Supabase

PostgreSQL's jsonb type lets you store flexible, schema-less data alongside your structured columns. Supabase exposes powerful JSONB query capabilities through both its JavaScript client and SQL. This tutorial shows you how to select specific fields from JSON columns, filter rows based on nested values, and add indexes to keep JSON queries fast as your data grows.

## Before you start

- A Supabase project with a table containing a jsonb column
- @supabase/supabase-js v2+ installed in your project
- Basic understanding of JSON structure and PostgreSQL data types

## Step-by-step guide

### 1. Create a table with a jsonb column

Before querying JSON data, you need a table with a jsonb column. The jsonb type stores JSON in a decomposed binary format that is faster to query than the plain json type. Use jsonb for any column that will be filtered or indexed. Add some sample data with nested objects to practice querying.

```
-- Create a table with a jsonb metadata column
CREATE TABLE products (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name text NOT NULL,
  metadata jsonb DEFAULT '{}',
  created_at timestamptz DEFAULT now()
);

-- Enable RLS
ALTER TABLE products ENABLE ROW LEVEL SECURITY;

-- Allow authenticated users to read
CREATE POLICY "Authenticated users can read products"
  ON products FOR SELECT TO authenticated USING (true);

-- Insert sample data with nested JSON
INSERT INTO products (name, metadata) VALUES
  ('Widget A', '{"color": "red", "weight": 1.5, "tags": ["sale", "new"], "dimensions": {"width": 10, "height": 5}}'),
  ('Widget B', '{"color": "blue", "weight": 2.0, "tags": ["popular"], "dimensions": {"width": 15, "height": 8}}'),
  ('Widget C', '{"color": "red", "weight": 0.8, "tags": ["sale", "popular"], "dimensions": {"width": 8, "height": 3}}');
```

**Expected result:** A products table with sample JSON data in the metadata column.

### 2. Select nested JSON values with the JS client

The Supabase JS client lets you extract specific JSON fields in the select() method using arrow notation. Use -> to navigate into nested objects. Supabase translates this into PostgreSQL's -> and ->> operators behind the scenes. You can select multiple nested fields and alias them for cleaner results.

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

const supabase = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!
)

// Select specific JSON fields
const { data } = await supabase
  .from('products')
  .select('name, metadata->color, metadata->weight')

// Select nested fields (dimensions.width)
const { data: nested } = await supabase
  .from('products')
  .select('name, metadata->dimensions->width')

// Result: [{ name: 'Widget A', width: 10 }, ...]
```

**Expected result:** Each row contains only the selected JSON fields alongside the regular columns.

### 3. Filter rows by JSON field values

You can filter rows based on values inside JSON columns using the standard filter methods. Use the arrow path syntax to reference nested fields in .eq(), .gt(), .lt(), and other filters. For checking if a JSON array contains a value, use the .contains() method. For checking if a JSON object contains a specific key-value pair, also use .contains() with a JSON object.

```
// Filter by a top-level JSON field
const { data: redProducts } = await supabase
  .from('products')
  .select('*')
  .eq('metadata->color', '"red"') // Note: JSON string values need quotes

// Filter by a nested JSON field
const { data: wideProducts } = await supabase
  .from('products')
  .select('*')
  .gt('metadata->weight', 1.0)

// Check if a JSON array contains a value
const { data: saleProducts } = await supabase
  .from('products')
  .select('*')
  .contains('metadata->tags', '["sale"]')

// Check if JSON contains a key-value pair
const { data: matched } = await supabase
  .from('products')
  .select('*')
  .contains('metadata', '{"color": "red"}')
```

**Expected result:** Only rows matching the JSON field condition are returned.

### 4. Query JSON with raw SQL operators

For complex JSON queries, use the SQL Editor or supabase.rpc() with PostgreSQL's native JSON operators. The -> operator returns a JSON element, ->> returns a text value, @> checks containment, and ? checks if a key exists. These operators give you full control for queries that the JS client cannot express directly.

```
-- Select a JSON field as text (no quotes)
SELECT name, metadata->>'color' AS color
FROM products;

-- Filter by nested value
SELECT * FROM products
WHERE metadata->'dimensions'->>'width' > '10';

-- Check containment (does metadata contain this object?)
SELECT * FROM products
WHERE metadata @> '{"color": "red"}';

-- Check if a key exists
SELECT * FROM products
WHERE metadata ? 'weight';

-- Check if any of these keys exist
SELECT * FROM products
WHERE metadata ?| array['color', 'size'];

-- Access array element by index
SELECT name, metadata->'tags'->>0 AS first_tag
FROM products;
```

**Expected result:** SQL queries return the expected filtered and extracted JSON data.

### 5. Add GIN indexes for fast JSON queries

Without indexes, JSON queries perform a sequential scan on every row. Add a GIN index on the jsonb column to speed up containment (@>), key existence (?), and array operations. For queries that filter on a specific JSON path, create a targeted btree index on the extracted value for even better performance.

```
-- General-purpose GIN index for @> and ? operators
CREATE INDEX idx_products_metadata ON products USING gin (metadata);

-- Targeted btree index for a specific field
-- Best for .eq() and comparison filters on one path
CREATE INDEX idx_products_color ON products
  USING btree ((metadata->>'color'));

-- Index for numeric comparisons on a JSON field
CREATE INDEX idx_products_weight ON products
  USING btree (((metadata->>'weight')::numeric));

-- Verify the index is being used
EXPLAIN (ANALYZE)
SELECT * FROM products
WHERE metadata @> '{"color": "red"}';
```

**Expected result:** EXPLAIN ANALYZE shows Index Scan instead of Seq Scan, with significantly faster query execution.

## Complete code example

File: `query-json-fields.ts`

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

const supabase = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!
)

// ---- Selecting JSON fields ----

// Select specific nested values
async function getProductColors() {
  const { data, error } = await supabase
    .from('products')
    .select('name, metadata->color, metadata->dimensions->width')
  return { data, error }
}

// ---- Filtering by JSON values ----

// Filter by JSON string field
async function getProductsByColor(color: string) {
  const { data, error } = await supabase
    .from('products')
    .select('*')
    .eq('metadata->color', `"${color}"`)
  return { data, error }
}

// Filter by JSON numeric field
async function getProductsHeavierThan(weight: number) {
  const { data, error } = await supabase
    .from('products')
    .select('*')
    .gt('metadata->weight', weight)
  return { data, error }
}

// Filter by JSON array containment
async function getProductsWithTag(tag: string) {
  const { data, error } = await supabase
    .from('products')
    .select('*')
    .contains('metadata->tags', `["${tag}"]`)
  return { data, error }
}

// Filter by JSON object containment
async function getProductsMatching(criteria: Record<string, any>) {
  const { data, error } = await supabase
    .from('products')
    .select('*')
    .contains('metadata', JSON.stringify(criteria))
  return { data, error }
}

// ---- Usage examples ----

async function main() {
  const colors = await getProductColors()
  console.log('Colors:', colors.data)

  const redProducts = await getProductsByColor('red')
  console.log('Red products:', redProducts.data)

  const heavy = await getProductsHeavierThan(1.0)
  console.log('Heavy products:', heavy.data)

  const onSale = await getProductsWithTag('sale')
  console.log('On sale:', onSale.data)
}

main()
```

## Common mistakes

- **Forgetting to wrap JSON string values in double quotes when using .eq()** — undefined Fix: JSON stores strings with quotes. Use .eq('metadata->color', '"red"') with the inner quotes. Numeric values like .gt('metadata->weight', 1.5) do not need quotes.
- **Using the json column type instead of jsonb** — undefined Fix: Always use jsonb for columns you query or index. The json type stores raw text and cannot be indexed with GIN. Alter existing columns: ALTER TABLE products ALTER COLUMN metadata TYPE jsonb USING metadata::jsonb;
- **Comparing ->> text output to numbers without casting in SQL** — undefined Fix: The ->> operator returns text. For numeric comparisons, cast explicitly: WHERE (metadata->>'weight')::numeric > 1.5. Without the cast, '9' > '10' because text comparison is alphabetical.
- **Not adding indexes on jsonb columns, leading to full table scans** — undefined Fix: Add a GIN index for containment queries or a btree index on extracted paths for equality/range queries. Without indexes, every JSON query scans every row.

## Best practices

- Use jsonb instead of json for all columns that need querying or indexing
- Add a GIN index on the jsonb column for general-purpose containment and key-existence queries
- Use targeted btree indexes on specific extracted paths for high-frequency equality or range filters
- Use -> to get JSON elements and ->> to get text values in SQL queries
- Cast ->> text output to the appropriate type for numeric or date comparisons
- Keep JSON structure consistent across rows — inconsistent keys make filtering unreliable
- Consider extracting frequently queried JSON fields into dedicated columns for better performance and type safety

## Frequently asked questions

### What is the difference between -> and ->> in PostgreSQL?

The -> operator returns a JSON element (preserving the JSON type), while ->> returns the value as plain text. Use -> for navigating nested objects and ->> when you need the final text value for comparisons or display.

### Can I update a single field inside a jsonb column without replacing the entire object?

Yes. Use the jsonb_set function in SQL: UPDATE products SET metadata = jsonb_set(metadata, '{color}', '"green"') WHERE id = 'some-id'. Via the JS client, you currently need to read the full JSON, modify it, and write it back.

### Should I use jsonb columns or separate tables for structured data?

Use separate tables for data with a consistent schema that you frequently query and join. Use jsonb for truly flexible or variable data like user preferences, feature flags, or third-party API responses where the structure varies.

### Does RLS work on jsonb columns?

Yes. You can reference jsonb fields in RLS policies. For example: USING (metadata->>'tenant_id' = (select auth.jwt()->>'tenant_id')). However, complex JSON operations in RLS policies can slow down every query, so keep policies simple.

### How do I query a jsonb array for partial matches?

Use the @> containment operator. For example, to find rows where tags contains 'sale': WHERE metadata->'tags' @> '["sale"]'. This works efficiently with a GIN index on the jsonb column.

### Can RapidDev help design my Supabase database schema with JSON columns?

Yes. RapidDev can help you decide where to use jsonb versus relational tables, design proper indexes for JSON queries, and implement efficient query patterns for your specific data model.

---

Source: https://www.rapidevelopers.com/supabase-tutorial/how-to-query-json-fields-in-supabase
© RapidDev — https://www.rapidevelopers.com/supabase-tutorial/how-to-query-json-fields-in-supabase