FG API

Appearance

Metafield Runtime Filtering Guide ​

This document explains how to filter products using metafields with runtime (JavaScript) filtering instead of database-level SQL queries.

Reference: Shopify Metafield Types Documentation

Why Runtime Filtering? ​

The Problem: Shopify has ~48 different metafield types (19 list types, 11 reference types, 18 base types). Trying to create database columns for each type is impractical and complex.

The Solution: Store all metafield values as a single value string column, then filter products in JavaScript at runtime based on the metafield type field.

Why This Works:

  • ✅ Simple database schema (one value column)
  • ✅ Handles all 48 types without schema changes
  • ✅ Flexible filtering logic
  • ✅ Perfect for ~300 products (current FG quiz = true) (small/medium dataset, manageable in memory)
  • ✅ Progressive filtering example at runtime: 300 → 150 → 70 → final list for the AI.

Trade-offs:

  • ❌ Must fetch all products, then filter in memory
  • ❌ Can't use SQL indexes for filtering
  • ⚠️ Performance degrades if scaling to thousands of products

Schema Design ​

Simple Approach: Single value Column

prisma

model ProductMetafield {
  id          String   @id @default(cuid())
  productId   String
  namespace   String
  key         String
  value       String   // All values stored as string (Shopify API format)
  type        String   // Shopify type (e.g., 'single_line_text_field', 'list.single_line_text_field', 'money')
  ownerType   String
  definitionId String?
  // ... other fields
}

model VariantMetafield {
  id          String   @id @default(cuid())
  variantId   String
  namespace   String
  key         String
  value       String   // All values stored as string
  type        String   // Shopify type
  ownerType   String
  definitionId String?
  // ... other fields
}

Note: Shopify API always returns values as strings, so we store them as strings. The type field tells us how to parse and filter them.

The Use Case ​

  1. Start with ~300 products in database
  2. User fills out quiz/form with multiple questions
  3. Each question creates filter conditions:
    • Include products (must have X)
    • Exclude products (must NOT have X)
    • Filter by metafields (custom data)
    • Filter by product fields (vendor, tags, etc.)
    • Filter by variant fields and variant metafields
  4. All conditions combined progressively narrow down: 300 → 150 → 70 → final list
  5. Final filtered product list sent to AI for processing

Value Parsing by Type ​

Helper: Parse Metafield Value ​

javascript
/**
 * Parse metafield value based on its type
 * @param {Object} metafield - Metafield object with 'type' and 'value'
 * @returns {any} Parsed value (string, number, boolean, array, object, null)
 */
function parseMetafieldValue(metafield) {
  if (!metafield || !metafield.value) return null;
  
  const type = metafield.type;
  
  // Array types (list.*) - stored as JSON string
  if (type.startsWith('list.')) {
    try {
      return JSON.parse(metafield.value);
    } catch {
      return [];
    }
  }
  
  // JSON object types - stored as JSON string
  const jsonObjectTypes = [
    'money', 'dimension', 'weight', 'volume', 
    'rating', 'link', 'json', 'rich_text_field'
  ];
  if (jsonObjectTypes.includes(type)) {
    try {
      return JSON.parse(metafield.value);
    } catch {
      return null;
    }
  }
  
  // Number types - stored as string, parse to number
  if (type === 'number_integer' || type === 'number_decimal') {
    return parseFloat(metafield.value);
  }
  
  // Boolean - stored as string "true"/"false"
  if (type === 'boolean') {
    return metafield.value === 'true' || metafield.value === true;
  }
  
  // Everything else is string (dates, URLs, text, references, etc.)
  return metafield.value;
}

Standardized Filter Functions ​

Operator Functions ​

javascript
/**
 * Apply operator to single value
 */
function applyOperator(actualValue, operator, expectedValue) {
  if (actualValue === null || actualValue === undefined) return false;
  
  switch (operator) {
    case 'equals':
      return actualValue === expectedValue;
    
    case 'not_equals':
      return actualValue !== expectedValue;
    
    case 'contains':
      return String(actualValue).toLowerCase().includes(String(expectedValue).toLowerCase());
    
    case 'starts_with':
      return String(actualValue).toLowerCase().startsWith(String(expectedValue).toLowerCase());
    
    case 'ends_with':
      return String(actualValue).toLowerCase().endsWith(String(expectedValue).toLowerCase());
    
    case 'greater_than':
      return parseFloat(actualValue) > parseFloat(expectedValue);
    
    case 'less_than':
      return parseFloat(actualValue) < parseFloat(expectedValue);
    
    case 'greater_equal':
      return parseFloat(actualValue) >= parseFloat(expectedValue);
    
    case 'less_equal':
      return parseFloat(actualValue) <= parseFloat(expectedValue);
    
    case 'in':
      return Array.isArray(expectedValue) && expectedValue.includes(actualValue);
    
    case 'not_in':
      return Array.isArray(expectedValue) && !expectedValue.includes(actualValue);
    
    default:
      return false;
  }
}

/**
 * Apply operator to array value
 */
function applyArrayOperator(actualArray, operator, expectedValue) {
  if (!Array.isArray(actualArray)) return false;
  
  switch (operator) {
    case 'contains':
      return actualArray.includes(expectedValue);
    
    case 'contains_any_of':
      return Array.isArray(expectedValue) && 
             expectedValue.some(val => actualArray.includes(val));
    
    case 'contains_all_of':
      return Array.isArray(expectedValue) && 
             expectedValue.every(val => actualArray.includes(val));
    
    case 'not_contains':
      return !actualArray.includes(expectedValue);
    
    case 'not_contains_any_of':
      return Array.isArray(expectedValue) && 
             !expectedValue.some(val => actualArray.includes(val));
    
    case 'equals':
      // Compare arrays (order-independent)
      const sortedActual = [...actualArray].sort();
      const sortedExpected = Array.isArray(expectedValue) 
        ? [...expectedValue].sort() 
        : [expectedValue].sort();
      return JSON.stringify(sortedActual) === JSON.stringify(sortedExpected);
    
    default:
      return false;
  }
}

Filter Functions by Field Type ​

javascript
/**
 * Filter by product field (vendor, tags, status, etc.)
 */
function filterProductField(product, field, operator, value, exclude = false) {
  let productValue = product[field];
  
  // Handle array fields (tags)
  if (Array.isArray(productValue)) {
    const matches = applyArrayOperator(productValue, operator, value);
    return exclude ? !matches : matches;
  }
  
  // Handle string/number/boolean fields
  const matches = applyOperator(productValue, operator, value);
  return exclude ? !matches : matches;
}

/**
 * Filter by product metafield (any type)
 */
