Translate logic to SQL conditions

This pair of functions take a jsonLogic logic object and tries to convert it to SQL conditions. It is currently used in the plv8 extension of PostgreSQL and it has some extra operators and syntax for supporting things such as ltree and json(b).

Probably needs work and testing to be included in someone else's project, but maybe it will be helpful. To work outside of plv8, the various methods, elog, quote_ident and quote_literal would have to be replaced.

function parseJsonVar(path, sqlResultType) {
  plv8.elog(DEBUG2, `Parsing JSON var: path: ${path}, sqlResultType: ${sqlResultType}`);

  let jsonPathTokens = path.split('.').map(function (token, index) {
    return index === 0 ? plv8.quote_ident(token) : plv8.quote_literal(token);
  });

  let jsonPathString = jsonPathTokens[0];
  if (jsonPathTokens.length > 2) {
    jsonPathString = jsonPathTokens.slice(0, -1).join(' -> ');
  }

  if (jsonPathTokens.length > 1) {
    if (sqlResultType) {
      switch (sqlResultType.toLowerCase()) {
        case 'string':
        case 'text':
        case 'ltree':
        case 'lquery':
          jsonPathString += ' ->> ' + jsonPathTokens.slice(-1);
          break;
        default:
          jsonPathString += ' -> ' + jsonPathTokens.slice(-1);
      }
    } else {
      jsonPathString += ' -> ' + jsonPathTokens.slice(-1);
    }
  }

  return jsonPathString;
}

