TRANSLATE JSON QUERY EXPRESSION ON SINGLE TABLE TO SQL QUERY
481509
def query(self, query, stacked=False):
        
        from jx_base.query import QueryOp

        query = QueryOp.wrap(query)

        sql, post = self._subquery(query, isolate=False, stacked=stacked)
        query.data = post(sql)
        return query.data
335405
function jsonToQueryString(json) {
    return '?' +
        Object
        .keys(json)
        .map(function (key) {
            if (json[key] !== null) {
                return encodeURIComponent(key) + '=' +
                    encodeURIComponent(json[key]);
            }
        })
        .join('&');
}
625852
function parse_query($query) {
		$operations = array('SELECT','INSERT','UPDATE','DELETE','REPLACE','RENAME','SHOW','SET','DROP','CREATE INDEX','CREATE TABLE','EXPLAIN','DESCRIBE');

		$query_ex = '/(?P<operation>'.implode("|",$operations).')\s*(?P<columns>.?)\s*(FROM|INTO)\s*(?P<table>`?.*?`?)\s*(WHERE)?\s*?(?P<where>.*?)\s*?(ORDER BY)?\s*?(?P<order>.*?)?\s*?(LIMIT)?(?P<limit>.*?)?$/i';
		preg_match($query_ex, $query, $retn);
		return $retn;	
    }
944239
def select(table, cols="*", where=(), group="", order=(), limit=(), **kwargs):
    """"""
    where = dict(where, **kwargs).items()
    sql, args = makeSQL("SELECT", table, cols, where, group, order, limit)
    return execute(sql, args)
289426
def any2sql(func, definition_dict=None, **definition_kwargs):
    
    if definition_dict and definition_kwargs:
        raise InputError("Cannot process dict and kwargs input at the same time")

    definition = load_input(definition_dict or definition_kwargs)

    if definition.get('returning', '') == '*':
        definition['returning'] = mosql_raw('*')

    try:
        result = func(**definition)
    except (TypeError, AttributeError) as exc:
        raise ClauseError("Clause definition error: %s" % str(exc)) from exc
    except Exception as exc:
        import json2sql.errors as json2sql_errors

        if exc.__class__.__name__ not in json2sql_errors.__dict__.keys():
            raise json2sql_errors.Json2SqlInternalError("Unhandled error: %s" % str(exc)) from exc
        raise

    return result
