sf-soql: Salesforce SOQL Query Expert Skill

sf-soql: Salesforce SOQL Query Expert

Expert database engineer specializing in Salesforce Object Query Language (SOQL). Generate optimized queries from natural language, analyze query performance, and ensure best practices for governor limits and security.

Core Responsibilities

Natural Language → SOQL: Convert plain English requests to optimized queries
Query Optimization: Analyze and improve query performance
Relationship Queries: Build parent-child and child-parent traversals
Aggregate Functions: COUNT, SUM, AVG, MIN, MAX with GROUP BY
Security Enforcement: Ensure FLS and sharing rules compliance
Governor Limit Awareness: Design queries within limits

Workflow (4-Phase Pattern)

Phase 1: Requirements Gathering

Use AskUserQuestion to gather:

What data is needed (objects, fields)
Filter criteria (WHERE conditions)
Sort requirements (ORDER BY)
Record limit requirements
Use case (display, processing, reporting)

Phase 2: Query Generation

Natural Language Examples:

| Request | Generated SOQL | |---------|----------------| | "Get all active accounts with their contacts" | SELECT Id, Name, (SELECT Id, Name FROM Contacts) FROM Account WHERE IsActive__c = true | | "Find contacts created this month" | SELECT Id, Name, Email FROM Contact WHERE CreatedDate = THIS_MONTH | | "Count opportunities by stage" | SELECT StageName, COUNT(Id) FROM Opportunity GROUP BY StageName | | "Get accounts with revenue over 1M sorted by name" | SELECT Id, Name, AnnualRevenue FROM Account WHERE AnnualRevenue > 1000000 ORDER BY Name |

Phase 3: Optimization

Query Optimization Checklist:

Selectivity: Does WHERE clause use indexed fields?
Field Selection: Only query needed fields (not SELECT *)
Limit: Is LIMIT appropriate for use case?
Relationship Depth: Avoid deep traversals (max 5 levels)
Aggregate Queries: Use for counts instead of loading all records

Phase 4: Validation & Execution

# Test query
sf data query --query "SELECT Id, Name FROM Account LIMIT 10" --target-org my-org

# Analyze query plan
sf data query --query "..." --target-org my-org --use-tooling-api --plan

Best Practices (100-Point Scoring)

| Category | Points | Key Rules | |----------|--------|-----------| | Selectivity | 25 | Indexed fields in WHERE, selective filters | | Performance | 25 | Appropriate LIMIT, minimal fields, no unnecessary joins | | Security | 20 | WITH SECURITY_ENFORCED or stripInaccessible | | Correctness | 15 | Proper syntax, valid field references | | Readability | 15 | Formatted, meaningful aliases, comments |

Scoring Thresholds:

⭐⭐⭐⭐⭐ 90-100 pts → Production-optimized query
⭐⭐⭐⭐   80-89 pts  → Good query, minor optimizations possible
⭐⭐⭐    70-79 pts   → Functional, performance concerns
⭐⭐      60-69 pts   → Basic query, needs improvement
⭐        <60 pts    → Problematic query

SOQL Reference

Basic Query Structure

SELECT field1, field2, ...
FROM ObjectName
WHERE condition1 AND condition2
ORDER BY field1 ASC/DESC
LIMIT number
OFFSET number

Field Selection

-- Specific fields (recommended)
SELECT Id, Name, Industry FROM Account

-- All fields (avoid in Apex - use only in Developer Console)
SELECT FIELDS(ALL) FROM Account LIMIT 200

-- Standard fields only
SELECT FIELDS(STANDARD) FROM Account

WHERE Clause Operators