function filterProductMetafield(product, namespace, key, operator, value, exclude = false) {
  const metafield = product.metafields?.find(
    mf => mf.namespace === namespace && mf.key === key
  );
  
  if (!metafield) return exclude; // If metafield doesn't exist, exclude if exclude=true
  
  const parsedValue = parseMetafieldValue(metafield);
  const type = metafield.type;
  
  // Handle array types
  if (type.startsWith('list.')) {
    const matches = applyArrayOperator(parsedValue, operator, value);
    return exclude ? !matches : matches;
  }
  
  // Handle JSON object types (money, dimension, etc.)
  if (type === 'money' && parsedValue?.amount) {
    // For money: compare amount field
    const matches = applyOperator(parseFloat(parsedValue.amount), operator, value);
    return exclude ? !matches : matches;
  }
  
  if (type === 'dimension' && parsedValue?.value) {
    // For dimension: compare value field
    const matches = applyOperator(parseFloat(parsedValue.value), operator, value);
    return exclude ? !matches : matches;
  }
  
  if (type === 'weight' && parsedValue?.value) {
    // For weight: compare value field
    const matches = applyOperator(parseFloat(parsedValue.value), operator, value);
    return exclude ? !matches : matches;
  }
  
  if (type === 'volume' && parsedValue?.value) {
    // For volume: compare value field
    const matches = applyOperator(parseFloat(parsedValue.value), operator, value);
    return exclude ? !matches : matches;
  }
  
  if (type === 'rating' && parsedValue?.value) {
    // For rating: compare value field
    const matches = applyOperator(parseFloat(parsedValue.value), operator, value);
    return exclude ? !matches : matches;
  }
  
  // Handle number types
  if (type === 'number_integer' || type === 'number_decimal') {
    const matches = applyOperator(parsedValue, operator, value);
    return exclude ? !matches : matches;
  }
  
  // Handle boolean
  if (type === 'boolean') {
    const matches = parsedValue === value;
    return exclude ? !matches : matches;
  }
  
  // Handle string types (text, date, URL, reference, etc.)
  // For dates, you might want to parse and compare properly
  if (type === 'date' || type === 'date_time') {
    // Date comparison
    const actualDate = new Date(parsedValue);
    const expectedDate = new Date(value);
    let matches = false;
    
    switch (operator) {
      case 'equals':
        matches = actualDate.getTime() === expectedDate.getTime();
        break;
      case 'after':
        matches = actualDate > expectedDate;
        break;
      case 'before':
        matches = actualDate < expectedDate;
        break;
      case 'on_or_after':
        matches = actualDate >= expectedDate;
        break;
      case 'on_or_before':
        matches = actualDate <= expectedDate;
        break;
      default:
        matches = applyOperator(parsedValue, operator, value);
    }
    
    return exclude ? !matches : matches;
  }
  
  // Default: string comparison
  const matches = applyOperator(parsedValue, operator, value);
  return exclude ? !matches : matches;
}

/**
 * Filter by variant metafield
 */
function filterVariantMetafield(product, namespace, key, operator, value, exclude = false) {
  if (!product.variants) return exclude;
  
  const hasMatchingVariant = product.variants.some(variant => {
    const metafield = variant.metafields?.find(
      mf => mf.namespace === namespace && mf.key === key
    );
    if (!metafield) return false;
    
    const parsedValue = parseMetafieldValue(metafield);
    const type = metafield.type;
    
    // Handle array types
    if (type.startsWith('list.')) {
      return applyArrayOperator(parsedValue, operator, value);
    }
    
    // Handle JSON object types (same logic as product metafields)
    if (type === 'money' && parsedValue?.amount) {
      return applyOperator(parseFloat(parsedValue.amount), operator, value);
    }
    
    // Handle other types (same as product metafields)
    if (type === 'number_integer' || type === 'number_decimal') {
      return applyOperator(parsedValue, operator, value);
    }
    
    if (type === 'boolean') {
      return parsedValue === value;
    }
    
    // Default: string comparison
    return applyOperator(parsedValue, operator, value);
  });
  
  return exclude ? !hasMatchingVariant : hasMatchingVariant;
}

/**
 * Filter by variant field
 */
function filterVariantField(product, field, operator, value, exclude = false) {
  if (!product.variants) return exclude;
  
  const hasMatchingVariant = product.variants.some(variant => {
    let variantValue = variant[field];
    
    // Handle price (stored as string)
    if (field === 'price') {
      variantValue = parseFloat(variantValue);
    }
    
    return applyOperator(variantValue, operator, value);
  });
  
  return exclude ? !hasMatchingVariant : hasMatchingVariant;
}

Unified Filter Function ​

javascript
/**
 * Apply a single filter to a product
 * @param {Object} product - Product with metafields and variants
 * @param {Object} filterConfig - Filter configuration
 * @param {string} filterConfig.field - Field path (e.g., 'vendor', 'custom.flavors', 'variants.price', 'variants.custom.size')
 * @param {string} filterConfig.operator - Operator ('equals', 'contains', 'greater_than', etc.)
 * @param {any} filterConfig.value - Value to compare against
 * @param {boolean} filterConfig.exclude - If true, exclude products that match (default: false)
 * @returns {boolean} True if product passes filter
 */
