Skip to content

Instantly share code, notes, and snippets.

@connor-davis

connor-davis/json_to_sql.rs

Last active August 14, 2024 09:36
Show Gist options
  • Save connor-davis/abf7bce918a6baa1dd863b607c6a67f1 to your computer and use it in GitHub Desktop.
Save connor-davis/abf7bce918a6baa1dd863b607c6a67f1 to your computer and use it in GitHub Desktop.
Download ZIP
Takes a specifically defined json object and converts it to a sql query.
Raw
json_to_sql.rs
use anyhow::Error;
use serde_json::{json, Value};
use crate::utils::merge_json;
pub async fn report_to_sql(report: Value) -> Result<String, Error> {
let table = report["table"].as_object();
if table.is_none() {
return Err(Error::msg("Table not found."));
}
let table = table.unwrap();
let mut query_string = String::from("SELECT");
let columns = report["columns"].as_array();
let references = report["references"].as_array();
let filters = report["filters"].as_array();
let sorts = report["sorts"].as_array();
match columns {
Some(columns) => {
for column_idx in 0..columns.len() {
let column = columns.get(column_idx);
match column {
Some(column) => {
query_string = format!(
"{}\n\tt1.{} AS '{}',",
query_string,
column["column_name"].as_str().unwrap_or("-"),
column["custom_column_name"].as_str().unwrap_or(
format!("t1{}", column["column_name"].as_str().unwrap_or("-"))
.as_str()
)
);
}
None => {}
}
}
}
None => {}
};
match references {
Some(references) => {
for reference_idx in 0..references.len() {
let reference = references.get(reference_idx);
match reference {
Some(reference) => {
let columns = reference["referenced_columns"].as_array();
match columns {
Some(columns) => {
for column_idx in 0..columns.len() {
let column = columns.get(column_idx);
match column {
Some(column) => {
query_string = format!(
"{}\n\tt{}.{} AS '{}',",
query_string,
reference_idx + 2,
column["column_name"].as_str().unwrap_or("-"),
column["custom_column_name"].as_str().unwrap_or(
format!(
"t1{}",
column["column_name"]
.as_str()
.unwrap_or("-")
)
.as_str()
)
);
}
None => {}
}
}
}
None => {}
};
}
None => {}
}
}
}
None => {}
}
query_string = format!(
"{}\nFROM {} t1",
query_string.strip_suffix(",").unwrap_or("-"),
table["table_name"].as_str().unwrap_or("-")
);
match references {
Some(references) => {
for reference_idx in 0..references.len() {
let reference = references.get(reference_idx);
match reference {
Some(reference) => {
query_string = format!(
"{}\nLEFT JOIN {} t{} ON t{}.{} = t1.{}",
query_string,
reference["referenced_table"].as_str().unwrap_or("-"),
reference_idx + 2,
reference_idx + 2,
reference["referenced_table_column"].as_str().unwrap_or("-"),
reference["primary_table_column"].as_str().unwrap_or("-")
);
}
None => {}
}
}
}
None => {}
}
let mut primary_filters: Vec<Value> = Vec::new();
let mut reference_filters: Vec<Value> = Vec::new();
match filters {
Some(filters) => {
if filters.len() > 0 {
primary_filters.extend(filters.to_owned());
query_string = format!("{}\nWHERE", query_string);
for filter_idx in 0..filters.len() {
let filter = filters.get(filter_idx);
match filter {
Some(filter) => {
if filter_idx < filters.len() && filter_idx > 0 {
query_string = format!("{},\n\tAND", query_string);
}
let filter_type = filter["filter_type"].as_str().unwrap_or("-");
match filter_type {
"sw" => {
query_string = format!(
"{}\n\tt1.{} LIKE '{}%'",
query_string,
filter["column_name"].as_str().unwrap_or("-"),
filter["filter_value"].as_str().unwrap_or("-")
);
}
"ew" => {
query_string = format!(
"{}\n\tt1.{} LIKE '%{}'",
query_string,
filter["column_name"].as_str().unwrap_or("-"),
filter["filter_value"].as_str().unwrap_or("-")
);
}
"incl" => {
query_string = format!(
"{}\n\tt1.{} LIKE '%{}%'",
query_string,
filter["column_name"].as_str().unwrap_or("-"),
filter["filter_value"].as_str().unwrap_or("-")
);
}
"eq" => {
query_string = format!(
"{}\n\tt1.{} = '{}'",
query_string,
filter["column_name"].as_str().unwrap_or("-"),
filter["filter_value"].as_str().unwrap_or("-")
);
}
"gt" => {
query_string = format!(
"{}\n\tt1.{} > '{}'",
query_string,
filter["column_name"].as_str().unwrap_or("-"),
filter["filter_value"].as_str().unwrap_or("-")
);
}
"gte" => {
query_string = format!(
"{}\n\tt1.{} >= '{}'",
query_string,
filter["column_name"].as_str().unwrap_or("-"),
filter["filter_value"].as_str().unwrap_or("-")
);
}
"lt" => {
query_string = format!(
"{}\n\tt1.{} < '{}'",
query_string,
filter["column_name"].as_str().unwrap_or("-"),
filter["filter_value"].as_str().unwrap_or("-")
);
}
"lte" => {
query_string = format!(
"{}\n\tt1.{} <= '{}'",
query_string,
filter["column_name"].as_str().unwrap_or("-"),
filter["filter_value"].as_str().unwrap_or("-")
);
}
_ => {}
}
}
None => {}
}
}
}
}
None => {}
}
match references {
Some(references) => {
for reference_idx in 0..references.len() {
let reference = references.get(reference_idx);
match reference {
Some(reference) => {
let filters = reference["referenced_filters"].as_array();
match filters {
Some(filters) => {
for filter_idx in 0..filters.len() {
let filter = filters.get(filter_idx);
match filter {
Some(filter) => {
let mut old_filter = filter.clone();
merge_json(
&mut old_filter,
&json!({ "reference_table_number": reference_idx + 2 }),
);
reference_filters.push(old_filter.to_owned());
}
None => {}
}
}
}
None => {}
}
}
None => {}
}
}
}
None => {}
}
if primary_filters.len() == 0 && reference_filters.len() > 0 {
query_string = format!("{}\nWHERE", query_string);
}
if primary_filters.len() > 0 && reference_filters.len() > 0 {
query_string = format!("{}\n\tAND", query_string);
}
for filter_idx in 0..reference_filters.len() {
let filter = reference_filters.get(filter_idx);
match filter {
Some(filter) => {
if filter_idx < reference_filters.len() && filter_idx > 0 {
query_string = format!("{},\n\tAND", query_string);
}
let reference_table_number =
filter["reference_table_number"].as_i64().unwrap_or(1);
let filter_type = filter["filter_type"].as_str().unwrap_or("-");
match filter_type {
"sw" => {
query_string = format!(
"{}\n\tt{}.{} LIKE '{}%'",
query_string,
reference_table_number,
filter["column_name"].as_str().unwrap_or("-"),
filter["filter_value"].as_str().unwrap_or("-")
);
}
"ew" => {
query_string = format!(
"{}\n\tt{}.{} LIKE '{}%'",
query_string,
reference_table_number,
filter["column_name"].as_str().unwrap_or("-"),
filter["filter_value"].as_str().unwrap_or("-")
);
}
"incl" => {
query_string = format!(
"{}\n\tt{}.{} LIKE '{}%'",
query_string,
reference_table_number,
filter["column_name"].as_str().unwrap_or("-"),
filter["filter_value"].as_str().unwrap_or("-")
);
}
"eq" => {
query_string = format!(
"{}\n\tt{}.{} LIKE '{}%'",
query_string,
reference_table_number,
filter["column_name"].as_str().unwrap_or("-"),
filter["filter_value"].as_str().unwrap_or("-")
);
}
"gt" => {
query_string = format!(
"{}\n\tt{}.{} LIKE '{}%'",
query_string,
reference_table_number,
filter["column_name"].as_str().unwrap_or("-"),
filter["filter_value"].as_str().unwrap_or("-")
);
}
"gte" => {
query_string = format!(
"{}\n\tt{}.{} LIKE '{}%'",
query_string,
reference_table_number,
filter["column_name"].as_str().unwrap_or("-"),
filter["filter_value"].as_str().unwrap_or("-")
);
}
"lt" => {
query_string = format!(
"{}\n\tt{}.{} LIKE '{}%'",
query_string,
reference_table_number,
filter["column_name"].as_str().unwrap_or("-"),
filter["filter_value"].as_str().unwrap_or("-")
);
}
"lte" => {
query_string = format!(
"{}\n\tt{}.{} LIKE '{}%'",
query_string,
reference_table_number,
filter["column_name"].as_str().unwrap_or("-"),
filter["filter_value"].as_str().unwrap_or("-")
);
}
_ => {}
}
}
None => {}
}
}
let mut primary_sorts: Vec<Value> = Vec::new();
let mut reference_sorts: Vec<Value> = Vec::new();
match sorts {
Some(sorts) => {
if sorts.len() > 0 {
primary_sorts.extend(sorts.to_owned());
query_string = format!("{}\nORDER BY", query_string);
for sort_idx in 0..sorts.len() {
let sort = sorts.get(sort_idx);
match sort {
Some(sort) => {
if sort_idx < sorts.len() && sort_idx > 0 {
query_string = format!("{},", query_string);
}
let sort_type = sort["sort_type"].as_str().unwrap_or("-");
match sort_type {
"asc" => {
query_string = format!(
"{}\n\tt1.{} ASC",
query_string,
sort["column_name"].as_str().unwrap_or("-"),
);
}
"desc" => {
query_string = format!(
"{}\n\tt1.{} DESC",
query_string,
sort["column_name"].as_str().unwrap_or("-"),
);
}
_ => {}
}
}
None => {}
}
}
}
}
None => {}
}
match references {
Some(references) => {
for reference_idx in 0..references.len() {
let reference = references.get(reference_idx);
match reference {
Some(reference) => {
let sorts = reference["referenced_sorts"].as_array();
match sorts {
Some(sorts) => {
for sort_idx in 0..sorts.len() {
let sort = sorts.get(sort_idx);
match sort {
Some(sort) => {
let mut old_sort = sort.clone();
merge_json(
&mut old_sort,
&json!({ "reference_table_number": reference_idx + 2 }),
);
reference_sorts.push(old_sort);
}
None => {}
}
}
}
None => {}
}
}
None => {}
}
}
}
None => {}
}
if primary_sorts.len() == 0 && reference_sorts.len() > 0 {
query_string = format!("{}\nORDER BY", query_string);
}
if primary_sorts.len() > 0 && reference_sorts.len() > 0 {
query_string = format!("{},", query_string);
}
for sort_idx in 0..reference_sorts.len() {
let sort = reference_sorts.get(sort_idx);
match sort {
Some(sort) => {
if sort_idx < reference_sorts.len() && sort_idx > 0 {
query_string = format!("{},", query_string);
}
let reference_table_number = sort["reference_table_number"].as_i64().unwrap_or(1);
let sort_type = sort["sort_type"].as_str().unwrap_or("-");
match sort_type {
"asc" => {
query_string = format!(
"{}\n\tt{}.{} ASC",
query_string,
reference_table_number,
sort["column_name"].as_str().unwrap_or("-"),
);
}
"desc" => {
query_string = format!(
"{}\n\tt{}.{} DESC",
query_string,
reference_table_number,
sort["column_name"].as_str().unwrap_or("-"),
);
}
_ => {}
}
}
None => {}
}
}
Ok(query_string)
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment