Version: v6

Export

Refer to the TypeScript reference page for information about the types and interfaces referenced below.

Use the formatQuery function to export queries in various formats. The function signature is:

function formatQuery(
  query: RuleGroupTypeAny,
  options?: ExportFormat | FormatQueryOptions
): string | ParameterizedSQL | ParameterizedNamedSQL | RQBJsonLogic;

formatQuery converts a given query into one of the following formats:

JSON (with or without ids)
SQL WHERE clause
Parameterized SQL (with anonymous or named parameters)
MongoDB
Common Expression Language (CEL)
Spring Expression Language (SpEL)
JsonLogic

For the next few sections, assume the query variable has been defined as:

const query: RuleGroupType = {
  id: 'root',
  combinator: 'and',
  not: false,
  rules: [
    {
      id: 'rule1',
      field: 'firstName',
      operator: '=',
      value: 'Steve',
    },
    {
      id: 'rule2',
      field: 'lastName',
      operator: '=',
      value: 'Vai',
    },
  ],
};

tip

TL;DR: For best results, use the default combinators and operators or map your custom combinators/operators to the defaults with transformQuery.

While formatQuery technically accepts query objects of type RuleGroupTypeAny (i.e. RuleGroupType or RuleGroupTypeIC), it is not guaranteed to process a query correctly unless the query also conforms to the type DefaultRuleGroupTypeAny (i.e. DefaultRuleGroupType or DefaultRuleGroupTypeIC).

In practice, this means that all combinator and operator properties in the query must match the name of an element in defaultCombinators or defaultOperators, respectively. If you implement custom combinator/operator names, you can use the transformQuery function to map your query properties to the defaults.

For example, assume your implementation replaces the default "between" operator ({ name: "between", label: "between" }) with { name: "b/w", label: "b/w" }. Any rules using this operator would have operator: "b/w" instead of operator: "between". So if a query looked like this...

{
  "combinator": "and",
  "rules": [
    {
      "field": "someNumber",
      "operator": "b/w",
      "value": "12,14"
    }
  ]
}

...you could run it through transformQuery with the operatorMap option:

const newQuery = transformQuery(query, { operatorMap: { 'b/w': 'between' } });
// {
//   "combinator": "and",
//   "rules": [
//     {
//       "field": "someNumber",
//       "operator": "between",
//       "value": "12,14"
//     }
//   ]
// }

The newQuery object would be ready for processing by formatQuery, including its special handling of the "between" operator.

Basic usage

JSON

To export the internal query representation like what react-querybuilder passes to the onQueryChange callback, formatted by JSON.stringify, simply pass the query to formatQuery:

formatQuery(query);
// or
formatQuery(query, 'json');

The output will be a multi-line string representation of the query using 2 spaces for indentation.

`{
  "id": "root",
  "combinator": "and",
  "not": false,
  "rules": [
    {
      "id": "rule1",
      "field": "firstName",
      "value": "Steve",
      "operator": "="
    },
    {
      "id": "rule2",
      "field": "lastName",
      "value": "Vai",
      "operator": "="
    }
  ]
}`;

JSON without identifiers

To export the internal query representation without formatting (single-line, no indentation) and without the id or path attributes on each object, use the "json_without_ids" format. This is useful if you need to serialize the query for storage.

formatQuery(query, 'json_without_ids');

Output:

`{"combinator":"and","not":false,"rules":[{"field":"firstName","value":"Steve","operator":"="},{"field":"lastName","value":"Vai","operator":"="}]}`;

SQL

To export a SQL WHERE clause, use the "sql" format. The output should be compatible with almost all major RDBMS engines, but may require configuration in some cases.

formatQuery(query, 'sql');

Output:

`(firstName = 'Steve' and lastName = 'Vai')`;

Parameterized SQL

To export a SQL WHERE clause with bind variables instead of inline values, use the "parameterized" format. The output is an object with sql and params attributes.

formatQuery(query, 'parameterized');

Output:

{
  "sql": "(firstName = ? and lastName = ?)",
  "params": ["Steve", "Vai"]
}

Named parameters

If anonymous parameters (aka bind variables) are not acceptable, formatQuery can name each parameter based on the field name when the "parameterized_named" format is used. The output object is similar to the "parameterized" format, but the params attribute is an object instead of an array.

formatQuery(query, 'parameterized_named');

Output:

{
  "sql": "(firstName = :firstName_1 and lastName = :lastName_1)",
  "params": {
    "firstName_1": "Steve",
    "lastName_1": "Vai"
  }
}

MongoDB

For MongoDB-compatible output, use the "mongodb" format.

formatQuery(query, 'mongodb');

Output:

`{"$and":[{"firstName":"Steve"},{"lastName":"Vai"}]}`;

info

The MongoDB export format does not support the inversion operator (setting not: true for a rule group), however rules can be created using the "!=" operator.