| Operator | Example | Notes | |----------|---------|-------| | = | Name = 'Acme' | Exact match | | != | Status != 'Closed' | Not equal | | <, >, <=, >= | Amount > 1000 | Comparison | | LIKE | Name LIKE 'Acme%' | Wildcard match | | IN | Status IN ('New', 'Open') | Multiple values | | NOT IN | Type NOT IN ('Other') | Exclude values | | INCLUDES | Interests__c INCLUDES ('Golf') | Multi-select picklist | | EXCLUDES | Interests__c EXCLUDES ('Golf') | Multi-select exclude |

Date Literals

| Literal | Meaning | |---------|---------| | TODAY | Current day | | YESTERDAY | Previous day | | THIS_WEEK | Current week (Sun-Sat) | | LAST_WEEK | Previous week | | THIS_MONTH | Current month | | LAST_MONTH | Previous month | | THIS_QUARTER | Current quarter | | THIS_YEAR | Current year | | LAST_N_DAYS:n | Last n days | | NEXT_N_DAYS:n | Next n days |

-- Created in last 30 days
SELECT Id FROM Account WHERE CreatedDate = LAST_N_DAYS:30

-- Modified this month
SELECT Id FROM Contact WHERE LastModifiedDate = THIS_MONTH

Relationship Queries

Child-to-Parent (Dot Notation)

-- Access parent fields
SELECT Id, Name, Account.Name, Account.Industry
FROM Contact
WHERE Account.AnnualRevenue > 1000000

-- Up to 5 levels
SELECT Id, Contact.Account.Owner.Manager.Name
FROM Case

Parent-to-Child (Subquery)

-- Get parent with related children
SELECT Id, Name,
       (SELECT Id, FirstName, LastName FROM Contacts),
       (SELECT Id, Name, Amount FROM Opportunities WHERE StageName = 'Closed Won')
FROM Account
WHERE Industry = 'Technology'

Relationship Names

| Object | Relationship Name | Example | |--------|-------------------|---------| | Account → Contacts | Contacts | (SELECT Id FROM Contacts) | | Account → Opportunities | Opportunities | (SELECT Id FROM Opportunities) | | Account → Cases | Cases | (SELECT Id FROM Cases) | | Contact → Cases | Cases | (SELECT Id FROM Cases) | | Opportunity → OpportunityLineItems | OpportunityLineItems | (SELECT Id FROM OpportunityLineItems) |

Custom Object Relationships

-- Custom relationship: add __r suffix
SELECT Id, Name, Custom_Object__r.Name
FROM Another_Object__c

-- Child relationship: add __r suffix
SELECT Id, (SELECT Id FROM Custom_Children__r)
FROM Parent_Object__c

Aggregate Queries

Basic Aggregates

-- Count all records
SELECT COUNT() FROM Account

-- Count with alias
SELECT COUNT(Id) cnt FROM Account

-- Sum, Average, Min, Max
SELECT SUM(Amount), AVG(Amount), MIN(Amount), MAX(Amount)
FROM Opportunity
WHERE StageName = 'Closed Won'

GROUP BY

-- Count by field
SELECT Industry, COUNT(Id)
FROM Account
GROUP BY Industry

-- Multiple groupings
SELECT StageName, CALENDAR_YEAR(CloseDate), COUNT(Id)
FROM Opportunity
GROUP BY StageName, CALENDAR_YEAR(CloseDate)

HAVING Clause

-- Filter aggregated results
SELECT Industry, COUNT(Id) cnt
FROM Account
GROUP BY Industry
HAVING COUNT(Id) > 10

GROUP BY ROLLUP

-- Subtotals
SELECT LeadSource, Rating, COUNT(Id)
FROM Lead
GROUP BY ROLLUP(LeadSource, Rating)

Query Optimization

Indexing Strategy

Indexed Fields (Always Selective):

Id
Name
OwnerId
CreatedDate
LastModifiedDate
RecordTypeId
External ID fields
Master-Detail relationship fields
Lookup fields (when unique)

Standard Indexed Fields by Object:

Account: AccountNumber, Site
Contact: Email
Lead: Email
Case: CaseNumber
Opportunity: -

Selectivity Rules

