Question 52830 in Javascript Question posted in
A very good W3school tutorial can be found here.

Constructing a nested JSON object from a sql query string in JavaScript

I’m trying to construct a nested JSON object from a query string which is returned from sql. The constructed nested JSON object will be be used by angular to render it in the UI using angular2-query-builder.

input_string = "TierLevel = ‘1’ AND CompanyType = ‘7’ AND CompanyService = ’23’ AND ( City LIKE(‘%york%’) OR City LIKE(‘%cal%’) ) AND ( StateProvince = 3171 OR StateProvince = 475 OR StateProvince = 2239 OR ( Country = 224 AND Country = 1 ) )";

output_JSON_Object = {
    condition: 'and',
    rules: [
        {
            field: 'TierLevel',
            operator: 'Equals',
            value: '1'
        },
        {
            field: 'CompanyType',
            operator: 'Equals',
            value: '7'
        },
        {
            field: 'CompanyService',
            operator: 'Equals',
            value: '23'
        },
        {
            condition: 'or',
            rules: [
                {
                    field: 'City',
                    operator: 'Contains',
                    value: 'york'
                },
                {
                    field: 'City',
                    operator: 'Contains',
                    value: 'cal'
                }
            ]
        },
        {
            condition: 'or',
            rules: [
                {
                    field: 'StateProvince',
                    operator: 'Equals',
                    value: '3171'
                },
                {
                    field: 'StateProvince',
                    operator: 'Equals',
                    value: '475'
                },
                {
                    field: 'StateProvince',
                    operator: 'Equals',
                    value: '2239'
                },
                {
                    condition: 'and',
                    rules: [
                        {
                            field: 'Country',
                            operator: 'Equals',
                            value: '224'
                        },
                        {
                            field: 'Country',
                            operator: 'Equals',
                            value: '1'
                        }
                    ]
                }
            ]
        }
    ]
}

Any help is appreciated. Thanks

Tags:

3

Answers


  1. 0

    You can try using antlr to break down your input sql string into tokens and later based on the broken tokens you can construct your required json.

    Login or Signup to reply.
  2. 0

    If your example contains all the possible variations in operators and literals, then you could use this parse function:

    function parseExpression(expr) {
    const tokens = expr.matchAll(/(Sw*)(?:s*(=|LIKE)(?s*('(?:[^']|'')*'|[-d.]+))?)?/g);
    
    function getRule() {
        const {value: [all, field, operator, literal]} = tokens.next();
        return all == "(" 
            ? getExpression()
            : {
                field,
                operator: operator == "=" ? "Equals"
                        : literal[1] != "%" ? "Ends With"
                        : literal.at(-2) != "%" ? "Begins With"
                        : "Contains",
                value: literal[0] != "'" ? +literal
                     : (operator == "LIKE" ? literal.replaceAll("%", "") : literal)
                       .slice(1, -1).replaceAll("''", "'")
            };
    }
    
    function getExpression() {
        const obj = { operator: null, rules: [getRule()] };
        for (let {done, value} = tokens.next(); !done && value[0] != ")"; {done, value} = tokens.next()) {
            obj.operator = value[0].toLowerCase();
            obj.rules.push(getRule());
        }
        return obj;
    }
    
    return getExpression();
}

// Example run
const input = "TierLevel = '1' AND CompanyType = '7' AND CompanyService = '23' AND ( City LIKE('york%') OR City LIKE('%cal') OR City LIKE('%any%') ) AND ( StateProvince = 3171 OR StateProvince = 475 OR StateProvince = 2239 OR ( Country = 224 AND Country = 1 ) )";

const output = parseExpression(input);
console.log(output);

    The parser has no error handling and assumes the input has the expected syntax. For instance:

    • The LIKE argument is expected to have at least one %, and only at the start and/or at the end of the string literal and no other wildcards, so that indeed it can be identified as a "Contains", "Begins With" or "Ends With" operation.
    • There is no support for <=, IN or other operators
    • The conditions are always in the form field operator literal, where the literal can have parentheses around it (as for LIKE)
    • Spacing around ( and ) is guaranteed when not used for LIKE.
    • The operators that occur in the same sequence are always the same (always AND or always OR), and parentheses are used to group subexpressions that use a different operator.
    • …etc

    Extend as needed to support other operators, syntax, and error handling.

    Login or Signup to reply.
  3. 0

    use AST with dfs can help you, i think.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search