1282884
def _select(self, tree, allow_select_scan):
        
        tablename = tree.table
        desc = self.describe(tablename, require=True)
        kwargs = {}
        if tree.consistent:
            kwargs["consistent"] = True

        visitor = Visitor(self.reserved_words)

        selection = SelectionExpression.from_selection(tree.attrs)
        if selection.is_count:
            kwargs["select"] = "COUNT"

        if tree.keys_in:
            if tree.limit:
                raise SyntaxError("Cannot use LIMIT with KEYS IN")
            elif tree.using:
                raise SyntaxError("Cannot use USING with KEYS IN")
            elif tree.order:
                raise SyntaxError("Cannot use DESC/ASC with KEYS IN")
            elif tree.where:
                raise SyntaxError("Cannot use WHERE with KEYS IN")
            keys = list(self._iter_where_in(tree))
            kwargs["attributes"] = selection.build(visitor)
            kwargs["alias"] = visitor.attribute_names
            return self.connection.batch_get(tablename, keys=keys, **kwargs)

        if tree.limit:
            if tree.scan_limit:
                kwargs["limit"] = Limit(
                    scan_limit=resolve(tree.scan_limit[2]),
                    item_limit=resolve(tree.limit[1]),
                    strict=True,
                )
            else:
                kwargs["limit"] = Limit(item_limit=resolve(tree.limit[1]), strict=True)
        elif tree.scan_limit:
            kwargs["limit"] = Limit(scan_limit=resolve(tree.scan_limit[2]))

        (action, query_kwargs, index) = self._build_query(desc, tree, visitor)
        if action == "scan" and not allow_select_scan:
            raise SyntaxError(
                "No index found for query. Please use a SCAN query, or "
                "set allow_select_scan=True\nopt allow_select_scan true"
            )
        order_by = None
        if tree.order_by:
            order_by = tree.order_by[0]
        reverse = tree.order == "DESC"
        if tree.order:
            if action == "scan" and not tree.order_by:
                raise SyntaxError(
                    "No index found for query, "
                    "cannot use ASC or DESC without "
                    "ORDER BY <field>"
                )
            if action == "query":
                if order_by is None or order_by == index.range_key:
                    kwargs["desc"] = reverse

        kwargs.update(query_kwargs)

        # This is a special case for when we're querying an index and selecting
        # fields that aren't projected into the index.
        # We will change the query to only fetch the primary keys, and then
        # fill in the selected attributes after the fact.
        fetch_attrs_after = False
        if index is not None and not index.projects_all_attributes(
            selection.all_fields
        ):
            kwargs["attributes"] = [
                visitor.get_field(a) for a in desc.primary_key_attributes
            ]
            fetch_attrs_after = True
        else:
            kwargs["attributes"] = selection.build(visitor)
        kwargs["expr_values"] = visitor.expression_values
        kwargs["alias"] = visitor.attribute_names

        method = getattr(self.connection, action + "2")
        result = method(tablename, **kwargs)

        # If the queried index didn't project the selected attributes, we need
        # to do a BatchGetItem to fetch all the data.
        if fetch_attrs_after:
            if not isinstance(result, list):
                result = list(result)
            # If no results, no need to batch_get
            if not result:
                return result
            visitor = Visitor(self.reserved_words)
            kwargs = {"keys": [desc.primary_key(item) for item in result]}
            kwargs["attributes"] = selection.build(visitor)
            kwargs["alias"] = visitor.attribute_names
            result = self.connection.batch_get(tablename, **kwargs)

        def order(items):
            """ Sort the items by the specified keys """
            if order_by is None:
                return items
            if index is None or order_by != index.range_key:
                if not isinstance(items, list):
                    items = list(items)
                items.sort(key=lambda x: x.get(order_by), reverse=reverse)
            return items

        # Save the data to a file
        if tree.save_file:
            if selection.is_count:
                raise Exception("Cannot use count(*) with SAVE")
            count = 0
            result = order(selection.convert(item, True) for item in result)
            filename = tree.save_file[0]
            if filename[0] in ['"', "'"]:
                filename = unwrap(filename)
            # If it's still an iterator, convert to a list so we can iterate
            # multiple times.
            if not isinstance(result, list):
                result = list(result)
            remainder, ext = os.path.splitext(filename)
            if ext.lower() in [".gz", ".gzip"]:
                ext = os.path.splitext(remainder)[1]
                opened = gzip.open(filename, "wb")
            else:
                opened = open(filename, "wb")
            if ext.lower() == ".csv":
                if selection.all_keys:
                    headers = selection.all_keys
                else:
                    # Have to do this to get all the headers :(
                    result = list(result)
                    all_headers = set()
                    for item in result:
                        all_headers.update(item.keys())
                    headers = list(all_headers)
                with opened as ofile:
                    writer = csv.DictWriter(
                        ofile, fieldnames=headers, extrasaction="ignore"
                    )
                    writer.writeheader()
                    for item in result:
                        count += 1
                        writer.writerow(item)
            elif ext.lower() == ".json":
                with opened as ofile:
                    for item in result:
                        count += 1
                        ofile.write(self._encoder.encode(item))
                        ofile.write("\n")
            else:
                with opened as ofile:
                    for item in result:
                        count += 1
                        pickle.dump(item, ofile)
            return count
        elif not selection.is_count:
            result = order(selection.convert(item) for item in result)

        return result
677522
private static void insertJsonColumn(CqlSession session) {

    User alice = new User("alice", 30);
    User bob = new User("bob", 35);

    // Build and execute a simple statement

    Statement stmt =
        insertInto("examples", "json_jackson_column")
            .value("id", literal(1))
            // the User object will be converted into a String and persisted into the VARCHAR column
            // "json"
            .value("json", literal(alice, session.getContext().getCodecRegistry()))
            .build();
    session.execute(stmt);

    // The JSON object can be a bound value if the statement is prepared
    // (subsequent calls to the prepare() method will return cached statement)
    PreparedStatement pst =
        session.prepare(
            insertInto("examples", "json_jackson_column")
                .value("id", bindMarker("id"))
                .value("json", bindMarker("json"))
                .build());
    session.execute(pst.bind().setInt("id", 2).set("json", bob, User.class));
  }