Common Expression Language

For Common Expression Language (CEL) output, use the "cel" format.

formatQuery(query, 'cel');

Output:

`firstName = "Steve" && lastName = "Vai"`;

Spring Expression Language

For Spring Expression Language (SpEL) output, use the "spel" format.

formatQuery(query, 'spel');

Output:

`firstName == 'Steve' and lastName == 'Vai'`;

JsonLogic

The "jsonlogic" format produces an object that can be processed by the JsonLogic apply function (see https://jsonlogic.com/).

formatQuery(query, 'jsonlogic');

Output:

{ "and": [{ "==": [{ "var": "firstName" }, "Steve"] }, { "==": [{ "var": "lastName" }, "Vai"] }] }

tip

Before using JsonLogic's apply() method to apply the result of formatQuery(query, 'jsonlogic'), register the additional operators startsWith and endsWith exported by react-querybuilder. These are not standard JsonLogic operations, but they correspond to the "beginsWith" and "endsWith" operators, respectively, from react-querybuilder.

The most future-proof way is to loop through the jsonLogicAdditionalOperators entries like below. This way if any more custom operators are added in the future, they will be automatically available.

import { add_operation, apply } from 'json-logic-js';
import { jsonLogicAdditionalOperators } from 'react-querybuilder';

for (const [op, func] of Object.entries(jsonLogicAdditionalOperators)) {
  add_operation(op, func);
}

apply({ startsWith: [{ var: 'firstName' }, 'Stev'] }, data);

Configuration

An object can be passed as the second argument instead of a string to have more fine-grained control over the output.

info

parseNumbers is the only configuration option that applies to the "json" and "json_without_ids" formats.

Parse numbers

Since HTML <input> controls store values as strings (even when type="number"), exporting a query to various formats may produce a string representation of a value when a true numeric value is required or more appropriate. Set the parseNumbers option to true and formatQuery will attempt to convert all values to numbers, falling back to the original value if parseFloat(value) returns NaN (not a number).

const query: RuleGroupType = {
  combinator: 'and',
  not: false,
  rules: [
    {
      field: 'digits',
      operator: '=',
      value: '20',
    },
    {
      field: 'age',
      operator: 'between',
      value: '26, 52',
    },
    {
      field: 'lastName',
      operator: '=',
      value: 'Vai',
    },
  ],
};

// Default configuration - all values are strings:
formatQuery(query, { format: 'sql' });
// Returns: "(digits = '20' and age between '26' and '52' and lastName = 'Vai')"

// `parseNumbers: true` - numeric strings converted to actual numbers:
formatQuery(query, { format: 'sql', parseNumbers: true });
// Returns: "(digits = 20 and age between 26 and 52 and lastName = 'Vai')"

info

To avoid information loss, this option is more strict about what qualifies as "numeric" than the standard parseFloat function. To oversimplify a bit, parseFloat works with any string that starts with a numeric sequence, ignoring the rest of the string beginning with the first non-numeric character. In contrast, when parseNumbers is true, formatQuery will only convert a value to a number if it appears to be numeric in its entirety (after trimming whitespace).

Each of the following expressions evaluates to true:

// Everything after the '3' is ignored by `parseFloat`
parseFloat('000123abcdef') === 123;

// `value` contains non-numeric characters, so remains as-is
formatQuery(
  { rules: [{ field: 'f', operator: '=', value: '000123abcdef' }] },
  { format: 'sql', parseNumbers: true }
) === "(f = '000123abcdef')";

// `value` is wholly numeric (after trimming whitespace) so it gets converted to a number
formatQuery(
  { rules: [{ field: 'f', operator: '=', value: '  000123  ' }] },
  { format: 'sql', parseNumbers: true }
) === '(f = 123)';

Rule processor

To customize the format of exported rules, use the ruleProcessor configuration option. Rules will only be passed to the provided processor function if they first pass validation. The function will be called like this:

ruleProcessor(rule, { parseNumbers, escapeQuotes });

The first argument is the RuleType object from the query. The second argument is a ValueProcessorOptions object. parseNumbers is copied directly from the formatQuery options (default false), and escapeQuotes is true or false when appropriate as determined by the internal formatQuery logic (generally speaking, quotes are escaped for string values and not escaped otherwise, as when values represent field names).

The default rule processors for each format are available as exports from react-querybuilder:

defaultRuleProcessorCEL
defaultRuleProcessorJsonLogic
defaultRuleProcessorMongoDB
defaultRuleProcessorSpEL
defaultRuleProcessorSQL

info

ruleProcessor does not apply to the "parameterized" or "parameterized_named" formats (yet), but valueProcessor is available.

You can use the appropriate default rule processor as a fallback so your custom rule processor doesn't have to cover all cases, as shown below.

const query: RuleGroupType = {
  combinator: 'and',
  not: false,
  rules: [
    {
      field: 'firstName',
      operator: 'has', // <-- non-standard operator
      value: 'S',
    },
    {
      field: 'lastName',
      operator: '=',
      value: 'Vai',
    },
  ],
};

const customRuleProcessor: RuleProcessor = (rule, options) => {
  // The "has" operator is not handled by the default processor
  if (rule.operator === 'has') {
    return { in: [rule.value, { var: rule.field }] };
  }

  // Defer to the default processor for all other operators
  return defaultRuleProcessorJsonLogic(rule, options);
};

formatQuery(query, { format: 'jsonlogic', ruleProcessor: customRuleProcessor });
// Returns:
// {
//   and: [
//     { in: ["S", { var: "firstName" }] },
//     { "==": [{ var: "lastName" }, "Vai"] }
//   ]
// }

This SQL example (using Oracle syntax) demonstrates the generation of a case-insensitive condition:

// `query` is the same as in the previous example

const customRuleProcessor: RuleProcessor = (rule, options) => {
  if (rule.operator === 'has') {
    return `UPPER(${rule.field}) LIKE UPPER('%${rule.value}%')`;
  }

  return defaultRuleProcessorSQL(rule, options);
};

formatQuery(query, { format: 'sql', ruleProcessor: customRuleProcessor });
// Returns: "(UPPER(firstName) LIKE UPPER('%S%') and lastName = 'Vai')"
//            ^------------custom--------------^ ^------default-----^

Value processor

valueProcessor accepts the same arguments as ruleProcessor, but only affects the "value" portion of the output (to the right of the operator) for the "sql" format. If both options are provided, ruleProcessor takes precedence.

tip

For all formats except "sql", valueProcessor is merely a synonym for ruleProcessor. We recommend using ruleProcessor unless you are exporting SQL and only need to customize the value.

// `query` is the same as in the previous example

const customValueProcessor: ValueProcessorByRule = (rule, options) => {
  if (rule.operator === 'has') {
    return `'%${rule.value}%'`;
  }

  return defaultValueProcessorByRule(rule, options);
};

formatQuery(query, { format: 'sql', valueProcessor: customValueProcessor });
// Returns: "(firstName like '%S%' and lastName = 'Vai')"
//    default-^------------^ ^---^-custom  ^----------^-default

Legacy `valueProcessor` behavior

caution

The legacy valueProcessor signature exists for backwards compatibility, but we recommend avoiding it. For one reason, the options are not passed in so it becomes more difficult to correctly call a default processor.

If the function assigned to valueProcessor accepts three or more arguments (not including those with default values), it will be called like this:

valueProcessor(field, operator, value, valueSource);

Notice that no options or additional properties of the rule are passed as arguments. Among other problems, this prevents formatQuery from setting the escapeQuotes option.

This legacy behavior is documented here for completeness but, as stated above, is not recommended.

const query: RuleGroupType = {
  combinator: 'and',
  not: false,
  rules: [
    {
      field: 'instrument',
      operator: 'in',
      value: ['Guitar', 'Vocals'],
    },
    {
      field: 'lastName',
      operator: '=',
      value: 'Vai',
    },
  ],
};

const customValueProcessor = (field, operator, value) => {
  if (operator === 'in') {
    // Assuming `value` is an array, such as from a multi-select
    return `(${value.map(v => `'${v.trim()}'`).join(',')})`;
  }

  return defaultValueProcessor(field, operator, value);
};

formatQuery(query, { format: 'sql', valueProcessor: customValueProcessor });
// Returns: "(instrument in ('Guitar','Vocals') and lastName = 'Vai')"

Versions of the default value processors using the newer fn(rule, options) signature as well as the legacy signature are available for all query language formats except "jsonlogic" (use ruleProcessor instead).

Current signature (recommended):
- defaultValueProcessorByRule (for all SQL-based formats)
- defaultValueProcessorCELByRule
- defaultValueProcessorMongoDBByRule
- defaultValueProcessorSpELByRule
Legacy signature:
- defaultValueProcessor (for all SQL-based formats)
- defaultMongoDBValueProcessor
- defaultCELValueProcessor
- defaultSpELValueProcessor

Quote field names

Some database engines wrap field names in backticks (`) or square brackets ([]). This can be configured with the quoteFieldNamesWith option which can be assigned a string or an array of two strings.

formatQuery(query, { format: 'sql', quoteFieldNamesWith: '`' });
// Returns: "(`firstName` = 'Steve' and `lastName` = 'Vai')"

formatQuery(query, { format: 'sql', quoteFieldNamesWith: ['[', ']'] });
// Returns: "([firstName] = 'Steve' and [lastName] = 'Vai')"

Parameter prefix

If the "parameterized_named" format is used, configure the parameter prefix used in the sql string with the paramPrefix option (should the default ":" be inappropriate).

const p = formatQuery(query, {
  format: 'parameterized_named',
  paramPrefix: '$',
});
// p.sql === "(firstName = $firstName_1 and lastName = $lastName_1)"
//                         ^                           ^

Fallback expression

The fallbackExpression is a string that will be part of the output when formatQuery can't quite figure out what to do for a particular rule or group. The intent is to maintain valid syntax while (hopefully) not detrimentally affecting the query criteria. If not provided, the default fallback expression for the given format will be used (see table below).

Format	Default `fallbackExpression`
"sql"	`'(1 = 1)'`
"parameterized"	`'(1 = 1)'`
"parameterized_named"	`'(1 = 1)'`
"mongodb"	`'{"$and":[{"$expr":true}]}'`
"cel"	`'1 == 1'`
"spel"	`'1 == 1'`
"jsonlogic"	`false`

Value sources

When the valueSource property for a rule is set to "field", formatQuery will place the bare, unquoted value (which should be a valid field name) in the result for the "sql", "parameterized", "parameterized_named", "mongodb", "cel", and "spel" formats. No parameters will be generated for such rules.

const pf = formatQuery(
  {
    combinator: 'and',
    rules: [
      { field: 'firstName', operator: '=', value: 'lastName', valueSource: 'field' },
      { field: 'firstName', operator: 'beginsWith', value: 'middleName', valueSource: 'field' },
    ],
  },
  'parameterized_named'
);

Output:

{
  "sql": "(firstName = lastName and firstName like middleName || '%')",
  "params": {}
}

Placeholder values

Any rule where the field or operator matches the placeholder value (default "~") will be excluded from the output for most export formats (see Automatic validation). To use a different string as the placeholder value, set the placeholderFieldName and/or placeholderOperatorName options. These correspond to the fields.placeholderName and operators.placeholderName properties on the main component's translations prop object.

Validation

The validation options (validator and fields – see Validation for more information) only affect the output when format is not "json" or "json_without_ids". If the validator function returns false, the fallbackExpression will be returned. Otherwise, groups and rules marked as invalid (either by the validation map produced by the validator function or the result of the field-based validator function) will be ignored.

Example:

const query: RuleGroupType = {
  id: 'root',
  rules: [
    {
      id: 'r1',
      field: 'firstName',
      value: '',
      operator: '=',
    },
    {
      id: 'r2',
      field: 'lastName',
      value: 'Vai',
      operator: '=',
    },
  ],
  combinator: 'and',
  not: false,
};

// Example 1
// Query is invalid based on the validator function
formatQuery(query, {
  format: 'sql',
  validator: () => false,
});
// Returns: "(1 = 1)" <-- see `fallbackExpression` option

// Example 2
// Rule "r1" is invalid based on the validation map
formatQuery(query, {
  format: 'sql',
  validator: () => ({ r1: false }),
});
// Returns: "(lastName = 'Vai')" <-- skipped `firstName` rule with `id === 'r1'`

// Example 3
// Rule "r1" is invalid based on the field validator for `firstName`
formatQuery(query, {
  format: 'sql',
  fields: [{ name: 'firstName', validator: () => false }],
});
// Returns: "(lastName = 'Vai')" <-- skipped `firstName` rule because field validator returned `false`

Automatic validation

To minimize the chance of invalid syntax, some basic validation will be performed by formatQuery for the "in", "notIn", "between", and "notBetween" operators for all formats except "json" and "json_without_ids", even if no validator function or field validators are specified.

Rules that specify an "in" or "notIn" operator will be deemed invalid if the rule's value is neither an array with at least one element (i.e. value.length > 0) nor a non-empty string.
Rules that specify a "between" or "notBetween" operator will be deemed invalid if the rule's value is neither an array with length of at least two (value.length >= 2) nor a string with at least one comma that isn't the first or last character (i.e. value.split(',').length >= 2, and neither element is an empty string).
Rules where either the field or operator match their respective placeholder will be deemed invalid (i.e. field === placeholderFieldName || operator === placeholderOperatorName).

Basic usage​

JSON​

JSON without identifiers​

SQL​

Parameterized SQL​

Named parameters​

MongoDB​

Common Expression Language​

Spring Expression Language​

JsonLogic​

Configuration​

Parse numbers​

Rule processor​

Value processor​

Legacy valueProcessor behavior​

Quote field names​

Parameter prefix​

Fallback expression​

Value sources​

Placeholder values​

Validation​

Automatic validation​

Basic usage

JSON

JSON without identifiers

SQL

Parameterized SQL

Named parameters

MongoDB

Common Expression Language

Spring Expression Language

JsonLogic

Configuration

Parse numbers

Rule processor

Value processor

Legacy `valueProcessor` behavior

Quote field names

Parameter prefix

Fallback expression

Value sources

Placeholder values

Validation

Automatic validation