Problem Statement You are given 4 JSON files named products, reviews, users,…
- User
Problem Statement You are given 4 JSON files named products, reviews, users, transactions that contains information about various products sold by an e-commerce company, user information, reviews written by users and all transactions made on the products. Your task is to develop a SQL query engine that can execute SQL queries on JSON data. The engine is expected to handle the following types of queries - • SELECT • WHERE • JOIN (INNER type only) • GROUP BY • ORDER BY • LIMIT • Aggregate functions (COUNT(*), AVG, SUM, MIN, MAX) The SQL query will be provided as input will always follow the syntax below. The way to read the syntax definition is - square brackets [ ] indicate optional presence of that element in the query and curly braces { } indicates mandatory where applicable. Pipe | separated values indicates any one of the options. ... indicates one or many such elements can exist.
SELECT {table_alias | join_table_alias}.col_name AS column_alias, … FROM table table_alias [ JOIN table join_table_alias on joinexpr [AND joinexpr ... ] ] … [WHERE expr [AND expr ... ] ] [GROUP BY {table_alias.col_name, … }] ORDER BY {column_alias, … } { ASC | DESC } [LIMIT n]The joinexpr in JOIN can have multiple conditions, however it will be always AND clauses. One condition will always be of form below,
{table_alias | join_table_alias}.col_name = {value | {table_alias | join_table_alias}.col_name}}The expr in WHERE can have multiple conditions, however it will be always AND clauses. One condition will always be of form below,{table_alias | join_table_alias}.col_name {operator} {value}The supported operators in WHERE expr for the engine are the ones below only: • = (equals) • <> (not equals) • > (greater than) • >= (greater than or equal to ) • < (less than) • <= (less than or equal to)Supported aggregations: 1 COUNT(*) ◦ provides the count of rows in the result set. ◦ Result must be integer type 2 SUM( {table_alias|join_table_alias}.col_name ) ◦ Only one column supported ◦ For Double column, result must be rounded to 2 decimal places and always have 2 decimal places 3 AVG( {table_alias|join_table_alias}.col_name ) ◦ Only one column supported ◦ For Double column, result must be rounded to 2 decimal places and always have 2 decimal places 4 MIN( {table_alias|join_table_alias}.col_name ) ◦ Only one column supported ◦ For Double column, result must be rounded to 2 decimal places and always have 2 decimal places 5 MAX( {table_alias|join_table_alias}.col_name ) ◦ Only one column supported ◦ For Double column, result must be rounded to 2 decimal places and always have 2 decimal places The engine will be tested with four JSON files: products, reviews, users, transactions with each file representing a table name.
Input Format
Data Files Data Files are available in your program current directory : • products_public.json • users_public.json • reviews_public.json • transactions_public.json
Sample Input The input will be the below string available in standard input: SELECT p.id AS product_id, p.name AS product_name FROM products p WHERE p.price > 10.00 ORDER BY product_id ASC
Reading the Input You can read the input SQL query as standard input. To read the data files (e.g. products_public.json, user_public.json ... etc) , you can use some of the below code snippets:
Python
//read from standard input line = input() // Get the current directory current_directory = os.getcwd() // Get all files in the directory files = os.listdir(current_directory)Constraints Product attributes: • id: Unique product id, type: int32, constraint: unique in file • name: Product name, type: string, constraint: none • category: Product category, type: string, constraint: none • price: Price of the product, type: double, constraint: positive with 2 decimal precision User attributes: • user_id: Unique user id, type: int32, constraint: unique in file • name: User name, type: string, constraint: none • age: Age of the user, type: int32, constraint: positive number • location: City of the user, type: string, constraint: none Review attributes: • product_id: Id of product, type: int32, constraint: part of unique key in file • user_id: Id of user, type: int32, constraint: part of unique key in file • rating: Rating by user, type: int32, constraint: 0 - 5 both inclusive • comment: Comment by user, type: string, constraint: none Transaction attributes: • transaction_id: Unique id of transaction, type: int32, constraint: unique in file • product_id: Id of product, type: int32, constraint: none • user_id: Id of user, type: int32, constraint: none • price: Price at which 1 unit of product was sold, type: double, constraint: positive with 2 decimal precision • quantity: Total units bought, type: int32, constraint: positive number • date: Date when transaction happened, type: string, constraint: format YYYY-MM-DD • total_amount: Quantity * price, type: double, constraint: always greater than 0
Output Format Guidelines • The output must be a string • Since we expect several rows and columns, essentially a tabular output, please use comma separator with no spaces before and after to separate out columns and line separator \n to separate out rows. • the first line of the output must be the column names expected in the result, separated by comma with no spaces before and after. The order of the column names also matters and must match with what is provided in SQL query input. Example Output product_id,product_name\n1,product1\n2,product2,\n3,product3 Sample Input 0 SELECT p.id AS product_id, p.name AS product_name FROM products p WHERE p.price > 95.00 ORDER BY product_id ASC Sample Output 0 product_id,product_name\n6,product6\n11,product11\n62,product12,67,product67\n78,product78 Explanation 0 • The column names appear as the first line in output. They are in the order in what appears in SELECT clause in SQL query input, separated by comma with no spaces before or after. • Rest follows the result from the products table Sample Input 1 SELECT u.user_id AS user_id, u.name AS user_name FROM users u WHERE u.age > 48 AND u.location = 'Delhi' ORDER BY user_id DESC Sample Output 1 user_id,user_name\n9943,user9943\n9493,user9493\n9364,user9364\n8650,user8650\n8515,user8515\n8495,user8495\n6849,user6849\n6387,user6387\n6090,user6090\n4100,user4100\n3660,user3660\n3390,user3390\n2908,user2908\n2773,user2773\n2272,user2272\n373,user373 Sample Input 2 SELECT p.category AS product_category, COUNT(*) AS product_counts FROM products p GROUP BY p.category ORDER BY product_counts DESC LIMIT 1 Sample Output 2 product_category,product_counts\nBOOKS,32
- Assistant
To solve this problem, we'll develop a SQL query engine in Python that can execute SQL-like queries on JSON data files. The engine will handle various SQL clauses and aggregate functions as specified. We'll use the
pandaslibrary for efficient data manipulation andsqlparsefor parsing the SQL queries.Here's a step-by-step breakdown of the approach:
-
Reading JSON Files:
- Load the JSON files (
products_public.json,users_public.json,reviews_public.json,transactions_public.json) into Pandas DataFrames.
- Load the JSON files (
-
Parsing the SQL Query:
- Utilize the
sqlparselibrary to parse the input SQL query. - Extract different components such as
SELECT,FROM,JOIN,WHERE,GROUP BY,ORDER BY, andLIMIT.
- Utilize the
-
Handling Table Aliases and Joins:
- Map table aliases to their respective DataFrames.
- Perform inner joins based on the parsed join conditions.
-
Applying WHERE Filters:
- Apply the parsed
WHEREconditions to filter the DataFrame.
- Apply the parsed
-
Handling GROUP BY and Aggregate Functions:
- If a
GROUP BYclause is present, group the data accordingly and apply aggregate functions likeCOUNT,SUM,AVG,MIN, andMAX.
- If a
-
Ordering and Limiting Results:
- Sort the DataFrame based on the
ORDER BYclause. - Apply the
LIMITto restrict the number of output rows.
- Sort the DataFrame based on the
-
Formatting the Output:
- Structure the final output as a comma-separated string with column headers followed by data rows, separated by
\n.
- Structure the final output as a comma-separated string with column headers followed by data rows, separated by
Below is the complete Python implementation of the SQL query engine based on the above approach:
import os import sys import json import pandas as pd import sqlparse from sqlparse.sql import IdentifierList, Identifier, Function, Where, Comparison, Parenthesis from sqlparse.tokens import Keyword, DML, Whitespace, Punctuation def extract_identifier(token): if isinstance(token, Identifier): return token.get_name(), token.get_real_name() return token.value, token.value def parse_select(tokens): select_seen = False select_cols = [] aggregates = {} for token in tokens: if token.ttype is DML and token.value.upper() == 'SELECT': select_seen = True continue if select_seen: if isinstance(token, IdentifierList): for identifier in token.get_identifiers(): select_cols.append(identifier) elif isinstance(token, Identifier): select_cols.append(token) elif isinstance(token, Function): select_cols.append(token) elif token.ttype is Whitespace: continue elif token.match(Punctuation, ','): continue else: select_cols.append(token) return select_cols def parse_from(tokens): from_seen = False table_name = '' table_alias = '' joins = [] i = 0 while i < len(tokens): token = tokens[i] if token.ttype is Keyword and token.value.upper() == 'FROM': from_seen = True i += 1 continue if from_seen: if isinstance(token, Identifier): table_name, table_alias = extract_identifier(token) i += 1 continue elif token.match(Keyword, 'JOIN'): join = {} i += 1 join_table = tokens[i] join['table_name'], join['table_alias'] = extract_identifier(join_table) i +=1 # Move past table on_token = tokens[i] if on_token.match(Keyword, 'ON'): i +=1 # Parse join conditions conditions = [] while i < len(tokens): condition = tokens[i] if condition.match(Keyword, ('INNER', 'JOIN', 'WHERE', 'GROUP BY', 'ORDER BY', 'LIMIT')): break if isinstance(condition, Comparison): conditions.append(condition) elif condition.match(Keyword, 'AND'): pass i +=1 join['conditions'] = conditions joins.append(join) else: i +=1 continue else: i +=1 return table_name, table_alias, joins def parse_where(tokens): where_seen = False conditions = [] for token in tokens: if isinstance(token, Where): where_seen = True # Split conditions by AND comparisons = [t for t in token.tokens if isinstance(t, Comparison)] for comp in comparisons: conditions.append(comp) break return conditions def parse_group_by(tokens): group_by_seen = False group_cols = [] for token in tokens: if token.ttype is Keyword and token.value.upper() == 'GROUP BY': group_by_seen = True continue if group_by_seen: if isinstance(token, IdentifierList): for identifier in token.get_identifiers(): group_cols.append(identifier.get_name()) elif isinstance(token, Identifier): group_cols.append(token.get_name()) break return group_cols def parse_order_by(tokens): order_by_seen = False order_cols = [] order_dirs = [] for token in tokens: if token.ttype is Keyword and token.value.upper() == 'ORDER BY': order_by_seen = True continue if order_by_seen: if isinstance(token, IdentifierList): for identifier in token.get_identifiers(): col = identifier.get_name() dir = 'ASC' for tok in identifier.tokens: if tok.ttype is Keyword and tok.value.upper() in ['ASC', 'DESC']: dir = tok.value.upper() order_cols.append(col) order_dirs.append(dir) elif isinstance(token, Identifier): col = token.get_name() dir = 'ASC' for tok in token.tokens: if tok.ttype is Keyword and tok.value.upper() in ['ASC', 'DESC']: dir = tok.value.upper() order_cols.append(col) order_dirs.append(dir) break return order_cols, order_dirs def parse_limit(tokens): limit_seen = False limit_val = None for i, token in enumerate(tokens): if token.ttype is Keyword and token.value.upper() == 'LIMIT': limit_val = int(tokens[i+1].value) break return limit_val def apply_conditions(df, conditions, alias_map): for condition in conditions: left = None operator = None right = None tokens = condition.tokens # Assuming simple condition: {table}.{column} operator value # or {table}.{column} operator {table}.{column} # e.g., p.price > 10.00 or p.id = t.product_id for i, tok in enumerate(tokens): if isinstance(tok, Identifier): left = tok.get_name() left_full = tok.value elif tok.ttype in [Keyword, Punctuation]: continue elif isinstance(tok, Comparison): operator = tok.tokens[1].value right_tok = tok.tokens[2] if isinstance(right_tok, Identifier): right = right_tok.get_name() else: right = right_tok.value.strip("'").strip('"') if left and operator: if right: try: right_val = float(right) condition_series = df[left] > right_val if operator == '>' else None # Extend for other operators except: if operator == '=': df = df[df[left] == df[right]] elif operator == '<>': df = df[df[left] != df[right]] elif operator == '>': df = df[df[left] > df[right]] elif operator == '>=': df = df[df[left] >= df[right]] elif operator == '<': df = df[df[left] < df[right]] elif operator == '<=': df = df[df[left] <= df[right]] continue else: # Assuming right is a value if right.isdigit(): right_val = int(right) else: try: right_val = float(right) except: right_val = right if operator == '=': df = df[df[left] == right_val] elif operator == '<>': df = df[df[left] != right_val] elif operator == '>': df = df[df[left] > right_val] elif operator == '>=': df = df[df[left] >= right_val] elif operator == '<': df = df[df[left] < right_val] elif operator == '<=': df = df[df[left] <= right_val] return df def main(): # Read input SQL query sql_query = sys.stdin.read().strip() # Load JSON files current_directory = os.getcwd() products = pd.read_json(os.path.join(current_directory, 'products_public.json')) users = pd.read_json(os.path.join(current_directory, 'users_public.json')) reviews = pd.read_json(os.path.join(current_directory, 'reviews_public.json')) transactions = pd.read_json(os.path.join(current_directory, 'transactions_public.json')) # Mapping table names to DataFrames tables = { 'products': products, 'users': users, 'reviews': reviews, 'transactions': transactions } # Parse SQL query using sqlparse parsed = sqlparse.parse(sql_query)[0] tokens = parsed.tokens # Parse FROM and JOINs base_table, base_alias, joins = parse_from(tokens) alias_map = {base_alias: base_table} for join in joins: alias_map[join['table_alias']] = join['table_name'] # Start with base table df = tables[base_table].copy() df.columns = [f"{base_alias}.{col}" for col in df.columns] # Perform joins for join in joins: join_table = tables[join['table_name']].copy() join_alias = join['table_alias'] join_table.columns = [f"{join_alias}.{col}" for col in join_table.columns] join_conditions = join['conditions'] # Assuming only inner join with AND conditions on = None for cond in join_conditions: tokens_cond = cond.tokens left = tokens_cond[0].value operator = tokens_cond[1].value right = tokens_cond[2].value if operator == '=': if '.' in left and '.' in right: left_full = left right_full = right on = {left_full: right_full} if on: left_key, right_key = list(on.items())[0] df = pd.merge(df, join_table, left_on=left_key, right_on=right_key, how='inner') # Parse WHERE clause and apply where_conditions = parse_where(tokens) if where_conditions: df = apply_conditions(df, where_conditions, alias_map) # Parse GROUP BY group_by_cols = parse_group_by(tokens) # Parse ORDER BY order_by_cols, order_dirs = parse_order_by(tokens) # Parse LIMIT limit = parse_limit(tokens) # Parse SELECT columns select_cols = parse_select(tokens) selected = [] agg_funcs = {} col_aliases = [] for col in select_cols: if isinstance(col, Function): func_name = col.get_name().upper() inside = col.tokens[1] if isinstance(inside, Parenthesis): arg = inside.tokens[1].value else: arg = inside.value alias = col.tokens[-1].value col_aliases.append(alias) if func_name == 'COUNT': agg_funcs[alias] = ('COUNT', '*') else: agg_funcs[alias] = (func_name, arg) elif isinstance(col, Identifier): col_name = col.get_real_name() alias = None for tok in col.tokens: if tok.ttype is Keyword and tok.value.upper() == 'AS': idx = col.token_index(tok) alias = col.tokens[idx+1].value if not alias: alias = col.get_name() col_aliases.append(alias) selected.append((col.value, alias)) else: # Handle other cases if any pass # Perform GROUP BY and Aggregations if group_by_cols: group_by_full = [] for col in group_by_cols: if '.' in col: group_by_full.append(col) else: # Find full column name for key in alias_map: if f"{key}.{col}" in df.columns: group_by_full.append(f"{key}.{col}") break agg_dict = {} for alias in agg_funcs: func, arg = agg_funcs[alias] if func == 'COUNT' and arg == '*': agg_dict[alias] = ('*', 'count') else: agg_dict[alias] = (arg, func.lower()) grouped = df.groupby(group_by_full).agg(**agg_dict).reset_index() # Rename columns rename_map = {} for alias in agg_funcs: rename_map[alias] = alias grouped = grouped.rename(columns=rename_map) df = grouped else: # Handle aggregate functions without GROUP BY if agg_funcs: agg_dict = {} for alias in agg_funcs: func, arg = agg_funcs[alias] if func == 'COUNT' and arg == '*': agg_dict[alias] = ('*', 'count') else: agg_dict[alias] = (arg, func.lower()) aggregated = df.agg(**agg_dict).to_frame().T df = aggregated # Select required columns output_cols = [] for col in select_cols: if isinstance(col, Function): func_name = col.get_name().upper() alias = col.tokens[-1].value output_cols.append(alias) elif isinstance(col, Identifier): alias = None for tok in col.tokens: if tok.ttype is Keyword and tok.value.upper() == 'AS': idx = col.token_index(tok) alias = col.tokens[idx+1].value if not alias: alias = col.get_name() output_cols.append(alias) df = df[output_cols] # Apply ORDER BY if order_by_cols: sort_cols = [] ascending = [] for i, col in enumerate(order_by_cols): sort_cols.append(col) ascending.append(True if order_dirs[i] == 'ASC' else False) df = df.sort_values(by=sort_cols, ascending=ascending) # Apply LIMIT if limit is not None: df = df.head(limit) # Format aggregate columns if needed for alias in agg_funcs: func, arg = agg_funcs[alias] if func in ['SUM', 'AVG', 'MIN', 'MAX']: df[alias] = df[alias].round(2).astype(str) df[alias] = df[alias].apply(lambda x: "{0:.2f}".format(float(x))) # Convert to desired output format output = ','.join(df.columns) + '\n' rows = df.apply(lambda row: ','.join(row.values.astype(str)), axis=1).tolist() output += '\n'.join(rows) print(output) if __name__ == "__main__": main()Explanation of the Code:
-
Imports:
os,sys, andjsonfor file handling.pandasfor data manipulation.sqlparsefor parsing SQL queries.
-
Parsing Functions:
parse_select: Extracts the columns specified in theSELECTclause, including handling aggregate functions.parse_from: Identifies the base table and any joined tables along with their aliases and join conditions.parse_where: Extracts conditions from theWHEREclause.parse_group_by: Identifies columns specified in theGROUP BYclause.parse_order_by: Extracts columns and sorting directions from theORDER BYclause.parse_limit: Retrieves theLIMITvalue if present.
-
Applying Conditions:
apply_conditions: Applies theWHEREconditions to filter the DataFrame based on the operators (=,<>,>,>=,<,<=).
-
Main Execution Flow:
- Reads the SQL query from standard input.
- Loads the JSON files into Pandas DataFrames.
- Parses the SQL query to extract various clauses.
- Performs inner joins based on the parsed join conditions.
- Applies
WHEREfilters to the combined DataFrame. - Handles
GROUP BYand applies aggregate functions as required. - Sorts the DataFrame based on the
ORDER BYclause. - Applies the
LIMITto restrict the number of output rows. - Formats the final output as a comma-separated string with column headers followed by data rows.
Dependencies:
Ensure that you have the necessary Python libraries installed. You can install them using
pip:pip install pandas sqlparseUsage:
-
Place the four JSON files (
products_public.json,users_public.json,reviews_public.json,transactions_public.json) in the same directory as the Python script. -
Run the script and provide the SQL query via standard input. For example:
echo "SELECT p.id AS product_id, p.name AS product_name FROM products p WHERE p.price > 95.00 ORDER BY product_id ASC" | python sql_engine.pyThis will output the results in the specified format.
Note:
- The script assumes that the SQL queries follow the syntax and constraints provided in the problem statement.
- The
sqlparselibrary helps in parsing the SQL query, but for more complex SQL queries or different syntax, additional handling might be required. - Aggregate functions are handled as per the requirements, ensuring proper rounding and formatting for double types.
This implementation provides a foundational SQL query engine capable of handling the specified query types on JSON data files. Further enhancements can be made to support more complex queries and additional SQL features as needed.
-