827989
function columnsToSQL(columns) {
    return columns
        .map(column => {
            let str = exprToSQL(column.expr);

            if (column.as !== null) {
                str += ' AS ';
                if (column.as.match(/^[a-z_][0-9a-z_]*$/i)) str += identifierToSql(column.as);
                else str += '"' + column.as + '"';
            }

            return str;
        })
        .join(', ');
}
354766
function(query) {
        var queryObj = {q: ''};
        if(!query) {
            return queryObj;
        }

        var qArray = query.split('&');
        var qObj = qs.parse(query);
        for(var i = 0, len = qArray.length; i < len; i++) {
            var queryItem = decodeURIComponent(qArray[i]).replace('=', '');
            if(/^\?/g.test(queryItem)) {
                debug('matched query: %s', queryItem) ;
                queryObj.q = queryItem;
                break;
            }
        }

        queryObj.m = qObj._mcss == 'true' || qObj._mcss == 'y';
        return queryObj;
    }
1088787
def to_sql
      sql = ["select"]
      select_values << "*" if select_values.empty?

      sql << select_values.uniq.join(", ")

      sql << "from #{build_series_name}"

      sql << "where #{where_values.join(' and ')}" unless where_values.empty?

      unless group_values.empty? && time_value.nil?
        group_fields = (time_value.nil? ? [] : ['time(' + @values[:time] + ')']) + group_values
        group_fields.uniq!
        sql << "group by #{group_fields.join(', ')}"
      end

      sql << "fill(#{fill_value})" unless fill_value.nil?

      sql << "order by #{order_values.uniq.join(',')}" unless order_values.empty?

      sql << "limit #{limit_value}" unless limit_value.nil?
      sql << "offset #{offset_value}" unless offset_value.nil?
      sql << "slimit #{slimit_value}" unless slimit_value.nil?
      sql << "soffset #{soffset_value}" unless soffset_value.nil?
      sql.join " "
    end
192239
def _construct_select_query(**filter_definition):
    
    table_name = filter_definition.pop('table')
    distinct = filter_definition.pop('distinct', False)
    select_count = filter_definition.pop('count', False)

    if distinct and select_count:
        raise UnsupportedDefinitionError('SELECT (DISTINCT ...) is not supported')

    if select_count and 'select' in filter_definition:
        raise UnsupportedDefinitionError('SELECT COUNT(columns) is not supported')

    if 'joins' in filter_definition:
        join_definitions = filter_definition.pop('joins')

        if not isinstance(join_definitions, (tuple, list)):
            join_definitions = (join_definitions,)

        filter_definition['joins'] = []
        for join_def in join_definitions:
            filter_definition['joins'].append(_expand_join(join_def))

    if 'where' in filter_definition:
        for key, value in filter_definition['where'].items():
            if is_filter_query(value):
                # We can do it recursively here
                sub_query = value.pop(DEFAULT_FILTER_KEY)
                if value:
                    raise ParsingInputError("Unknown keys for sub-query provided: %s" % value)
                filter_definition['where'][key] = mosql_raw('( {} )'.format(_construct_select_query(**sub_query)))
            elif isinstance(value, str) and value.startswith('$') and QUERY_REFERENCE.fullmatch(value[1:]):
                # Make sure we construct correct query with escaped table name and escaped column for sub-queries
                filter_definition['where'][key] = mosql_raw('"{}"'.format('"."'.join(value[1:].split('.'))))

    raw_select = select(table_name, **filter_definition)

    if distinct:
        # Note that we want to limit replace to the current SELECT, not affect nested ones
        raw_select = raw_select.replace('SELECT', 'SELECT DISTINCT', 1)
    if select_count:
        # Note that we want to limit replace to the current SELECT, not affect nested ones
        raw_select = raw_select.replace('SELECT *', 'SELECT COUNT(*)', 1)

    return raw_select