A filter is selective when it returns:
- < 10% of total records for first 1 million
- < 5% of total records for additional records
- OR uses an indexed field

Optimization Patterns

-- ❌ NON-SELECTIVE (scans all records)
SELECT Id FROM Lead WHERE Status = 'Open'

-- ✅ SELECTIVE (uses index + selective filter)
SELECT Id FROM Lead
WHERE Status = 'Open'
AND CreatedDate = LAST_N_DAYS:30
LIMIT 10000

-- ❌ LEADING WILDCARD (can't use index)
SELECT Id FROM Account WHERE Name LIKE '%corp'

-- ✅ TRAILING WILDCARD (uses index)
SELECT Id FROM Account WHERE Name LIKE 'Acme%'

Query Plan Analysis

# Get query plan
sf data query \
  --query "SELECT Id FROM Account WHERE Name = 'Test'" \
  --target-org my-org \
  --use-tooling-api \
  --plan

Plan Output Interpretation:

Cardinality: Estimated rows returned
Cost: Relative query cost (lower is better)
Fields: Index fields used
LeadingOperationType: How the query starts (Index vs TableScan)

Security Patterns

WITH SECURITY_ENFORCED

-- Throws exception if user lacks FLS
SELECT Id, Name, Phone
FROM Account
WITH SECURITY_ENFORCED

WITH USER_MODE / SYSTEM_MODE

-- Respects sharing rules (default in Apex)
SELECT Id, Name FROM Account WITH USER_MODE

-- Bypasses sharing rules (use with caution)
SELECT Id, Name FROM Account WITH SYSTEM_MODE

In Apex: stripInaccessible

// Strip inaccessible fields instead of throwing
SObjectAccessDecision decision = Security.stripInaccessible(
    AccessType.READABLE,
    [SELECT Id, Name, SecretField__c FROM Account]
);
List<Account> safeAccounts = decision.getRecords();

Governor Limits

| Limit | Synchronous | Asynchronous | |-------|-------------|--------------| | Total SOQL Queries | 100 | 200 | | Records Retrieved | 50,000 | 50,000 | | Query Rows (queryMore) | 2,000 | 2,000 | | Query Locator Rows | 10 million | 10 million |

Efficient Patterns

-- ❌ Query all, filter in Apex
SELECT Id, Name FROM Account
-- Then filter 50,000 records in Apex

-- ✅ Filter in SOQL
SELECT Id, Name FROM Account
WHERE Industry = 'Technology' AND IsActive__c = true
LIMIT 1000

-- ❌ Multiple queries in loop
for (Contact c : contacts) {
    Account a = [SELECT Name FROM Account WHERE Id = :c.AccountId];
}

-- ✅ Single query with Map
Map<Id, Account> accounts = new Map<Id, Account>(
    [SELECT Id, Name FROM Account WHERE Id IN :accountIds]
);

SOQL FOR Loops

// For large datasets - doesn't load all into heap
for (Account acc : [SELECT Id, Name FROM Account WHERE Industry = 'Technology']) {
    // Process one record at a time
    // Governor: Uses queryMore internally (200 at a time)
}

// With explicit batch size
for (List<Account> accs : [SELECT Id, Name FROM Account]) {
    // Process 200 records at a time
}

Advanced Features

Polymorphic Relationships (What)

-- Query polymorphic fields
SELECT Id, What.Name, What.Type
FROM Task
WHERE What.Type IN ('Account', 'Opportunity')

-- TYPEOF for conditional fields
SELECT
    TYPEOF What
        WHEN Account THEN Name, Phone
        WHEN Opportunity THEN Name, Amount
    END
FROM Task

Semi-Joins and Anti-Joins

-- Semi-join: Records that HAVE related records
SELECT Id, Name FROM Account
WHERE Id IN (SELECT AccountId FROM Contact)

-- Anti-join: Records that DON'T HAVE related records
SELECT Id, Name FROM Account
WHERE Id NOT IN (SELECT AccountId FROM Opportunity)

Format in Aggregate Queries

-- Format currency/date in results
SELECT FORMAT(Amount), FORMAT(CloseDate)
FROM Opportunity

convertCurrency()

-- Convert to user's currency
SELECT Id, convertCurrency(Amount)
FROM Opportunity

CLI Commands

Execute Query

# Basic query
sf data query --query "SELECT Id, Name FROM Account LIMIT 10" --target-org my-org

# JSON output
sf data query --query "SELECT Id, Name FROM Account" --target-org my-org --json

# CSV output
sf data query --query "SELECT Id, Name FROM Account" --target-org my-org --result-format csv

Bulk Query

# For large datasets
sf data query --query "SELECT Id, Name FROM Account" --target-org my-org --bulk

Query Plan

sf data query \
  --query "SELECT Id FROM Account WHERE Name = 'Test'" \
  --target-org my-org \
  --use-tooling-api \
  --plan

Cross-Skill Integration

| Skill | When to Use | Example | |-------|-------------|---------| | sf-apex | Embed queries in Apex | Skill(skill="sf-apex", args="Create service with SOQL query for accounts") | | sf-data | Execute queries against org | Skill(skill="sf-data", args="Query active accounts from production") | | sf-debug | Analyze query performance | Skill(skill="sf-debug", args="Analyze slow query in debug logs") | | sf-lwc | Generate wire queries | Skill(skill="sf-lwc", args="Create component with wired account query") |

Natural Language Examples

| Request | SOQL | |---------|------| | "Get me all accounts" | SELECT Id, Name FROM Account LIMIT 1000 | | "Find contacts without email" | SELECT Id, Name FROM Contact WHERE Email = null | | "Accounts created by John Smith" | SELECT Id, Name FROM Account WHERE CreatedBy.Name = 'John Smith' | | "Top 10 opportunities by amount" | SELECT Id, Name, Amount FROM Opportunity ORDER BY Amount DESC LIMIT 10 | | "Accounts in California" | SELECT Id, Name FROM Account WHERE BillingState = 'CA' | | "Contacts with @gmail emails" | SELECT Id, Name, Email FROM Contact WHERE Email LIKE '%@gmail.com' | | "Opportunities closing this quarter" | SELECT Id, Name, CloseDate FROM Opportunity WHERE CloseDate = THIS_QUARTER | | "Cases opened in last 7 days" | SELECT Id, Subject FROM Case WHERE CreatedDate = LAST_N_DAYS:7 | | "Total revenue by industry" | SELECT Industry, SUM(AnnualRevenue) FROM Account GROUP BY Industry | | "Accounts with more than 5 contacts" | SELECT Id, Name, (SELECT Id FROM Contacts) FROM Account + filter in Apex |

Dependencies

Required: Target org with sf CLI authenticated

Recommended:

sf-debug (for query plan analysis)
sf-apex (for embedding in Apex code)

Install: /plugin install github:Jaganpro/sf-skills/sf-soql

Documentation

| Document | Description | |----------|-------------| | soql-reference.md | Complete SOQL syntax reference | | cli-commands.md | SF CLI query commands | | anti-patterns.md | Common mistakes and how to avoid them | | selector-patterns.md | Query abstraction patterns (vanilla Apex) |

Templates

| Template | Description | |----------|-------------| | basic-queries.soql | Basic SOQL syntax examples | | aggregate-queries.soql | COUNT, SUM, GROUP BY patterns | | relationship-queries.soql | Parent-child traversals | | optimization-patterns.soql | Selectivity and indexing | | selector-class.cls | Selector class template | | bulkified-query-pattern.cls | Map-based bulk lookups |

Credits

See CREDITS.md for acknowledgments of community resources that shaped this skill.

Agent Skills: sf-soql: Salesforce SOQL Query Expert

Skill Files