function applyFilter(product, filterConfig) {
  const { field, operator, value, exclude = false } = filterConfig;
  
  // Parse field path
  const parts = field.split('.');
  
  // Product field (vendor, tags, status, productType, title)
  const productFields = ['vendor', 'tags', 'status', 'productType', 'title', 'handle'];
  if (productFields.includes(parts[0])) {
    return filterProductField(product, parts[0], operator, value, exclude);
  }
  
  // Variant field (variants.price, variants.sku, variants.availableForSale)
  if (parts[0] === 'variants' && parts.length === 2) {
    const variantField = parts[1];
    return filterVariantField(product, variantField, operator, value, exclude);
  }
  
  // Variant metafield (variants.custom.size, variants.custom.flavors)
  if (parts[0] === 'variants' && parts.length === 3) {
    const namespace = parts[1];
    const key = parts[2];
    return filterVariantMetafield(product, namespace, key, operator, value, exclude);
  }
  
  // Product metafield (custom.flavors, nutrition_panel.calories)
  if (parts.length === 2) {
    const namespace = parts[0];
    const key = parts[1];
    return filterProductMetafield(product, namespace, key, operator, value, exclude);
  }
  
  // Default: include if filter doesn't match
  return true;
}

Complete Usage Example ​

javascript
// 1. Fetch all products with metafields and variants
const allProducts = await prisma.product.findMany({
  include: {
    metafields: true,
    variants: {
      include: {
        metafields: true
      }
    }
  }
});

// 2. Filter configuration from quiz responses
const filters = [
  // Product fields
  { field: 'vendor', operator: 'in', value: ['BrandA', 'BrandB', 'BrandC'], exclude: false },
  { field: 'vendor', operator: 'in', value: ['BrandX', 'BrandY'], exclude: true },
  { field: 'status', operator: 'equals', value: 'active', exclude: false },
  { field: 'tags', operator: 'contains_any_of', value: ['organic', 'gluten-free'], exclude: false },
  
  // Product metafields (array types)
  { field: 'custom.dietary_preferences', operator: 'contains_any_of', value: ['vegan'], exclude: false },
  { field: 'custom.flavors', operator: 'contains_any_of', value: ['citrus', 'lemon'], exclude: true },
  
  // Product metafields (single string)
  { field: 'custom.product_type', operator: 'equals', value: 'supplement', exclude: false },
  
  // Product metafields (money type)
  { field: 'custom.price', operator: 'greater_than', value: 20, exclude: false },
  
  // Product metafields (date type)
  { field: 'custom.release_date', operator: 'after', value: '2024-01-01', exclude: false },
  
  // Variant fields
  { field: 'variants.availableForSale', operator: 'equals', value: true, exclude: false },
  { field: 'variants.price', operator: 'greater_than', value: 20, exclude: false },
  
  // Variant metafields
  { field: 'variants.custom.size', operator: 'equals', value: 'large', exclude: false },
  { field: 'variants.custom.flavors', operator: 'contains_any_of', value: ['lemon'], exclude: true },
];

// 3. Apply all filters
const filteredProducts = allProducts.filter(product => {
  return filters.every(filter => applyFilter(product, filter));
});

// 4. Result: ~70 products ready for AI processing
console.log(`Filtered from ${allProducts.length} to ${filteredProducts.length} products`);

Real-World Example ​

Quiz Scenario:

  • ✅ Must be vegan
  • ✅ Must be from BrandA, BrandB, or BrandC
  • ❌ Must NOT have citrus or lemon flavors
  • ❌ Must NOT be from BrandX or BrandY
  • ✅ Must have price > $20
  • ✅ Must have at least one variant available for sale