function parseFilter(_op, _data, _joiningClause, _minArrayElements, _maxArrayElements, _sqlResultType) {
  plv8.elog(DEBUG2, `Parsing filter: op: ${_op}, data: ${JSON.stringify(_data)}, sqlResultType: ${_sqlResultType}`);

  switch (_op) {
    case '<@':
      return parseFilter(null, _data, ' <@ ', 2, 2, _sqlResultType);
    case '@>':
      return parseFilter(null, _data, ' @> ', 2, 2, _sqlResultType);
    case '?|':
      if (Array.isArray(_data) && _data.length === 2 && Array.isArray(_data[1])) {
        let _objToSearch = parseFilter(null, _data[0], null, 0, 0, 'jsonb');
        let _strings = parseFilter("textarray", _data[1], null, 1, null, _sqlResultType);
        return `( ${_objToSearch} ?| ${_strings} ) `;
      } else {
        plv8.elog(ERROR, `Operator '${_op}' takes two arguments: [objectToSearch, [strings]].`);
        return null;
      }
    case '~':
      if (Array.isArray(_data) && _data.length === 2) {
        let _objToSearch = parseFilter(null, _data[0], null, 0, 0, 'ltree[]');
        let _strings = parseFilter(null, _data[1], null, 1, null, Array.isArray(_data[1]) ? 'lquery[]' : 'lquery');
        return `${_objToSearch} ~ ${_strings}`;
      } else {
        plv8.elog(ERROR, `Operator '${_op}' takes two arguments: [<ltree reference>, <lquery string>].`);
        return null;
      }
    case '=':
    case '==':
    case '===':
      return parseFilter(null, _data, ' = ', 2, 2, _sqlResultType);
    case '<>':
    case '!=':
    case '!==':
      return parseFilter(null, _data, ' != ', 2, 2, _sqlResultType);
    case 'in':
    case 'in_list':
      if (Array.isArray(_data) && _data.length === 2) {
        let _inList = parseFilter(null, _data[1], null, 1, null, _sqlResultType);
        let sqlResultType;
        switch (typeof _inList[0]) {
          case 'string':
            sqlResultType = 'text';
            break;
          case 'number':
            sqlResultType = 'number';
            break;
        }
        let _searchElement = parseFilter(null, _data[0], null, null, null, sqlResultType);
        return `${_searchElement} IN ( ${_inList} ) `;
      } else {
        plv8.elog(ERROR, `Operator '${_op}' takes two arguments. First is element to search for, second is array of elements to search.`);
        return null;
      }
    case '!':
    case 'not':
      if (!Array.isArray(_data)) _data = [_data];
      let _notConditions = parseFilter(null, _data, null, 1, 1, _sqlResultType);
      return `NOT ( ${_notConditions} ) `;
    case 'or':
      let _orConditions = parseFilter(null, _data, ' OR ', 1, null, _sqlResultType);
      return `( ${_orConditions} ) `;
    case 'and':
      let _andConditions = parseFilter(null, _data, ' AND ', 1, null, _sqlResultType);
      return `( ${_andConditions} ) `;
    case 'min':
    case 'max':
      return `${_op}( ${parseFilter(null, _data, ', ', 1, _sqlResultType)} ) `;
    case '>':
    case '>=':
      return parseFilter(null, _data, ` ${_op} `, 2, 2, _sqlResultType);
    case '<':
      if (_data.length === 2) {
        return parseFilter(null, _data, ` ${_op} `, 2, 2, _sqlResultType);
      } else if (_data.length === 3) {
        let _compMin = parseFilter(null, _data[0], null, 0, 0, _sqlResultType);
        let _compTarget = parseFilter(null, _data[1], null, 0, 0, _sqlResultType);
        let _compMax = parseFilter(null, _data[2], null, 0, 0, _sqlResultType);
        let _gt = parseFilter('>', [_compTarget, _compMin], _joiningClause, _minArrayElements, _maxArrayElements, _sqlResultType);
        let _lt = parseFilter('<', [_compTarget, _compMax], _joiningClause, _minArrayElements, _maxArrayElements, _sqlResultType);
        return `( ${_gt} AND ${_lt} ) `;
      } else {
        plv8.elog(ERROR, `Operator '${_op}' takes two or three numeric arguments.`);
        return null;
      }
    case '<=':
      if (_data.length === 2) {
        return parseFilter(null, _data, ` ${_op} `, 2, 2, _sqlResultType);
      } else if (_data.length === 3) {
        let _compMin = parseFilter(null, _data[0], null, 0, 0, _sqlResultType);
        let _compTarget = parseFilter(null, _data[1], null, 0, 0, _sqlResultType);
        let _compMax = parseFilter(null, _data[2], null, 0, 0, _sqlResultType);
        return `${_compTarget} BETWEEN ${_compMin} AND ${_compMax}`;
      } else {
        plv8.elog(ERROR, `Operator '${_op}' takes two or three numeric arguments.`);
        return null;
      }
    case 'contains':
      if (_data.length === 2) {
        let _stringToSearchIn = parseFilter(null, _data[0], null, 0, 0, 'string');
        let _stringToSearchWith = parseFilter(null, _data[1], null, 0, 0, 'string');
        return `strpos(lower(${_stringToSearchIn}), lower(${_stringToSearchWith})) > 0`
      } else {
        plv8.elog(ERROR, `Operator '${_op}' takes two string arguments.`);
        return null;
      }
    case 'starts_with':
      if (_data.length === 2) {
        let _stringToSearchIn = parseFilter(null, _data[0], null, 0, 0, 'string');
        let _stringToSearchWith = parseFilter(null, _data[1], null, 0, 0, 'string');
        return `strpos(lower(${_stringToSearchIn}), lower(${_stringToSearchWith})) = 1`
      } else {
        plv8.elog(ERROR, `Operator '${_op}' takes two string arguments.`);
        return null;
      }
    case 'ends_with':
      if (_data.length === 2) {
        let _stringToSearchIn = parseFilter(null, _data[0], null, 0, 0, 'string');
        let _stringToSearchWith = parseFilter(null, _data[1], null, 0, 0, 'string');
        return `right(lower(${_stringToSearchIn}), ${_data[1].length}) = lower(${_stringToSearchWith})`
      } else {
        plv8.elog(ERROR, `Operator '${_op}' takes two string arguments.`);
        return null;
      }
    case 'length':
      if (_data.length === 2) {
        let _stringToSize = parseFilter(null, _data[0], null, 0, 0, 'string');
        let _lengthToMatch = parseFilter(null, _data[1], null, 0, 0, 'number');
        return `length(${_stringToSize}) = ${_lengthToMatch}`;
      } else if (_data.length === 1) {
        let _stringToSize = parseFilter(null, _data[0], null, 0, 0, 'string');
        return `length(${_stringToSize})`;
      } else {
        plv8.elog(ERROR, `Operator '${_op}' takes either a single string argument or a string and a length integer to compare.`);
        return null;
      }
    case 'strpos':
      if (_data.length === 2) {
        let _stringToSearchIn = parseFilter(null, _data[0], null, 0, 0, 'string');
        let _stringToSearchWith = parseFilter(null, _data[1], null, 0, 0, 'string');
        return `strpos(lower(${_stringToSearchIn}), lower(${_stringToSearchWith}))`
      } else {
        plv8.elog(ERROR, `Operator '${_op}' takes two string arguments.`);
        return null;
      }
    case 'regex_test':
      if (_data.length === 2) {
        let _stringToTest = parseFilter(null, _data[0], null, 0, 0, 'string');
        let _pattern = parseFilter(null, _data[1], null, 0, 0, 'string');
        return `${_stringToTest} ~ ${_pattern}`
      } else {
        plv8.elog(ERROR, `Operator '${_op}' takes two string arguments.`);
        return null;
      }
    case 'var':
      if (_sqlResultType === 'text')
        return parseJsonVar(_data, _sqlResultType);
      else
        return castToSqlResultType(parseJsonVar(_data, _sqlResultType), _sqlResultType);
    case 'string':
      return castToSqlResultType(plv8.quote_literal(_data), _sqlResultType);
    case 'textarray':
      if (Array.isArray(_data)) {
        let identedStrings = _data.map(function (it) {
          return plv8.quote_ident(it);
        }).join(',');
        return `'{${identedStrings}}'::text[]`;
      } else {
        plv8.elog(ERROR, "textarray must take an array of strings as input");
        return null;
      }
    case 'jsonstring':
      return `'${plv8.quote_ident(_data)}'`;
    case 'boolean':
      return `to_jsonb(${_data})`;
    case 'number':
      return _data;
    case 'jsonb':
      return castToSqlResultType(plv8.quote_literal(JSON.stringify(_data)), 'jsonb');
    case 'object':
      if (_data === null) {
        return 'NULL';
      }
      let _keys = Object.keys(_data);
      if (_keys.length !== 1) {
        plv8.elog(ERROR, `Invalid operator object ${_data}`);
        return null;
      }
      let _objOp = _keys[0];
      return parseFilter(_objOp, _data[_objOp], _joiningClause, _minArrayElements, _maxArrayElements, _sqlResultType);
    case null:
      if (Array.isArray(_data)) {
        if (
          (_minArrayElements && _data.length < _minArrayElements) ||
          (_maxArrayElements && _data.length > _maxArrayElements)
        ) {
          plv8.elog(ERROR, `Array out of bounds for data ${_data} (${_minArrayElements} to ${_maxArrayElements}).`);
          return null;
        }
        let _vals = _data.map(function (val) {
          return parseFilter((typeof val), val, _joiningClause, _minArrayElements, _maxArrayElements, _sqlResultType);
        });
        if (_joiningClause) {
          return _vals.join(_joiningClause);
        } else {
          return _vals;
        }
      } else {
        return parseFilter((typeof _data), _data, _joiningClause, _minArrayElements, _maxArrayElements, _sqlResultType);
      }
    default:
      plv8.elog(ERROR, `Unsupported data structure op: ${_op}, data: ${JSON.stringify(_data)}`);
      return null;
  }
}