1058732
function  doQuery(option, inQuery, cb)  {
	var  expr = option.expr,
		 tables = toTableArrays(expr.table),
		 mainTable = tables[0].alias || tables[0].name;
	//console.log('table names are:\n%s', JSON.stringify(tables, null, 4));

	getQueryTableSchema(tables, function(err, tableSchema) {
		if (err)
			return cb(err);

		//console.log('table schema:\n%s', JSON.stringify(tableSchema, null, 4));
		// now we can move on to analyze the query
		// first, we need to collect filter columns...
		var  noColumnSpecified = !(expr.columns || option.raw),
			 whereCol = [];
		if (expr.filters)
			collectFilterColumns(mainTable, expr.filters, whereCol);

		var  colInfo = scanColumns(tableSchema, mainTable, expr.columns, whereCol),
			 isSqlCol = colInfo.nc.length === 0,
			 isSqlFilter = matchSQLFilter(tableSchema, whereCol);
		//console.log('colInfo is:\n%s', JSON.stringify(colInfo, null, 4));
		//console.log('whereCol is:\n%s', JSON.stringify(whereCol, null, 4));

		if (isSqlCol && isSqlFilter)  {
			// all query column are 'inertia', go ahead to do SQL query
			//console.log('doing standard SQL query');
			//option.debug = true;
			soar.execute( option, inQuery, function(err, result) {
				if (noColumnSpecified && result)  {
					// user does not specify what columns to be returned.
					// return all columns and json_columns are extracted/added
					if (tableSchema[mainTable].columns.indexOf('_c_json') >= 0)
						mergeJsonColumns(option.op === 'query'  ?  [result] : result);
				}
				//console.log('query result is:\n%s', JSON.stringify(result, null, 4));
				cb(err, result);
			});
		}
		else  {
			// there are variable columns, we have to reconstruct the query terms to make them SQL only
			var  schema = tableSchema[mainTable],
				 sqlExpr = cloneObj(expr),
				 sqlCmd = {op: 'list', expr: sqlExpr};
				 
			sqlExpr.columns = colInfo.cc;
			//sqlCmd.range = sqlCmd.range || soar.range(1, _queryLimit);

			if (!isSqlFilter)
				sqlExpr.filters = buildSqlFilter(expr.filters, schema.columns);
			//console.log('The adjusted SQL expression is\n%s', JSON.stringify(sqlCmd, null, 2));

			// now we're ready to perform SQL query
			var  listQuery = sqlExpr.filters  ?  inQuery : null;
			
			var  pgIdx = 1,
				 hasMore = true,
				 listSum = [];
			async.whilst(
				function()  {return  hasMore;},
				function(cb)  {
					sqlCmd.range = soar.range(pgIdx++, _queryPageSize);
					//sqlCmd.debug = true;
					soar.execute( sqlCmd, listQuery, function(err, result, count) {
						if (err)  {
							console.log( err.stack );
							return cb(err);
						}
						
						hasMore = result.length === _queryPageSize;
		
						//console.log('result of adjusted SQL:\n%s', JSON.stringify(result, null, 2));
						if (!isSqlCol)
							// add back specified non-sql properties
							mergeSelectedJsonColumns(colInfo, result);
						else
							// user does not specify what columns to be returned.
							// return all columns and json_columns are extracted/added
							mergeJsonColumns(result);
						//console.log('result before final filtering\n%s', JSON.stringify(result, null, 2));
		
						if (!isSqlFilter)  {
							// ok, let's run a JSON-FP program to get the final result
							var  rdQuery = {},
								 reducedF = reduceFilter(mainTable, expr.filters, schema.columns, colInfo.nc, inQuery, rdQuery);
							//console.log('filter is\n%s', JSON.stringify(reducedF, null, 2));
							//console.log('query value is\n%s', JSON.stringify(rdQuery, null, 2));
							result = jsonQ.filter( result, reducedF, rdQuery );
						}
						//console.log('result after final filtering\n%s', JSON.stringify(result, null, 2));
		
						if (colInfo.more.length)  {
							// remove auto-added columns
							var  rmc = colInfo.more;
							result.forEach(function(data) {
								for (var i in rmc)
									delete  data[rmc[i]];
							});
						}
		
						// not using listSum = listSum.concat(result) for potential performance gains
						for (var i in result)
							listSum.push( result[i] );
							
						//console.log('current size: %d, accumulated size: %d, total size: %d, hasMore: %s', result.length, listSum.length, count, hasMore);
						cb();
					});
				},
				function(err)  {
					cb(err, listSum);
				}
			);
		}
	});
}
711055
def as_sql(self):
        
        return ','.join('`%s`' % name if self.attributes[name].sql_expression is None
                        else '%s as `%s`' % (self.attributes[name].sql_expression, name)
                        for name in self.names)
