在JavaScript中从SQL查询字符串构建嵌套JSON对象

需求说明 #

需要把给定的SQL查询字符串转换成angular2-query-builder所需的嵌套JSON结构，用于Angular UI的查询条件渲染。

输入SQL字符串 #

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 )  )

期望输出JSON #

const 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'
                        }
                    ]
                }
            ]
        }
    ]
};

实现方案 #

通过递归解析SQL中的逻辑运算符、括号分组和条件语句，可直接转换为目标JSON结构。以下是TypeScript/JavaScript实现：

interface Rule {
  field: string;
  operator: string;
  value: string;
}

interface Group {
  condition: 'and' | 'or';
  rules: (Rule | Group)[];
}

function parseSQLToQueryBuilder(sql: string): Group {
  // 预处理：移除多余空格、统一大小写
  let processedSql = sql.trim().replace(/\s+/g, ' ').toUpperCase();
  
  // SQL运算符与query-builder运算符映射
  const operatorMap: Record<string, string> = {
    '=': 'Equals',
    'LIKE': 'Contains'
  };

  // 递归解析分组或条件
  function parseGroup(sqlSegment: string): Group {
    // 处理外层括号包裹的分组
    if (sqlSegment.startsWith('(') && sqlSegment.endsWith(')')) {
      return parseGroup(sqlSegment.slice(1, -1));
    }

    // 拆分当前层级的逻辑条件（仅处理最外层AND/OR）
    const { condition, segments } = splitTopLevelOperators(sqlSegment);

    const rules: (Rule | Group)[] = segments.map(segment => {
      // 嵌套分组递归处理
      if (segment.includes('(') || segment.includes(')')) {
        return parseGroup(segment);
      }

      // 解析单个条件语句
      let [field, op, value] = segment.split(/\s+/);
      
      // 处理LIKE值的通配符和引号
      if (op === 'LIKE') {
        value = value.replace(/^'%|%'$/g, '');
      } else {
        // 移除字符串值的引号
        value = value.replace(/^'|'$/g, '');
      }

      // 还原字段名的首字母大写格式
      return {
        field: field.charAt(0) + field.slice(1).toLowerCase(),
        operator: operatorMap[op],
        value
      };
    });

    return { condition, rules };
  }

  // 辅助函数：拆分最外层的AND/OR运算符
  function splitTopLevelOperators(sql: string): { condition: 'and' | 'or', segments: string[] } {
    let depth = 0;
    let segments: string[] = [];
    let current = '';
    let condition: 'and' | 'or' = 'and';

    for (let i = 0; i < sql.length; i++) {
      const char = sql[i];
      if (char === '(') depth++;
      if (char === ')') depth--;

      // 仅在最外层时判断AND/OR
      if (depth === 0) {
        if (sql.slice(i, i+3) === 'AND') {
          condition = 'and';
          segments.push(current.trim());
          current = '';
          i += 2; // 跳过AND剩余字符
          continue;
        } else if (sql.slice(i, i+2) === 'OR') {
          condition = 'or';
          segments.push(current.trim());
          current = '';
          i += 1; // 跳过OR剩余字符
          continue;
        }
      }

      current += char;
    }

    segments.push(current.trim());
    return { condition, segments };
  }

  return parseGroup(processedSql);
}

// 使用示例
const inputSQL = "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 )  )";
const result = parseSQLToQueryBuilder(inputSQL);
console.log(JSON.stringify(result, null, 2));

代码说明 #

1. 预处理：统一SQL格式，避免空格、大小写差异影响解析。
2. 递归解析：通过递归处理括号嵌套的分组，生成对应的Group结构。
3. 运算符映射：将SQL的=、LIKE转换为组件支持的Equals、Contains。
4. 值处理：移除字符串引号和LIKE语句中的%通配符，匹配组件的参数格式。
5. 层级拆分：通过括号深度判断，仅拆分最外层逻辑运算符，保证嵌套结构的正确性。

内容的提问来源于stack exchange，提问作者Shashikumar N