> ## Documentation Index
> Fetch the complete documentation index at: https://doc.lucidworks.com/llms.txt
> Use this file to discover all available pages before exploring further.

# SQL-Based Experiment Metric Jobs

export const schema = {
  "type": "object",
  "title": "SQL-Based Experiment Metric",
  "description": "This job is created by an experiment in order to calculate an objective",
  "required": ["id", "type"],
  "properties": {
    "id": {
      "type": "string",
      "title": "Spark Job ID",
      "description": "The ID for this Spark job. Used in the API to reference this job. Allowed characters: a-z, A-Z, dash (-) and underscore (_)",
      "maxLength": 128,
      "pattern": "^[A-Za-z0-9_\\-]+$"
    },
    "experimentId": {
      "type": "string",
      "title": "Experiment ID",
      "hints": ["readonly"]
    },
    "metricName": {
      "type": "string",
      "title": "Objective name",
      "hints": ["readonly"]
    },
    "notes": {
      "type": "string",
      "title": "Notes",
      "description": "A short description about this job.",
      "hints": ["lengthy"]
    },
    "sql": {
      "type": "string",
      "title": "SQL",
      "hints": ["readonly"]
    },
    "experiment": {
      "type": "object",
      "title": "Experiment",
      "required": ["id", "baseSignalsCollection", "metrics"],
      "properties": {
        "id": {
          "type": "string",
          "title": "ID",
          "maxLength": 128,
          "pattern": "^[A-Za-z0-9_\\-]+$"
        },
        "description": {
          "type": "string",
          "title": "Description"
        },
        "uniqueIdParameter": {
          "type": "string",
          "title": "Unique ID Parameter",
          "description": "The name of the request parameter containing the user ID",
          "default": "userId",
          "format": "uri"
        },
        "baseSignalsCollection": {
          "type": "string",
          "title": "Base Collection for Signals",
          "description": "Signals resulting from requests that flow through this experiment will go into the signal collection associated with this collection",
          "minLength": 1,
          "pattern": "^[A-Za-z0-9_\\-]+$"
        },
        "variants": {
          "type": "array",
          "title": "Variants",
          "description": "Specify what varies in this variant, and optionally change the traffic weight",
          "items": {
            "type": "object",
            "properties": {
              "id": {
                "type": "string",
                "title": "Variant id",
                "hints": ["hidden"],
                "maxLength": 128,
                "pattern": "^[A-Za-z0-9_\\-]+$"
              },
              "name": {
                "type": "string",
                "title": "Name"
              },
              "queryPipeline": {
                "type": "string",
                "title": "Query Pipeline"
              },
              "params": {
                "type": "array",
                "title": "Query Params",
                "description": "URL parameters to add to queries using this variant",
                "items": {
                  "type": "object",
                  "required": ["key", "value"],
                  "properties": {
                    "key": {
                      "type": "string",
                      "title": "Parameter Name"
                    },
                    "value": {
                      "type": "string",
                      "title": "Parameter Value"
                    },
                    "policy": {
                      "type": "string",
                      "title": "Update Policy",
                      "enum": ["replace", "append", "remove", "default"],
                      "default": "append"
                    }
                  }
                }
              },
              "collection": {
                "type": "string",
                "title": "Collection"
              },
              "weight": {
                "type": "number",
                "title": "Weight",
                "description": "Proportion of traffic to send to this variant. Higher values mean proportionally more traffic will be routed to this variant",
                "default": 1,
                "minimum": 0.01,
                "exclusiveMinimum": false
              }
            }
          }
        },
        "metrics": {
          "type": "array",
          "title": "Objectives",
          "description": "Metrics that will be used to evaluate the variants",
          "minItems": 1,
          "items": {
            "type": "object",
            "required": ["name"],
            "properties": {
              "name": {
                "type": "string",
                "title": "Name"
              },
              "description": {
                "type": "string",
                "title": "Description"
              },
              "primary": {
                "type": "boolean",
                "title": "Primary",
                "description": "Whether this metric is the primary metric used for evaluating the variants (the 'OEC')."
              },
              "jobId": {
                "type": "string",
                "title": "Associated Spark Job ID",
                "hints": ["hidden"]
              },
              "binary": {
                "type": "boolean",
                "title": "Binary-valued metric",
                "description": "Whether this metric measures a Bernoulli trial (clicks, cart adds, etc) or a continuous-valued event.",
                "hints": ["hidden"]
              }
            }
          }
        },
        "enabled": {
          "type": "boolean",
          "title": "Enabled",
          "default": true,
          "hints": ["readonly"]
        },
        "startTimestamp": {
          "type": "string",
          "title": "Start Date",
          "description": "When the experiment last started",
          "hints": ["readonly"],
          "format": "date-time"
        },
        "runId": {
          "type": "string",
          "title": "Run Identifier",
          "hints": ["readonly", "hidden"]
        },
        "automaticallyAdjustTraffic": {
          "type": "boolean",
          "title": "Automatically Adjust Weights Between Variants",
          "default": false
        }
      },
      "hints": ["hidden"]
    },
    "type": {
      "type": "string",
      "title": "Spark Job Type",
      "enum": ["experiment_sql"],
      "default": "experiment_sql",
      "hints": ["readonly"]
    }
  },
  "additionalProperties": true,
  "category": "Other",
  "categoryPriority": 1,
  "unsafe": false
};