javascript
const filters = [
  { field: 'custom.dietary_preferences', operator: 'contains_any_of', value: ['vegan'], exclude: false },
  { field: 'vendor', operator: 'in', value: ['BrandA', 'BrandB', 'BrandC'], exclude: false },
  { field: 'custom.flavors', operator: 'contains_any_of', value: ['citrus', 'lemon'], exclude: true },
  { field: 'vendor', operator: 'in', value: ['BrandX', 'BrandY'], exclude: true },
  { field: 'custom.price', operator: 'greater_than', value: 20, exclude: false },
  { field: 'variants.availableForSale', operator: 'equals', value: true, exclude: false },
];

const filteredProducts = allProducts.filter(product => 
  filters.every(filter => applyFilter(product, filter))
);

Supported Operators ​

String Operators ​

  • equals - Exact match
  • not_equals - Not equal
  • contains - Contains substring (case-insensitive)
  • starts_with - Starts with string
  • ends_with - Ends with string
  • in - Value is in array
  • not_in - Value is not in array

Number Operators ​

  • equals - Equal to
  • not_equals - Not equal to
  • greater_than - Greater than
  • less_than - Less than
  • greater_equal - Greater than or equal
  • less_equal - Less than or equal

Array Operators ​

  • contains - Array contains value
  • contains_any_of - Array contains any of the values
  • contains_all_of - Array contains all of the values
  • not_contains - Array does not contain value
  • not_contains_any_of - Array does not contain any of the values
  • equals - Arrays are equal (order-independent)

Date Operators ​

  • equals - Same date
  • after - Date is after
  • before - Date is before
  • on_or_after - Date is on or after
  • on_or_before - Date is on or before

Handling All Metafield Types ​

Array Types (list.*) ​

All list types are stored as JSON strings and parsed to arrays:

  • list.single_line_text_field → Array of strings
  • list.number_integer → Array of numbers (stored as strings, parsed)
  • list.date → Array of date strings
  • list.boolean → Array of booleans (stored as strings, parsed)
  • list.product_reference → Array of GID strings
  • list.link → Array of JSON objects
  • list.rating → Array of JSON objects

JSON Object Types ​

All JSON object types are stored as JSON strings and parsed:

  • money → {amount: "5.99", currency_code: "CAD"}
  • dimension → {value: 25.0, unit: "cm"}
  • weight → {value: 2.5, unit: "kg"}
  • volume → {value: 20.0, unit: "ml"}
  • rating → {value: "3.5", scale_min: "1.0", scale_max: "5.0"}
  • link → {text: "Learn more", url: "https://shopify.com"}
  • rich_text_field → Complex JSON structure
  • json → Any JSON-serializable value

Primitive Types ​

  • number_integer, number_decimal → Parsed to number
  • boolean → Parsed to boolean
  • All others → Kept as string (dates, URLs, text, references, etc.)

Performance Considerations ​

For ~300 products:

  • Fetching all products with metafields: ~100-200ms
  • Runtime filtering: <10ms
  • Total: Fast enough for real-time filtering

If scaling to thousands:

  • Consider adding database-level pre-filtering for common fields (vendor, status)
  • Cache frequently used filter results
  • Consider pagination or lazy loading

Summary ​

This approach provides:

  • ✅ Simple schema - Single value column
  • ✅ Handles all types - All 48+ Shopify metafield types
  • ✅ Flexible filtering - Any combination of include/exclude
  • ✅ Type-aware - Parses values based on type field
  • ✅ Works for quiz filtering - Perfect for progressive filtering (300 → 70 products)

Field Reference Tables ​

Product Fields (from Prisma schema) ​

