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
use the below base code to solve the problem import os import json import sys import re from copy import deepcopy
def load_json_file(filename): with open(filename, 'r') as f: return [json.loads(line) for line in f]
- Assistant
To address the problem, we'll develop a Python script that functions as a simple SQL query engine tailored to handle JSON data. Here's a step-by-step overview of the approach:
-
Loading Data:
- Load data from the provided JSON files (
products_public.json,users_public.json,reviews_public.json,transactions_public.json).
- Load data from the provided JSON files (
-
Parsing the SQL Query:
- Utilize regular expressions to parse the SQL query into its constituent parts:
SELECT,FROM,JOIN,WHERE,GROUP BY,ORDER BY, andLIMIT. - Extract column selections, table aliases, join conditions, filter expressions, grouping columns, ordering preferences, and limit values.
- Utilize regular expressions to parse the SQL query into its constituent parts:
-
Executing the Query:
- FROM and JOIN: Merge tables based on the specified joins, supporting only INNER JOINS.
- WHERE: Apply filtering conditions using the supported operators.
- GROUP BY and Aggregations: Group data as specified and compute aggregate functions like
COUNT,SUM,AVG,MIN, andMAX. - ORDER BY: Sort the resulting data based on the specified columns and order (ASC/DESC).
- LIMIT: Restrict the number of output rows as per the
LIMITclause.
-
Generating Output:
- Format the final result as a string with comma-separated values and newline characters to separate rows, ensuring that the column headers are correctly ordered.
Below is the comprehensive Python script that accomplishes the above steps:
import os import json import sys import re from copy import deepcopy def load_json_file(filename): with open(filename, 'r') as f: return [json.loads(line) for line in f] def parse_sql(query): # Regex patterns for different clauses select_pattern = r"SELECT\s+(.*?)\s+FROM\s" from_pattern = r"FROM\s+(\w+)\s+(\w+)" join_pattern = r"JOIN\s+(\w+)\s+(\w+)\s+ON\s+(.*?)(?=\s+(WHERE|JOIN|GROUP BY|ORDER BY|LIMIT|$))" where_pattern = r"WHERE\s+(.*?)(?=\s+(GROUP BY|ORDER BY|LIMIT|$))" group_by_pattern = r"GROUP BY\s+(.*?)(?=\s+(ORDER BY|LIMIT|$))" order_by_pattern = r"ORDER BY\s+(.*?)(?=\s+(LIMIT|$))" limit_pattern = r"LIMIT\s+(\d+)" parsed = {} # SELECT select_match = re.search(select_pattern, query, re.IGNORECASE) if select_match: parsed['select'] = [col.strip() for col in select_match.group(1).split(',')] # FROM from_match = re.search(from_pattern, query, re.IGNORECASE) if from_match: parsed['from_table'] = from_match.group(1) parsed['from_alias'] = from_match.group(2) # JOINs joins = re.findall(join_pattern, query, re.IGNORECASE) parsed['joins'] = [] for join in joins: join_table, join_alias, join_condition, _ = join join_conditions = [cond.strip() for cond in join_condition.split('AND')] parsed['joins'].append({ 'table': join_table, 'alias': join_alias, 'conditions': join_conditions }) # WHERE where_match = re.search(where_pattern, query, re.IGNORECASE) if where_match: conditions = [cond.strip() for cond in re.split(r'\bAND\b', where_match.group(1), flags=re.IGNORECASE)] parsed['where'] = conditions # GROUP BY group_by_match = re.search(group_by_pattern, query, re.IGNORECASE) if group_by_match: group_by_cols = [col.strip() for col in group_by_match.group(1).split(',')] parsed['group_by'] = group_by_cols # ORDER BY order_by_match = re.search(order_by_pattern, query, re.IGNORECASE) if order_by_match: order_clause = order_by_match.group(1).strip() # Split by comma and parse each order orders = [part.strip() for part in order_clause.split(',')] parsed['order_by'] = [] for order in orders: parts = order.split() if len(parts) == 1: parsed['order_by'].append( (parts[0], 'ASC') ) else: parsed['order_by'].append( (parts[0], parts[1].upper()) ) # LIMIT limit_match = re.search(limit_pattern, query, re.IGNORECASE) if limit_match: parsed['limit'] = int(limit_match.group(1)) return parsed def evaluate_condition(cond, row): # Condition format: alias.col operator value OR alias.col = alias.col # Supported operators: =, <>, >, >=, <, <= operators = ['>=', '<=', '<>', '=', '>', '<'] for op in operators: if op in cond: left, right = cond.split(op) left = left.strip() right = right.strip() # Handle left operand if '.' in left: alias, col = left.split('.') left_val = row.get(alias + '.' + col) else: left_val = row.get(left) # Handle right operand if '.' in right: alias, col = right.split('.') right_val = row.get(alias + '.' + col) else: # Remove quotes if string if right.startswith("'") and right.endswith("'"): right_val = right[1:-1] elif '.' in right: right_val = float(right) else: try: right_val = int(right) except: try: right_val = float(right) except: right_val = right # Compare based on operator if op == '=': return left_val == right_val elif op == '<>': return left_val != right_val elif op == '>': return left_val > right_val elif op == '>=': return left_val >= right_val elif op == '<': return left_val < right_val elif op == '<=': return left_val <= right_val return False def apply_where(rows, where_conditions): if not where_conditions: return rows filtered = [] for row in rows: if all(evaluate_condition(cond, row) for cond in where_conditions): filtered.append(row) return filtered def perform_join(left_rows, right_rows, join_conditions, left_alias, right_alias): joined = [] for lrow in left_rows: for rrow in right_rows: merged_row = deepcopy(lrow) merged_row.update({f"{right_alias}.{k}": v for k, v in rrow.items()}) match = True for cond in join_conditions: # Cond is in form alias.col = value or alias.col = alias.col operators = ['>=', '<=', '<>', '=', '>', '<'] for op in operators: if op in cond: left, right = cond.split(op) left = left.strip() right = right.strip() # Get left value if '.' in left: alias, col = left.split('.') left_val = merged_row.get(alias + '.' + col) else: left_val = merged_row.get(left) # Get right value if '.' in right: alias, col = right.split('.') right_val = merged_row.get(alias + '.' + col) else: # Remove quotes if string if right.startswith("'") and right.endswith("'"): right_val = right[1:-1] else: try: if '.' in right: right_val = float(right) else: right_val = int(right) except: right_val = right # Compare based on operator if op == '=': if left_val != right_val: match = False elif op == '<>': if left_val == right_val: match = False elif op == '>': if left_val <= right_val: match = False elif op == '>=': if left_val < right_val: match = False elif op == '<': if left_val >= right_val: match = False elif op == '<=': if left_val > right_val: match = False if not match: break if match: joined.append(merged_row) return joined def apply_group_by(rows, group_by_cols, select_cols): grouped = {} for row in rows: key = tuple(row[col] for col in group_by_cols) if key not in grouped: grouped[key] = [] grouped[key].append(row) result = [] for key, group in grouped.items(): aggregated_row = {} # First, include group by columns for i, col in enumerate(group_by_cols): aggregated_row[col] = key[i] # Then, process select columns for col in select_cols: if '(' in col: # aggregate function func, inner = re.match(r"(\w+)\((.*?)\)", col).groups() func = func.upper() inner = inner.strip() if func == 'COUNT': aggregated_row[col] = len(group) else: values = [r[inner] for r in group] if func == 'SUM': s = sum(values) if isinstance(s, float): s = round(s + 1e-8, 2) s = f"{s:.2f}" aggregated_row[col] = s elif func == 'AVG': avg = sum(values) / len(values) avg = round(avg + 1e-8, 2) aggregated_row[col] = f"{avg:.2f}" elif func == 'MIN': mn = min(values) if isinstance(mn, float): mn = round(mn + 1e-8, 2) mn = f"{mn:.2f}" aggregated_row[col] = mn elif func == 'MAX': mx = max(values) if isinstance(mx, float): mx = round(mx + 1e-8, 2) mx = f"{mx:.2f}" aggregated_row[col] = mx result.append(aggregated_row) return result def main(): # Read SQL query from standard input query = sys.stdin.read().strip() # Parse SQL query parsed = parse_sql(query) # Load data current_directory = os.getcwd() tables = {} table_files = { 'products': 'products_public.json', 'users': 'users_public.json', 'reviews': 'reviews_public.json', 'transactions': 'transactions_public.json' } for table, filename in table_files.items(): path = os.path.join(current_directory, filename) if os.path.exists(path): tables[table] = load_json_file(path) else: tables[table] = [] # FROM table from_table = parsed['from_table'] from_alias = parsed['from_alias'] rows = [] for row in tables[from_table]: prefixed_row = {f"{from_alias}.{k}": v for k, v in row.items()} rows.append(prefixed_row) # JOINs for join in parsed.get('joins', []): join_table = join['table'] join_alias = join['alias'] join_conditions = join['conditions'] right_rows = tables[join_table] rows = perform_join(rows, right_rows, join_conditions, from_alias, join_alias) # WHERE rows = apply_where(rows, parsed.get('where', [])) # GROUP BY select_cols = parsed['select'] if 'group_by' in parsed: group_by_cols = parsed['group_by'] processed_rows = apply_group_by(rows, group_by_cols, select_cols) else: # If no GROUP BY but select has aggregate functions has_aggregate = any('(' in col for col in select_cols) if has_aggregate: aggregated_row = {} for col in select_cols: if '(' in col: func, inner = re.match(r"(\w+)\((.*?)\)", col).groups() func = func.upper() inner = inner.strip() if func == 'COUNT': aggregated_row[col] = len(rows) else: values = [r[inner] for r in rows] if func == 'SUM': s = sum(values) if isinstance(s, float): s = round(s + 1e-8, 2) s = f"{s:.2f}" aggregated_row[col] = s elif func == 'AVG': avg = sum(values) / len(values) if len(values) > 0 else 0 avg = round(avg + 1e-8, 2) aggregated_row[col] = f"{avg:.2f}" elif func == 'MIN': mn = min(values) if len(values) > 0 else None if isinstance(mn, float): mn = round(mn + 1e-8, 2) mn = f"{mn:.2f}" aggregated_row[col] = mn elif func == 'MAX': mx = max(values) if len(values) > 0 else None if isinstance(mx, float): mx = round(mx + 1e-8, 2) mx = f"{mx:.2f}" aggregated_row[col] = mx processed_rows = [aggregated_row] else: processed_rows = rows # Select columns output_rows = [] headers = [] select_aliases = [] for col in select_cols: if ' AS ' in col.upper(): original, alias = re.split(r'\s+AS\s+', col, flags=re.IGNORECASE) headers.append(alias) select_aliases.append( (original.strip(), alias) ) else: headers.append(col) select_aliases.append( (col, col) ) for row in processed_rows: output_row = [] for original, alias in select_aliases: if '(' in original: # It's an aggregate function value = row.get(original) if isinstance(value, float): value = "{0:.2f}".format(value) elif value is None: value = '' else: value = str(value) output_row.append(str(value)) else: value = row.get(original) if isinstance(value, float): value = "{0:.2f}".format(round(value + 1e-8,2)) elif value is None: value = '' else: value = str(value) output_row.append(str(value)) output_rows.append(output_row) # ORDER BY if 'order_by' in parsed: order_instructions = parsed['order_by'] # Build a sort key function def sort_key(row): key = [] for col, order in order_instructions: try: idx = headers.index(col) except: idx = -1 if idx == -1: key.append(None) else: val = row[idx] # Try to convert to int or float for proper sorting try: val = int(val) except: try: val = float(val) except: pass key.append(val) return key reverse = [True if order=='DESC' else False for _, order in order_instructions] # Since Python's sort doesn't support multiple reverse flags, we'll sort iteratively for i in reversed(range(len(order_instructions))): col, order = order_instructions[i] idx = headers.index(col) is_reverse = True if order == 'DESC' else False output_rows.sort(key=lambda x: try_convert(x[idx]), reverse=is_reverse) # LIMIT if 'limit' in parsed: limit = parsed['limit'] output_rows = output_rows[:limit] # Construct output string output = ','.join(headers) + '\n' row_strings = [] for row in output_rows: row_strings.append( ','.join(row) ) output += '\\n'.join(row_strings) print(output) def try_convert(value): try: return int(value) except: try: return float(value) except: return value if __name__ == "__main__": main()Explanation of the Script:
-
Loading JSON Data:
- The
load_json_filefunction reads a JSON file where each line is a JSON object and returns a list of dictionaries representing the records.
- The
-
Parsing the SQL Query:
- The
parse_sqlfunction uses regular expressions to extract different parts of the SQL query:- SELECT Clause: Identifies the columns to select and their aliases.
- FROM Clause: Determines the primary table and its alias.
- JOIN Clauses: Handles multiple INNER JOINs with their conditions.
- WHERE Clause: Extracts filtering conditions.
- GROUP BY Clause: Identifies columns to group the data.
- ORDER BY Clause: Determines the sorting order of the results.
- LIMIT Clause: Sets the maximum number of records to return.
- The
-
Executing JOINs:
- The
perform_joinfunction performs INNER JOINs between the primary table and the joined tables based on the specified conditions.
- The
-
Applying WHERE Filters:
- The
apply_wherefunction filters the joined dataset based on the WHERE conditions using theevaluate_conditionhelper function.
- The
-
Handling GROUP BY and Aggregations:
- The
apply_group_byfunction groups the data and computes aggregate functions likeCOUNT,SUM,AVG,MIN, andMAX.
- The
-
Selecting and Formatting Columns:
- After grouping (if applicable), the script selects the required columns and applies any necessary formatting (e.g., rounding of float values).
-
Ordering the Results:
- The script sorts the results based on the
ORDER BYclause. It handles multiple sorting keys and supports both ascending and descending orders.
- The script sorts the results based on the
-
Limiting the Output:
- If a
LIMITis specified, the script restricts the output to the desired number of records.
- If a
-
Generating the Output String:
- The script constructs the final output string with comma-separated values and uses
\nas a newline separator between records. The first line contains the column headers.
- The script constructs the final output string with comma-separated values and uses
Usage:
-
Ensure Data Files Are Present:
- Place the JSON data files (
products_public.json,users_public.json,reviews_public.json,transactions_public.json) in the same directory as the script.
- Place the JSON data files (
-
Run the Script:
- You can execute 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 > 10.00 ORDER BY product_id ASC" | python sql_engine.py
- You can execute the script and provide the SQL query via standard input. For example:
-
Sample Output:
- For the above input, the script will output:
product_id,product_name\n1,product1\n2,product2,\n3,product3
- For the above input, the script will output:
Notes:
- The script assumes that the JSON files are properly formatted, with each line representing a valid JSON object.
- The SQL parser is simplistic and tailored specifically to the problem's constraints. It may not handle more complex SQL queries outside the defined syntax.
- Aggregate functions are supported only as specified in the problem statement.
- The script ensures that floating-point numbers are rounded to two decimal places where necessary.
This script provides a foundational SQL query engine capable of handling the specified operations on JSON data. Depending on further requirements or complexities, additional features and error handling can be incorporated.
-