Write SQL Queries for Redis

pip install redisvl[sql-redis]

from redisvl.utils.vectorize import HFTextVectorizer

hf = HFTextVectorizer()

schema = {
    "index": {
        "name": "user_simple",
        "prefix": "user_simple_docs",
        "storage_type": "json",
    },
    "fields": [
        {"name": "user", "type": "tag"},
        {"name": "region", "type": "tag"},
        {"name": "job", "type": "tag"},
        {"name": "job_description", "type": "text"},
        {"name": "age", "type": "numeric"},
        {"name": "office_location", "type": "geo"},
        {
            "name": "job_embedding",
            "type": "vector",
            "attrs": {
                "dims": len(hf.embed("get embed length")),
                "distance_metric": "cosine",
                "algorithm": "flat",
                "datatype": "float32"
            }
        }
    ]
}

# Office locations use "longitude,latitude" format (lon,lat - Redis convention)
# San Francisco: -122.4194, 37.7749
# Chicago: -87.6298, 41.8781
# New York: -73.9857, 40.7580
data = [
    {
        'user': 'john',
        'age': 34,
        'job': 'software engineer',
        'region': 'us-west',
        'job_description': 'Designs, develops, and maintains software applications and systems.',
        'office_location': '-122.4194,37.7749'  # San Francisco
    },
    {
        'user': 'bill',
        'age': 54,
        'job': 'engineer',
        'region': 'us-central',
        'job_description': 'Applies scientific and mathematical principles to solve technical problems.',
        'office_location': '-87.6298,41.8781'  # Chicago
    },
    {
        'user': 'mary',
        'age': 24,
        'job': 'doctor',
        'region': 'us-central',
        'job_description': 'Diagnoses and treats illnesses, injuries, and other medical conditions in the healthcare field.',
        'office_location': '-87.6298,41.8781'  # Chicago
    },
    {
        'user': 'joe',
        'age': 27,
        'job': 'dentist',
        'region': 'us-east',
        'job_description': 'Provides oral healthcare including diagnosing and treating teeth and gum issues.',
        'office_location': '-73.9857,40.7580'  # New York
    },
    {
        'user': 'stacy',
        'age': 61,
        'job': 'project manager',
        'region': 'us-west',
        'job_description': 'Plans, organizes, and oversees projects from inception to completion.',
        'office_location': '-122.4194,37.7749'  # San Francisco
    }
]

data = [
    {  
        **d,
        "job_embedding": hf.embed(f"{d['job_description']=} {d['job']=}"),
    } 
    for d in data
]

from redisvl.index import SearchIndex
from redis import Redis

client = Redis.from_url("redis://localhost:6379")
index = SearchIndex.from_dict(schema, redis_client=client)

index = SearchIndex.from_dict(schema, redis_url="redis://localhost:6379")

index.create(overwrite=True, drop=True)

keys = index.load(data)

print(keys)

from redisvl.query import SQLQuery

sql_str = """
    SELECT user, region, job, age
    FROM user_simple
    WHERE age > 17
    """

# Optional sql_redis_options are passed through to sql-redis.
# schema_cache_strategy balances startup cost vs repeated-query speed:
# use "lazy" (default) to load schemas on demand, or "load_all"
# to preload schemas up front for broader repeated-query workloads.
sql_query = SQLQuery(
    sql_str, sql_redis_options={"schema_cache_strategy": "lazy"}
)

sql_query.redis_query_string(redis_url="redis://localhost:6379")

results = index.query(sql_query)
results

sql_str = """
    SELECT user, region, job, age
    FROM user_simple
    WHERE age > 17 and region = 'us-west'
"""

sql_query = SQLQuery(sql_str)
redis_query = sql_query.redis_query_string(redis_url="redis://localhost:6379")
print("Resulting redis query: ", redis_query)
results = index.query(sql_query)
results

sql_str = """
    SELECT user, region, job, age
    FROM user_simple
    WHERE region = 'us-west' or region = 'us-central'
    """