FieldTypeJavaScript ShapeExample
idStringproduct.id"gid://shopify/Product/123" (Shopify GID)
titleStringproduct.title"T-Shirt"
productTypeStringproduct.productType"Clothing"
tagsString[]product.tags["organic", "gluten-free"]
handleStringproduct.handle"t-shirt"
descriptionString?product.description"A comfortable t-shirt"
statusStringproduct.status"active"
vendorStringproduct.vendor"Nike"
totalInventoryIntproduct.totalInventory100
variantsCountIntproduct.variantsCount3
hasOnlyDefaultVariantBooleanproduct.hasOnlyDefaultVariantfalse
trackInventoryBooleanproduct.trackInventorytrue
created_atDateTimeproduct.created_atDate
updated_atDateTimeproduct.updated_atDate

Variant Fields (from Prisma schema) ​

FieldTypeJavaScript ShapeExample
idStringvariant.id"gid://shopify/ProductVariant/456" (Shopify GID)
titleStringvariant.title"Large / Blue"
priceStringvariant.price"29.99" (parse to number)
compareAtPriceString?variant.compareAtPrice"39.99"
skuString?variant.sku"TSH-001-L-BLUE"
inventoryQuantityInt?variant.inventoryQuantity50
availableForSaleBooleanvariant.availableForSaletrue
created_atDateTimevariant.created_atDate
updated_atDateTimevariant.updated_atDate

Metafield Types (from Shopify Docs) ​

Reference: Shopify Metafield Types

TypeStored AsJavaScript ParseExample Value
boolean"true" or "false"value === "true"true
color"#fff123"value (string)"#fff123"
date"2022-02-02"value (string)"2022-02-02"
date_time"2024-01-01T12:30:00"value (string)"2024-01-01T12:30:00"
dimensionJSON stringJSON.parse(value){value: 25.0, unit: "cm"}
id"1234"value (string)"1234"
jsonJSON stringJSON.parse(value){ingredient: "flour", amount: 0.3}
linkJSON stringJSON.parse(value){text: "Learn more", url: "https://shopify.com"}
moneyJSON stringJSON.parse(value){amount: "5.99", currency_code: "CAD"}
multi_line_text_field"text\nmore text"value (string)"Ingredients\nFlour\nWater"
number_decimal"10.4"parseFloat(value)10.4
number_integer"10"parseFloat(value)10
ratingJSON stringJSON.parse(value){value: "3.5", scale_min: "1.0", scale_max: "5.0"}
rich_text_fieldJSON stringJSON.parse(value)Complex JSON structure
single_line_text_field"text"value (string)"T-Shirt"
url"https://shopify.com"value (string)"https://shopify.com"
phone_number"+1234567890"value (string)"+1234567890"
email"user@example.com"value (string)"user@example.com"
Reference types (all)GID stringvalue (string)"gid://shopify/Product/123"
list.single_line_text_fieldJSON stringJSON.parse(value)["item1", "item2"]
list.number_integerJSON stringJSON.parse(value)["10", "20", "30"]
list.number_decimalJSON stringJSON.parse(value)["10.4", "20.5"]
list.dateJSON stringJSON.parse(value)["2022-02-02", "2023-03-03"]
list.booleanJSON stringJSON.parse(value)["true", "false"]
list.colorJSON stringJSON.parse(value)["#fff123", "#000456"]
list.urlJSON stringJSON.parse(value)["https://shopify.com", "https://shopify.dev"]
list.linkJSON stringJSON.parse(value)[{text: "Link 1", url: "https://example.com"}]
list.ratingJSON stringJSON.parse(value)[{value: "3.5", scale_min: "1.0", scale_max: "5.0"}]
list.dimensionJSON stringJSON.parse(value)[{value: 20.0, unit: "cm"}]
list.weightJSON stringJSON.parse(value)[{value: 2.5, unit: "kg"}]
list.volumeJSON stringJSON.parse(value)[{value: 20.0, unit: "ml"}]
List reference types (all)JSON stringJSON.parse(value)["gid://shopify/Product/1", "gid://shopify/Product/2"]