3863
@Override
    public void loadFromJSONObject( JSONObject jobj, Database db ) throws JSONException {
        super.loadFromJSONObject(jobj, db);
        m_lookupType = IndexLookupType.get( jobj.getString( Members.LOOKUP_TYPE.name() ) );
        m_sortDirection = SortDirectionType.get( jobj.getString( Members.SORT_DIRECTION.name() ) );
        if (jobj.has(Members.HAS_OFFSET_RANK.name())) {
            m_hasOffsetRankOptimization = jobj.getBoolean(Members.HAS_OFFSET_RANK.name());
        }
        m_purpose = jobj.has(Members.PURPOSE.name()) ?
                jobj.getInt(Members.PURPOSE.name()) : FOR_SCANNING_PERFORMANCE_OR_ORDERING;
        m_targetIndexName = jobj.getString(Members.TARGET_INDEX_NAME.name());
        m_catalogIndex = db.getTables().get(super.m_targetTableName).getIndexes().get(m_targetIndexName);
        // load end_expression
        m_endExpression = AbstractExpression.fromJSONChild(jobj, Members.END_EXPRESSION.name(), m_tableScan);
        // load initial_expression
        m_initialExpression = AbstractExpression.fromJSONChild(jobj, Members.INITIAL_EXPRESSION.name(), m_tableScan);
        // load searchkey_expressions
        AbstractExpression.loadFromJSONArrayChild(m_searchkeyExpressions, jobj,
                Members.SEARCHKEY_EXPRESSIONS.name(), m_tableScan);
        // load COMPARE_NOTDISTINCT flag vector
        loadBooleanArrayFromJSONObject(jobj, Members.COMPARE_NOTDISTINCT.name(), m_compareNotDistinct);
        // load skip_null_predicate
        m_skip_null_predicate = AbstractExpression.fromJSONChild(jobj, Members.SKIP_NULL_PREDICATE.name(), m_tableScan);
    }
1188326
private static void selectWithCoreApi(CqlSession session) {
    // Reading the whole row as a JSON object:
    Row row =
        session
            .execute(
                SimpleStatement.newInstance(
                    "SELECT JSON * FROM examples.querybuilder_json WHERE id = ?", 1))
            .one();
    assert row != null;
    System.out.printf("Entry #1 as JSON: %s%n", row.getString("[json]"));

    // Extracting a particular column as JSON:
    row =
        session
            .execute(
                SimpleStatement.newInstance(
                    "SELECT id, toJson(specs) AS json_specs FROM examples.querybuilder_json WHERE id = ?",
                    2))
            .one();
    assert row != null;
    System.out.printf(
        "Entry #%d's specs as JSON: %s%n", row.getInt("id"), row.getString("json_specs"));
  }
486305
public function query($sql, $bind = [], $fetchMode = \PDO::FETCH_ASSOC, $useMaster = false)
    {
        $sql = $this->_replaceQuoteCharacters($sql);

        try {
            $statement = $bind ? $this->_execute($sql, $bind) : @$this->_getPdo()->query($sql);
        } catch (\PDOException $e) {
            $failed = true;

            if ($this->_transaction_level === 0 && !$this->_ping()) {
                try {
                    $this->close();
                    $statement = $bind ? $this->_execute($sql, $bind) : @$this->_getPdo()->query($sql);
                    $failed = false;
                } catch (\PDOException $e) {

                }
            }

            if ($failed) {
                throw new DbException([
                    ':message => ' . PHP_EOL . 'SQL: ":sql"' . PHP_EOL . ' BIND: :bind',
                    'message' => $e->getMessage(),
                    'sql' => $sql,
                    'bind' => json_encode($bind, JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT)
                ], 0, $e);
            }
        }

        return $statement->fetchAll($fetchMode);
    }
926905
def _json_column(**kwargs):
    """"""
    return db.Column(
        JSONType().with_variant(
            postgresql.JSON(none_as_null=True),
            'postgresql',
        ),
        nullable=True,
        **kwargs
    )