Package Exports

sql-where-parser

This package does not declare an exports field, so the exports above have been automatically detected and optimized by JSPM instead. If any package subpath is missing, it is recommended to post an issue to the original package (sql-where-parser) to support the "exports" field. If that is not possible, create a JSPM override to customize the exports field for this package.

Readme

SqlWhereParser

What is it?

SqlWhereParser parses the WHERE portion of an SQL-like string into an abstract syntax tree.

const sql = 'name = "Shaun Persad" AND age >= 27';
const parser = new SqlWhereParser();
const parsed = parser.parse(sql);
/**
 * The tree is object-based, where each key is the operator, and its value is an array of the operands.
 * The number of operands depends on if the operation is defined as unary, binary, or ternary in the config.
 */
equals(parsed, {
    'AND': [
        {
            '=': ['name', 'Shaun Persad']
        },
        {
           '>=': ['age', 27] 
        }
    ]
});

You can also evaluate the query in-line as the expressions are being built.

const sql = 'name = "Shaun Persad" AND age >= (20 + 7)';
const parser = new SqlWhereParser();
/**
 * This evaluator function will evaluate the "+" operator with its operands by adding its operands together.
 */
const parsed = parser.parse(sql, (operatorValue, operands) => {
    
    if (operatorValue === '+') {
        return operands[0] + operands[1];
    }
    return parser.defaultEvaluator(operatorValue, operands);
});

equals(parsed, {
    'AND': [
        {
            '=': ['name', 'Shaun Persad']
        },
        {
            '>=': ['age', 27]
        }
    ]
});

This evaluation can also be used to convert the AST into a specific tree, like a MongoDB query.

const sql = 'name = "Shaun Persad" AND age >= 27';
const parser = new SqlWhereParser();
/**
 * This will map each operand to its mongoDB equivalent.
 */
const parsed = parser.parse(sql, (operatorValue, operands) => {
    switch (operatorValue) {
        case '=':
            return {
                [operands[0]]: operands[1]
            };
        case 'AND':
            return {
                $and: operands
            };
        case '>=':
            return {
                [operands[0]]: {
                    $gte: operands[1]
                }
            };
    }
});

equals(parsed, {
    $and: [
        {
            name: 'Shaun Persad'
        },
        {
            age: {
                $gte: 27
            }
        }
    ]
});

SqlWhereParser can also parse into an array-like structure, where each sub-array is its own group of parentheses in the SQL.

const sql = '(name = "Shaun Persad") AND (age >= (20 + 7))';
const parser = new SqlWhereParser();
const sqlArray = parser.toArray(sql);
equals(sqlArray, [['name', '=', 'Shaun Persad'], 'AND', ['age', '>=', [20, '+', 7]]]);

This array structure is useful for displaying the query on the front-end, e.g. as HTML.

const sql = '(name = "Shaun Persad") AND age >= (20 + 7)';
const parser = new SqlWhereParser();
const sqlArray = parser.toArray(sql);
/**
 * This function will recursively map the elements of the array to HTML.
 */
const toHtml = (toConvert) => {
    if (toConvert && toConvert.constructor === SqlWhereParser.Operator) {
        return `<strong class="operator">${toConvert}</strong>`;
    }
    
    if (!toConvert || !(toConvert.constructor === Array)) {
        
        return `<span class="operand">${toConvert}</span>`;
    }
    const html = toConvert.map((toConvert) => {
        return toHtml(toConvert);
    });
    return `<div class="expression">${html.join('')}</div>`;
};

const html = toHtml(sqlArray);
equals(html,
    '<div class="expression">' +
        '<div class="expression">' +
            '<span class="operand">name</span>' +
            '<strong class="operator">=</strong>' +
            '<span class="operand">Shaun Persad</span>' +
        '</div>' +
        '<strong class="operator">AND</strong>' +
        '<span class="operand">age</span>' +
        '<strong class="operator">>=</strong>' +
        '<div class="expression">' +
            '<span class="operand">20</span>' +
            '<strong class="operator">+</strong>' +
            '<span class="operand">7</span>' +
        '</div>' +
    '</div>'
);

Installation

npm install sql-where-parser.

// or if in the browser: <script src="sql-where-parser/sql-where-parser.min.js"></script>

Usage

require it, and create a new instance.

//const SqlWhereParser = require('sql-where-parser');
const sql = 'name = "Shaun Persad" AND age >= 27';
const parser = new SqlWhereParser();

const parsed = parser.parse(sql); // Abstract syntax tree
const sqlArray = parser.toArray(sql); // Array

Advanced Usage

Supplying a config object

see here for all options

Modifying the config can be used to add new operators:

const config = SqlWhereParser.defaultConfig; // start off with the default config.
config.operators[5]['<=>'] = 2; // number of operands to expect for this operator.
config.operators[5]['<>'] = 2; // number of operands to expect for this operator.
config.tokenizer.shouldTokenize.push('<=>', '<>');
const sql = 'name <> "Shaun Persad" AND age <=> 27';
const parser = new SqlWhereParser(config); // use the new config
const parsed = parser.parse(sql);
equals(parsed, {
    'AND': [
        {
            '<>': ['name', 'Shaun Persad']
        },
        {
            '<=>': ['age', 27]
        }
    ]
});

API

For the full API documentation and more examples, see here.