sql_query = SQLQuery(sql_str)
redis_query = sql_query.redis_query_string(redis_url="redis://localhost:6379")
print("Resulting redis query: ", redis_query)
results = index.query(sql_query)
results

# job is a tag field therefore this syntax works
sql_str = """
    SELECT user, region, job, age
    FROM user_simple
    WHERE job IN ('software engineer', 'engineer', 'pancake tester')
    """

sql_query = SQLQuery(sql_str)
redis_query = sql_query.redis_query_string(redis_url="redis://localhost:6379")
print("Resulting redis query: ", redis_query)
results = index.query(sql_query)
results

# Prefix (LIKE)
sql_str = """
    SELECT user, region, job, job_description, age
    FROM user_simple
    WHERE job_description LIKE 'sci%'
"""

sql_query = SQLQuery(sql_str)
redis_query = sql_query.redis_query_string(redis_url="redis://localhost:6379")
print("Resulting redis query: ", redis_query)
results = index.query(sql_query)
results

# Suffix (LIKE)
sql_str = """
    SELECT user, region, job, job_description, age
    FROM user_simple
    WHERE job_description LIKE '%care'
"""

sql_query = SQLQuery(sql_str)
redis_query = sql_query.redis_query_string(redis_url="redis://localhost:6379")
print("Resulting redis query: ", redis_query)
results = index.query(sql_query)
results

# Contains (LIKE)
sql_str = """
    SELECT user, region, job, job_description, age
    FROM user_simple
    WHERE job_description LIKE '%diagnose%'
"""

sql_query = SQLQuery(sql_str)
redis_query = sql_query.redis_query_string(redis_url="redis://localhost:6379")
print("Resulting redis query: ", redis_query)
results = index.query(sql_query)
results

# Phrase no stop words
sql_str = """
    SELECT user, region, job, job_description, age
    FROM user_simple
    WHERE job_description = 'healthcare including'
"""

sql_query = SQLQuery(sql_str)
redis_query = sql_query.redis_query_string(redis_url="redis://localhost:6379")
print("Resulting redis query: ", redis_query)
results = index.query(sql_query)
results

# Phrase with stop words (sql-redis strips default stopwords and warns)
sql_str = """
    SELECT user, region, job, job_description, age
    FROM user_simple
    WHERE job_description = 'diagnosing and treating'
"""

sql_query = SQLQuery(sql_str)
redis_query = sql_query.redis_query_string(redis_url="redis://localhost:6379")
print("Resulting redis query: ", redis_query)
results = index.query(sql_query)
results

sql_str = """
    SELECT user, region, job, age
    FROM user_simple
    WHERE age BETWEEN 40 and 60
    """

sql_query = SQLQuery(sql_str)
redis_query = sql_query.redis_query_string(redis_url="redis://localhost:6379")
print("Resulting redis query: ", redis_query)
results = index.query(sql_query)
results

sql_str = """
    SELECT
        user,
        COUNT(age) as count_age,
        COUNT_DISTINCT(age) as count_distinct_age,
        MIN(age) as min_age,
        MAX(age) as max_age,
        AVG(age) as avg_age,
        STDEV(age) as std_age,
        FIRST_VALUE(age) as fist_value_age,
        ARRAY_AGG(age) as to_list_age,
        QUANTILE(age, 0.99) as quantile_age
    FROM user_simple
    GROUP BY region
    """

sql_query = SQLQuery(sql_str)
redis_query = sql_query.redis_query_string(redis_url="redis://localhost:6379")
print("Resulting redis query: ", redis_query)
results = index.query(sql_query)
results

sql_str = """
    SELECT user, job, job_description, cosine_distance(job_embedding, :vec) AS vector_distance
    FROM user_simple
    ORDER BY vector_distance ASC
    """

vec = hf.embed("looking for someone to use base principles to solve problems", as_buffer=True)
sql_query = SQLQuery(sql_str, params={"vec": vec})