export const SchemaParamFields = ({schema}) => {
  const sanitize = str => {
    if (typeof str !== "string") return str;
    return str.replace(/^"(.*)"$/s, "$1").replace(/\\/g, "").replace(/"/g, "'");
  };
  const renderMd = str => {
    const s = sanitize(str);
    const text = (/[.!?]\)*$/).test(s) ? s : `${s}.`;
    return text.split(/(\*\*[^*]+\*\*|_[^_]+_|`[^`]+`)/g).map((part, i) => {
      if (part.startsWith("**")) return <strong key={i}>{part.slice(2, -2)}</strong>;
      if (part.startsWith("_")) return <em key={i}>{part.slice(1, -1)}</em>;
      if (part.startsWith("`")) return <code key={i}>{part.slice(1, -1)}</code>;
      return part;
    });
  };
  const {description, properties = {}, required: requiredProps = []} = schema;
  const visibleProps = useMemo(() => Object.entries(properties).filter(([, prop]) => !prop.hints?.includes("hidden")), [properties]);
  const renderProp = ([name, prop]) => {
    const isRequired = requiredProps.includes(name);
    const hasDefault = prop.default !== undefined;
    const rawDefault = prop.default;
    const hints = prop.hints || [];
    const isComplexDefault = hasDefault && (typeof rawDefault === "object" || typeof rawDefault === "string" && (rawDefault.length > 20 || rawDefault.includes('"')));
    const postBadges = [];
    if (prop.title) {
      postBadges.push(<><span className="text-stone-400 dark:text-stone-500">API property: </span>{name}</>);
    }
    const constraints = [];
    if (prop.minimum !== undefined && prop.maximum !== undefined) {
      constraints.push(`Range: ${prop.minimum} – ${prop.maximum}`);
    } else if (prop.minimum !== undefined) {
      constraints.push(`Min: ${prop.minimum}`);
    } else if (prop.maximum !== undefined) {
      constraints.push(`Max: ${prop.maximum}`);
    }
    if (prop.minLength !== undefined && prop.maxLength !== undefined) {
      constraints.push(`Length: ${prop.minLength} – ${prop.maxLength}`);
    } else if (prop.minLength !== undefined) {
      constraints.push(`Min length: ${prop.minLength}`);
    } else if (prop.maxLength !== undefined) {
      constraints.push(`Max length: ${prop.maxLength}`);
    }
    const fieldProps = {
      key: name,
      body: prop.title || name,
      type: prop.type,
      ...postBadges.length > 0 && ({
        post: postBadges
      }),
      ...isRequired && ({
        required: true
      }),
      ...!isComplexDefault && hasDefault ? {
        default: sanitize(String(rawDefault))
      } : {}
    };
    const isObject = prop.type === "object" && prop.properties;
    const isArrayOfObjects = prop.type === "array" && prop.items?.type === "object" && prop.items.properties;
    return <ParamField {...fieldProps}>
        {prop.description && <p>{renderMd(prop.description)}</p>}

        {prop.enum && <p>
            Allowed values: 
            {prop.enum.map((v, i) => <>{i > 0 && ", "}<code key={i}>{String(v)}</code></>)}
          </p>}

        {constraints.length > 0 && <p className="text-stone-500 dark:text-stone-400 text-sm">
            {constraints.join(" · ")}
          </p>}

        {isComplexDefault && <div className="flex">
            <p>
              <strong>Default:</strong>
            </p>
            <pre className="!my-0">
              <code>
                {JSON.stringify(rawDefault, null, 2)}
              </code>
            </pre>
          </div>}

        {isArrayOfObjects && <Expandable title="item properties">
            <SchemaParamFields schema={{
      properties: prop.items.properties,
      required: prop.items.required
    }} />
          </Expandable>}

        {isObject && <Expandable title="properties">
            <SchemaParamFields schema={{
      properties: prop.properties,
      required: prop.required
    }} />
          </Expandable>}
      </ParamField>;
  };
  return <div>
      {description && <p>{renderMd(description)}</p>}

      {visibleProps.map(renderProp)}
    </div>;
};

export const LwTemplate = ({title = "Key questions to get you started", icon = "sparkles", cta = "Powered by Agent Studio", linkHref = "https://lucidworks.com/demo/?utm_source=docs&utm_medium=referral&utm_campaign=docs_cta_ai"}) => {
  const [isLoaded, setIsLoaded] = useState(false);
  useEffect(() => {
    const timer = setTimeout(() => {
      setIsLoaded(true);
    }, 500);
    return () => clearTimeout(timer);
  }, []);
  return <div className="lw-template-container">
      <Card title={title} icon={icon}>
        {isLoaded && <span dangerouslySetInnerHTML={{
    __html: `<lw-template id="a029c1a9-28be-427e-b0e1-5d918920246a"></lw-template
            >`
  }} />}
        <Link href={linkHref} className="agent-studio-link text-left text-gray-600 gap-2 dark:text-gray-400 text-sm font-medium flex flex-row items-center hover:text-primary dark:hover:text-primary-light group-hover:text-primary group-hover:dark:text-primary-light">Powered by Lucidworks Agent Studio</Link>
      </Card>
    </div>;
};

[localhost link]: http://localhost:3000/docs/4/fusion-ai/reference/jobs/sql-based-experiment-metric

[mintlify link]: https://doc.lucidworks.com/docs/4/fusion-ai/reference/jobs/sql-based-experiment-metric

[old doc.lw link]: https://doc.lucidworks.com/fusion/5.9/571

This job is created by an experiment in order to calculate an objective.

<LwTemplate />

<Note>
  SQL-Based Experiment Metric job is deprecated as of Fusion AI 4.0.2.
</Note>

<SchemaParamFields schema={schema} />


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.