Creating a hierarchical JSON structure using a database query in JavaScript

Question

Creating a hierarchical JSON structure using a database query in JavaScript

Currently, I am in the process of building a nested JSON object by parsing a query string obtained from an SQL database. This constructed JSON object will then be utilized by Angular to display data in the user interface using angular2-query-builder.

The input query string is as follows: 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'
                        }
                    ]
                }
            ]
        }
    ]
}

Your assistance on this matter would be greatly appreciated. Thank you!

javascript json nested javascript-objects query-string

Answer 1

Answer №1

If your example includes a wide range of operators and literals, you can utilize the following parsing function:

function parseExpression(expr) {
    const tokens = expr.matchAll(/(\S\w*)(?:\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();
}

// Sample execution
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 lacks error handling and expects the input to adhere to the specified syntax. For instance:

The LIKE operation should contain at least one %, positioned only at the beginning and/or end of the string literal without any other wildcards, enabling identification as a "Contains," "Begins With," or "Ends With" operation.
No support is provided for <=, IN, or other operators.
Conditions are consistently in the format of field operator literal, with optional parentheses around the literal (such as for LIKE).
Spaces surrounding ( and ) are guaranteed unless used for LIKE.
Sequentially occurring operators are always identical (either AND or OR), with parentheses utilized to group subexpressions utilizing a different operator.
...and so forth

Enhance the functionality to accommodate additional operators, syntax variations, and error management protocols as necessary.

Answer 2

If your example includes a wide range of operators and literals, you can utilize the following parsing function:

function parseExpression(expr) {
    const tokens = expr.matchAll(/(\S\w*)(?:\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();
}

// Sample execution
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 lacks error handling and expects the input to adhere to the specified syntax. For instance:

The LIKE operation should contain at least one %, positioned only at the beginning and/or end of the string literal without any other wildcards, enabling identification as a "Contains," "Begins With," or "Ends With" operation.
No support is provided for <=, IN, or other operators.
Conditions are consistently in the format of field operator literal, with optional parentheses around the literal (such as for LIKE).
Spaces surrounding ( and ) are guaranteed unless used for LIKE.
Sequentially occurring operators are always identical (either AND or OR), with parentheses utilized to group subexpressions utilizing a different operator.
...and so forth

Enhance the functionality to accommodate additional operators, syntax variations, and error management protocols as necessary.

Answer 3

Answer №2

One approach is to utilize antlr for parsing your input SQL string into tokens. From there, you can analyze the broken down tokens to generate the necessary JSON output.

Answer 4

One approach is to utilize antlr for parsing your input SQL string into tokens. From there, you can analyze the broken down tokens to generate the necessary JSON output.

Answer 5

Answer №3

Implementing the Abstract Syntax Tree (AST) along with depth-first search (dfs) could provide you with valuable assistance, in my opinion.

Answer 6

Implementing the Abstract Syntax Tree (AST) along with depth-first search (dfs) could provide you with valuable assistance, in my opinion.

Creating a hierarchical JSON structure using a database query in JavaScript

Answer №1

Answer №2

Answer №3

Similar questions

Using Javascript to modify file permissions in Google Drive

Tips for moving an input bar aside to make room for another

What are the steps for integrating mongoDB with an angular2 application?

Guidelines for accessing the POST request entity with Slim framework

What are the implications of AngularJS's filter on performance?

Tips for updating the filename in a file input using AngularJS

Is it better to use an array of dictionaries or a dictionary with arrays?

What is the best method for extracting a specific value from this JSON data?

Discovering the key to selecting a row by double-clicking in Vuetify's v-data-table

Discover an Easy Way to Scroll to the Bottom of Modal Content with Bootstrap 5 on Your Razor Page

Using $.ajax in Zend to add an additional field to a Json object

Is it possible for me to create a hyperlink that directs to a javascript function?

When clicking on HTML input fields, they do not receive focus

How can you verify user identity in Firebase when making a call to a cloud function on a

Converting group by values into JSON in Django is a common task that can be achieved by

Obtaining the login status of users on my website and displaying the number of users along with their names from the database using PHP

Freemarker substitute & and &ampersand;

"Exploring the world of interactive external links within a Facebook canvas app

Detecting incorrect serialized data entries based on data types

Error: The javascript function is unable to execute because undefined is not recognized as a function