redis_query = sql_query.redis_query_string(redis_url="redis://localhost:6379")
print("Resulting redis query: ", redis_query)
results = index.query(sql_query)

results

sql_str = """
    SELECT user, region, cosine_distance(job_embedding, :vec) AS vector_distance
    FROM user_simple
    WHERE region = 'us-central'
    ORDER BY vector_distance ASC
    """

vec = hf.embed("looking for someone to use base principles to solve problems", as_buffer=True)
sql_query = SQLQuery(sql_str, params={"vec": vec})

redis_query = sql_query.redis_query_string(redis_url="redis://localhost:6379")
print("Resulting redis query: ", redis_query)
results = index.query(sql_query)

results

# Find users within 500km of San Francisco
sql_str = """
    SELECT user, job, region, office_location
    FROM user_simple
    WHERE geo_distance(office_location, POINT(-122.4194, 37.7749), 'km') < 500
"""

sql_query = SQLQuery(sql_str)
redis_query = sql_query.redis_query_string(redis_url="redis://localhost:6379")
print("Resulting redis query: ", redis_query)
results = index.query(sql_query)
results

# Find users within 50 miles of Chicago (using miles)
sql_str = """
    SELECT user, job, region
    FROM user_simple
    WHERE geo_distance(office_location, POINT(-87.6298, 41.8781), 'mi') < 50
"""

sql_query = SQLQuery(sql_str)
redis_query = sql_query.redis_query_string(redis_url="redis://localhost:6379")
print("Resulting redis query: ", redis_query)
results = index.query(sql_query)
results

# Combine GEO filter with TAG filter - find engineers near Chicago
sql_str = """
    SELECT user, job, region
    FROM user_simple
    WHERE job = 'engineer' AND geo_distance(office_location, POINT(-87.6298, 41.8781), 'mi') < 50
"""

sql_query = SQLQuery(sql_str)
redis_query = sql_query.redis_query_string(redis_url="redis://localhost:6379")
print("Resulting redis query: ", redis_query)
results = index.query(sql_query)
results

# Combine GEO with NUMERIC filter - find users over 30 near San Francisco
sql_str = """
    SELECT user, job, age
    FROM user_simple
    WHERE age > 30 AND geo_distance(office_location, POINT(-122.4194, 37.7749), 'km') < 100
"""

sql_query = SQLQuery(sql_str)
redis_query = sql_query.redis_query_string(redis_url="redis://localhost:6379")
print("Resulting redis query: ", redis_query)
results = index.query(sql_query)
results

# Combine GEO with TEXT search - find users with "technical" in job description near Chicago
sql_str = """
    SELECT user, job, job_description
    FROM user_simple
    WHERE job_description LIKE 'technical%' AND geo_distance(office_location, POINT(-87.6298, 41.8781), 'km') < 100
"""

sql_query = SQLQuery(sql_str)
redis_query = sql_query.redis_query_string(redis_url="redis://localhost:6379")
print("Resulting redis query: ", redis_query)
results = index.query(sql_query)
results

# Calculate distances from New York to all users
# Note: geo_distance() in SELECT uses FT.AGGREGATE and returns distance in meters
sql_str = """
    SELECT user, region, geo_distance(office_location, POINT(-73.9857, 40.7580)) AS distance_meters
    FROM user_simple
"""

sql_query = SQLQuery(sql_str)
redis_query = sql_query.redis_query_string(redis_url="redis://localhost:6379")
print("Resulting redis query: ", redis_query)
results = index.query(sql_query)

# Convert meters to km for readability and sort by distance
print("\nDistances from NYC:")
for r in sorted(results, key=lambda x: float(x.get('distance_meters', 0))):
    dist_km = float(r.get('distance_meters', 0)) / 1000
    print(f"  {r['user']:10} | {r['region']:12} | {dist_km:,.0f} km")

# Create a separate index for date examples
from datetime import datetime, timezone

def to_timestamp(date_str):
    """Convert ISO date string to Unix timestamp (UTC)."""
    dt = datetime.strptime(date_str, "%Y-%m-%d")
    dt = dt.replace(tzinfo=timezone.utc)
    return int(dt.timestamp())

# Define schema with NUMERIC fields for timestamps
events_schema = {
    "index": {
        "name": "events",
        "prefix": "event:",
        "storage_type": "hash",
    },
    "fields": [
        {"name": "name", "type": "text", "attrs": {"sortable": True}},
        {"name": "category", "type": "tag", "attrs": {"sortable": True}},
        {"name": "created_at", "type": "numeric", "attrs": {"sortable": True}},
    ],
}

events_index = SearchIndex.from_dict(events_schema, redis_url="redis://localhost:6379")
events_index.create(overwrite=True)

# Sample events spanning 2023-2024
events = [
    {"name": "New Year Kickoff", "category": "meeting", "created_at": to_timestamp("2024-01-01")},
    {"name": "Q1 Planning", "category": "meeting", "created_at": to_timestamp("2024-01-15")},
    {"name": "Product Launch", "category": "release", "created_at": to_timestamp("2024-02-20")},
    {"name": "Team Offsite", "category": "meeting", "created_at": to_timestamp("2024-03-10")},
    {"name": "Summer Summit", "category": "conference", "created_at": to_timestamp("2024-07-15")},
    {"name": "Holiday Party 2023", "category": "conference", "created_at": to_timestamp("2023-12-15")},
    {"name": "Year End Review 2023", "category": "meeting", "created_at": to_timestamp("2023-12-20")},
]

events_index.load(events)

print(f"Loaded {len(events)} events:")
for e in events:
    date = datetime.fromtimestamp(e["created_at"], tz=timezone.utc).strftime("%Y-%m-%d")
    print(f"  - {e['name']:25} | {date} | {e['category']}")

# Find events after January 1st, 2024 using date literal
sql_str = """
    SELECT name, category
    FROM events
    WHERE created_at > '2024-01-01'
"""

sql_query = SQLQuery(sql_str)
redis_query = sql_query.redis_query_string(redis_url="redis://localhost:6379")
print("Resulting redis query: ", redis_query)
results = events_index.query(sql_query)

print(f"\nEvents after 2024-01-01 ({len(results)} found):")
for r in results:
    print(f"  - {r['name']}")

# Find events in Q1 2024 using BETWEEN
sql_str = """
    SELECT name, category
    FROM events
    WHERE created_at BETWEEN '2024-01-01' AND '2024-03-31'
"""

sql_query = SQLQuery(sql_str)
redis_query = sql_query.redis_query_string(redis_url="redis://localhost:6379")
print("Resulting redis query: ", redis_query)
results = events_index.query(sql_query)

print(f"\nEvents in Q1 2024 ({len(results)} found):")
for r in results:
    print(f"  - {r['name']} ({r['category']})")

# Combine date filter with TAG filter - find meetings in H1 2024
sql_str = """
    SELECT name
    FROM events
    WHERE category = 'meeting' AND created_at BETWEEN '2024-01-01' AND '2024-06-30'
"""

sql_query = SQLQuery(sql_str)
results = events_index.query(sql_query)

print(f"Meetings in H1 2024 ({len(results)} found):")
for r in results:
    print(f"  - {r['name']}")

from redisvl.index import AsyncSearchIndex
from redisvl.query import SQLQuery

# Create async index
async_index = AsyncSearchIndex.from_dict(schema, redis_url="redis://localhost:6379")

# Execute SQL query asynchronously
sql_query = SQLQuery(f"SELECT user, age FROM {async_index.name} WHERE age > 30")
results = await async_index.query(sql_query)

# Cleanup
await async_index.disconnect()

from redisvl.query import FilterQuery
from redisvl.query.filter import Geo, GeoRadius, Tag, Num

# Find users within 100km of Chicago using native filters
geo_filter = Geo("office_location") == GeoRadius(-87.6298, 41.8781, 100, "km")

print(f"Filter expression: {geo_filter}\n")

query = FilterQuery(
    filter_expression=geo_filter,
    return_fields=["user", "job", "region"]
)

results = index.query(query)
print(f"Users within 100km of Chicago ({len(results)} found):")
for r in results:
    print(f"  - {r['user']} ({r['job']}) - {r['region']}")

# Find users OUTSIDE 1000km of Chicago (using !=)
geo_filter_outside = Geo("office_location") != GeoRadius(-87.6298, 41.8781, 1000, "km")

print(f"Filter expression: {geo_filter_outside}\n")

query = FilterQuery(
    filter_expression=geo_filter_outside,
    return_fields=["user", "region"]
)

results = index.query(query)
print(f"Users OUTSIDE 1000km of Chicago ({len(results)} found):")
for r in results:
    print(f"  - {r['user']} ({r['region']})")

# Combine GEO + TAG + NUMERIC filters
# Find engineers over 40 within 500km of Chicago
geo_filter = Geo("office_location") == GeoRadius(-87.6298, 41.8781, 500, "km")
job_filter = Tag("job") == "engineer"
age_filter = Num("age") > 40

combined_filter = geo_filter & job_filter & age_filter

print(f"Combined filter: {combined_filter}\n")

query = FilterQuery(
    filter_expression=combined_filter,
    return_fields=["user", "job", "age", "region"]
)

results = index.query(query)
print(f"Engineers over 40 within 500km of Chicago ({len(results)} found):")
for r in results:
    print(f"  - {r['user']} (age: {r['age']}) - {r['region']}")

# Extract YEAR and MONTH using date functions in SELECT
sql_str = """
    SELECT name, YEAR(created_at) AS year, MONTH(created_at) AS month
    FROM events
"""

sql_query = SQLQuery(sql_str)
redis_query = sql_query.redis_query_string(redis_url="redis://localhost:6379")
print("Resulting redis query: ", redis_query)
results = events_index.query(sql_query)

print(f"\nEvents with year/month:")
for r in results:
    # Note: MONTH returns 0-11 in Redis (0=January)
    month_num = int(r.get('month', 0)) + 1
    print(f"  - {r['name']:25} | {r.get('year')}-{month_num:02d}")

# Filter by YEAR using date function in WHERE
sql_str = """
    SELECT name
    FROM events
    WHERE YEAR(created_at) = 2024
"""

sql_query = SQLQuery(sql_str)
redis_query = sql_query.redis_query_string(redis_url="redis://localhost:6379")
print("Resulting redis query: ", redis_query)
results = events_index.query(sql_query)

print(f"\nEvents in 2024 ({len(results)} found):")
for r in results:
    print(f"  - {r['name']}")

# Count events per year using GROUP BY
sql_str = """
    SELECT YEAR(created_at) AS year, COUNT(*) AS event_count
    FROM events
    GROUP BY year
"""

sql_query = SQLQuery(sql_str)
redis_query = sql_query.redis_query_string(redis_url="redis://localhost:6379")
print("Resulting redis query: ", redis_query)
results = events_index.query(sql_query)

print("\nEvents per year:")
for r in sorted(results, key=lambda x: x.get('year', 0)):
    print(f"  {r['year']}: {r['event_count']} events")

# Format dates using DATE_FORMAT
sql_str = """
    SELECT name, DATE_FORMAT(created_at, '%Y-%m-%d') AS event_date
    FROM events
"""

sql_query = SQLQuery(sql_str)
redis_query = sql_query.redis_query_string(redis_url="redis://localhost:6379")
print("Resulting redis query: ", redis_query)
results = events_index.query(sql_query)

print("\nEvents with formatted dates:")
for r in results:
    print(f"  - {r['name']:25} | {r.get('event_date', 'N/A')}")

# Delete both indexes and all associated data
events_index.delete(drop=True)
index.delete(drop=True)