GoJSON2SQL

GoJson2SQL is a library for composing SQL queries using JSON. A JSON file is transformed into an SQL string. This library facilitates the process of generating SQL statements by utilizing a structured JSON format, enhancing the readability and simplicity of SQL query construction.

Limitations

Currently, it can only perform SELECT queries.

Features

Basic select query
Basic selection field
Join Table
Conditional (WHERE Statement)
HAVING
SQL Function
CASE, WHEN and THEN in the selection fields
Subqueries
Parsing Value to Parameters
SQLi Prevention (Experimental)

TODO:

More Queries
Validate SQL Syntax
?

Installation

go get github.com/bonkzero404/gojson2sql

Simple Example

package main
import (
"fmt""github.com/bonkzero404/gojson2sql"
)
funcmain() {
sqlJson:=` { "table": "table_1", "selectFields": [ "a", "b" ], "conditions": [ { "datatype": "number", "clause": "a", "operator": "=", "value": 1 } ], "limit": 1 } `jql, err:=gojson2sql.NewJson2Sql([]byte(sqlJson), &gojson2sql.Json2SqlConf{})
iferr!=nil {
panic(err)
}
sql, param, _:=jql.Generate()
fmt.Println("SQL:", sql)
fmt.Println("Param:", param)
}

Output:

SQL: SELECT a, b FROM table_1 WHERE a = ? LIMIT
Param: [1]

You can use this raw SQL with either the sql package or GORM. Here's an example with sql package:

jql, _:=gojson2sql.NewJson2Sql([]byte(sqlJson), &gojson2sql.Json2SqlConf{})
sql, param, _:=jql.Generate()
db.Query(sql, param)

Example Union Query Operation

[
{
"table": "table_1",
"selectFields": ["a", "b"],
"conditions": [
{
"datatype": "number",
"clause": "a",
"operator": "=",
"value": 1
}
],
"limit": 1
},
{
"table": "table_2",
"selectFields": ["a", "b"],
"conditions": [
{
"datatype": "number",
"clause": "a",
"operator": "=",
"value": 1
}
],
"limit": 1
}
]

If you are using UNION, you must set the withUnion parameter in Json2SqlConf. Here is an example:

jql, _:=gojson2sql.NewJson2Sql([]byte(sqlJson), &gojson2sql.Json2SqlConf{
withUnion: true,
})
sql, param, _:=jql.Generate()
db.Query(sql, param)

Output:

SELECT a, b FROM table_1 WHERE a =1LIMIT1UNIONSELECT a, b FROM table_2 WHERE a =1LIMIT1

You can see the difference between a union query and a standard select. In a union, you must use a JSON array with the standard JSON format as before.

Config Parameters

withUnionboolwithSanitizedInjectionbool

withUnion: It is used to set the query to union and the structure must be of array type.

withSanitizedInjection: This is an experimental feature, it is far from perfect, and it serves to validate SQL strings against SQL Injection.

Operator Lists

const (
EqualSQLOperatorEnum="="NotEqualSQLOperatorEnum="<>"LessThanSQLOperatorEnum="<"LessEqualSQLOperatorEnum="<="GreaterThanSQLOperatorEnum=">"GreaterEqualSQLOperatorEnum=">="LikeSQLOperatorEnum="LIKE"IlikeSQLOperatorEnum="ILIKE"BetweenSQLOperatorEnum="BETWEEN"NotLikeSQLOperatorEnum="NOT LIKE"InSQLOperatorEnum="IN"NotInSQLOperatorEnum="NOT IN"IsNullSQLOperatorEnum="IS NULL"IsNotNullSQLOperatorEnum="IS NOT NULL"
)

Datatype Lists

const (
BooleanSQLDataTypeEnum="BOOLEAN"StringSQLDataTypeEnum="STRING"NumberSQLDataTypeEnum="NUMBER"RawSQLDataTypeEnum="RAW"FunctionSQLDataTypeEnum="FUNCTION"ArraySQLDataTypeEnum="ARRAY"
)

isStatic / isField Properties

isStatic: (boolean) isStatic is used to set the value of the clause. If true, the value will not be parsed to parameters. However, if false, the opposite will occur. This is typically used in where statements.
isField: (boolean) isField is used to set a field so that it does not use single quotes. For example, if you describe a function and do not use isField, it will look like this: COUNT('field'). However, if you use isField, it will look like this: COUNT(field).

JSON Format

In general, the structure of the JSON format used is as follows:

table: Used to describe the table name, e.g. table_name (string)
selectFields: Used to select fields from a table, this property uses the Array type, you can combine Array of String, and Array of Json, the example is as follows:
```
{
"selectFields": [
"table_1.a",
{
"field": "table_1.b",
"alias": "foo_bar"
},
{
"field": "table_2.a",
"alias": "baz",
"subquery": {
"table": "table_4",
"selectFields": ["*"],
"conditions": [
{
"datatype": "number",
"clause": "a",
"operator": "=",
"value": 1
}
],
"limit": 1
}
},
"table_2.b",
"table_3.a",
"table_3.b"
]
}
```
There you can see there is a subquery, you can use a subquery with the same format as its parent, you can also describe a field with an alias in the selection field.
NOTE: If you are using a subquery, you don't need to describe the datatype property

join: You can use join to combine multiple tables, an example is as follows:

{
"join": [
{
"table": "table_2",
"type": "join",
"on": {
"table_2.a": "table_1.a"
}
},
{
"table": "table_3",
"type": "left",
"on": {
"table_3.a": "table_2.a"
}
}
]
}

conditions: Conditions are used for SQL Where clauses. The structure of these conditions is dynamic; you can use a function, subquery, or composite. Consider the following example:

{
"conditions": [
{
"datatype": "string",
"clause": "table_1.a",
"operator": "=",
"value": "foo"
},
{
"operand": "and",
"datatype": "boolean",
"clause": "table_1.b",
"operator": "=",
"value": true
},
{
"operand": "and",
"datatype": "function",
"clause": "table_2.a",
"operator": ">",
"value": {
"sqlFunc": {
"name": "sum",
"params": [100]
}
}
},
{
"operand": "and",
"clause": "table_2.b",
"operator": "=",
"value": {
"subquery": {
"table": "table_4",
"selectFields": ["*"],
"conditions": [
{
"datatype": "number",
"clause": "a",
"operator": "=",
"value": 1
}
],
"limit": 1
}
}
},
{
"operand": "or",
"composite": [
{
"clause": "table_3.a",
"datatype": "string",
"operator": "between",
"value": {
"from": "2020-01-01",
"to": "2023-01-01"
}
},
{
"operand": "and",
"datatype": "string",
"clause": "table_3.b",
"operator": "=",
"value": "2"
}
]
}
]
}

groupBy:

{
"groupBy": {
"fields": ["table_1.a"]
}
}

having

{
"having": [
{
"clause": {
"sqlFunc": {
"name": "count",
"isField": true,
"params": ["table_2.a"]
}
},
"datatype": "number",
"operator": ">",
"value": 10
}
]
}

orderBy, limit, offset:

{
"orderBy": {
"fields": ["table_1.a", "table_2.a"],
"sort": "asc"
},
"limit": 1,
"offset": 0
}

Or you can describe the limit and offset like this

{
"offset": {
"isStatic": true,
"value": 10
},
"offset": {
"value": 10
}
}

Convert to Raw Query

You can also convert to raw query without parameters.

jql, err:=gojson2sql.NewJson2Sql([]byte(sqlJson), &gojson2sql.Json2SqlConf{})
iferr!=nil {
panic(err)
}
sql:=jql.Build()
fmt.Println("SQL:", sql)

Output:

SQL: SELECT a, b FROM table_1 WHERE a =1LIMIT1

Full Example Advance Query

sqlJson:=` { "table": "table_1", "selectFields": [ "table_1.a", { "field": "table_1.b", "alias": "foo_bar" }, { "field": "table_2.a", "alias": "baz", "subquery": { "table": "table_4", "selectFields": ["*"], "conditions": [ { "datatype": "number", "clause": "a", "operator": "=", "value": 1 } ], "limit": 1 } }, "table_2.b", "table_3.a", "table_3.b" ], "join": [ { "table": "table_2", "type": "join", "on": { "table_2.a": "table_1.a" } }, { "table": "table_3", "type": "left", "on": { "table_3.a": "table_2.a" } } ], "conditions": [ { "datatype": "string", "clause": "table_1.a", "operator": "=", "value": "foo" }, { "operand": "and", "datatype": "boolean", "clause": "table_1.b", "operator": "=", "value": true }, { "operand": "and", "datatype": "function", "clause": "table_2.a", "operator": ">", "value": { "sqlFunc": { "name": "sum", "params": [100] } } }, { "operand": "and", "clause": "table_2.b", "operator": "=", "value": { "subquery": { "table": "table_4", "selectFields": ["*"], "conditions": [ { "datatype": "number", "clause": "a", "operator": "=", "value": 1 } ], "limit": 1 } } }, { "operand": "or", "composite": [ { "clause": "table_3.a", "datatype": "string", "operator": "between", "value": { "from": "2020-01-01", "to": "2023-01-01" } }, { "operand": "and", "datatype": "string", "clause": "table_3.b", "operator": "=", "value": "2" } ] } ], "groupBy": { "fields": ["table_1.a"] }, "having": [ { "clause": { "sqlFunc": { "name": "count", "isField": true, "params": ["table_2.a"] } }, "datatype": "number", "operator": ">", "value": 10 } ], "orderBy": { "fields": ["table_1.a", "table_2.a"], "sort": "asc" }, "limit": 1, "offset": 0 }`jql, err:=gojson2sql.NewJson2Sql([]byte(sqlJson), &gojson2sql.Json2SqlConf{})
iferr!=nil {
panic(err)
}
sql, param, _:=jql.Generate()
fmt.Println("SQL:", sql)
fmt.Println("Param:", param)

output:

SQL:
SELECTtable_1.a,
table_1.bAS foo_bar,
(SELECT*FROM table_4 WHERE a = ? LIMIT1) AS baz,
table_2.b,
table_3.a,
table_3.bFROM table_1
JOIN table_2 ONtable_2.a=table_1.aLEFT JOIN table_3 ONtable_3.a=table_2.aWHEREtable_1.a= ? ANDtable_1.b= ? ANDtable_2.a>sum(?) ANDtable_2.b= (SELECT*FROM table_4 WHERE a = ? LIMIT1) OR
(table_3.a BETWEEN ? AND ? ANDtable_3.b= ?)
GROUP BYtable_1.aHAVINGCOUNT(table_2.a) > ?
ORDER BYtable_1.a, table_2.aASCLIMIT1
OFFSET 0
Param:
[1 foo true 10012020-01-012023-01-01210]

Complete Example JSON

{
"table": "table_1",
"selectFields": [
{
"field": "table_1.a",
"alias": "foo_bar"
},
{
"alias": "foo_bar_baz",
"addFunction": {
"sqlFunc": {
"name": "count",
"isField": true,
"params": ["table_1.b"]
}
}
},
{
"field": "table_2.a",
"alias": "baz",
"subquery": {
"table": "table_4",
"selectFields": ["*"],
"conditions": [
{
"datatype": "number",
"clause": "a",
"operator": "=",
"value": 1
}
],
"limit": 1
}
},
{
"when": [
{
"clause": "table_2.b",
"datatype": "number",
"isStatic": true,
"operator": ">",
"value": 100,
"expectation": {
"datatype": "BOOLEAN",
"isStatic": true,
"value": true
}
}
],
"defaultValue": {
"isStatic": true,
"value": {
"subquery": {
"table": "table_3",
"selectFields": ["*"],
"conditions": [
{
"datatype": "NUMBER",
"clause": "a",
"operator": "=",
"value": 1
}
],
"limit": 1
}
}
},
"alias": "field_alias"
},
"table_3.a",
"table_3.b"
],
"join": [
{
"table": "table_2",
"type": "join",
"on": {
"table_2.a": "table_1.a"
}
},
{
"table": "table_3",
"type": "left",
"on": {
"table_3.a": "table_2.a"
}
}
],
"conditions": [
{
"datatype": "string",
"clause": "table_1.a",
"operator": "=",
"value": "foo"
},
{
"operand": "and",
"datatype": "boolean",
"clause": "table_1.b",
"operator": "=",
"value": true
},
{
"operand": "and",
"datatype": "function",
"clause": "table_2.a",
"operator": ">",
"value": {
"sqlFunc": {
"name": "sum",
"params": [100]
}
}
},
{
"operand": "and",
"clause": "table_2.b",
"operator": "=",
"value": {
"subquery": {
"table": "table_4",
"selectFields": ["*"],
"conditions": [
{
"datatype": "number",
"clause": "a",
"operator": "=",
"value": 1
}
],
"limit": 1
}
}
},
{
"operand": "or",
"composite": [
{
"clause": "table_3.a",
"datatype": "string",
"operator": "between",
"value": {
"from": "2020-01-01",
"to": "2023-01-01"
}
},
{
"operand": "and",
"datatype": "string",
"clause": "table_3.b",
"operator": "=",
"value": "2"
}
]
}
],
"groupBy": {
"fields": ["table_1.a"]
},
"having": [
{
"clause": {
"sqlFunc": {
"name": "count",
"isField": true,
"params": ["table_2.a"]
}
},
"datatype": "number",
"operator": ">",
"value": 10
}
],
"orderBy": {
"fields": ["table_1.a", "table_2.a"],
"sort": "asc"
},
"limit": {
"isStatic": true,
"value": 10
},
"offset": 0
}

output:

SELECTtable_1.aAS foo_bar,
COUNT(table_1.b) AS foo_bar_baz,
(SELECT*FROM table_4 WHERE a =1LIMIT1) AS baz,
CASE
WHEN table_2.b>100 THEN true
ELSE (SELECT*FROM table_3 WHERE a =1LIMIT1)
END AS field_alias,
table_3.a,
table_3.bFROM table_1
JOIN table_2 ONtable_2.a=table_1.aLEFT JOIN table_3 ONtable_3.a=table_2.aWHEREtable_1.a='foo'ANDtable_1.b= true ANDtable_2.a>sum(100) ANDtable_2.b= (SELECT*FROM table_4 WHERE a =1LIMIT1) OR
(table_3.a BETWEEN '2020-01-01'AND'2023-01-01'ANDtable_3.b='2')
GROUP BYtable_1.aHAVINGCOUNT(table_2.a) >10ORDER BYtable_1.a, table_2.aASCLIMIT10
OFFSET 0

Testing

>gotest-v-cover ./...===RUNTestConstructor---PASS: TestConstructor (0.00s)
===RUNTestConstructor_Fail---PASS: TestConstructor_Fail (0.00s)
===RUNTestConstructor_Fail_Union---PASS: TestConstructor_Fail_Union (0.00s)
===RUNTestRawJson_OK---PASS: TestRawJson_OK (0.00s)
===RUNTestRawJson_Error---PASS: TestRawJson_Error (0.00s)
===RUNTestMaskedQueryValue---PASS: TestMaskedQueryValue (0.00s)
===RUNTestGenerateSelectFrom---PASS: TestGenerateSelectFrom (0.00s)
===RUNTestGenerateSelectFrom_Selection---PASS: TestGenerateSelectFrom_Selection (0.00s)
===RUNTestGenerateSelectFrom_CaseWhenThen---PASS: TestGenerateSelectFrom_CaseWhenThen (0.00s)
===RUNTestGenerateSelectFrom_CaseDefaultValueSub---PASS: TestGenerateSelectFrom_CaseDefaultValueSub (0.00s)
===RUNTestGenerateSelectFrom_SqlFunc---PASS: TestGenerateSelectFrom_SqlFunc (0.00s)
===RUNTestSqlLikeAndBlankDatatype---PASS: TestSqlLikeAndBlankDatatype (0.00s)
===RUNTestSqlLikeWithOperand---PASS: TestSqlLikeWithOperand (0.00s)
===RUNTestBetweenWithOperand---PASS: TestBetweenWithOperand (0.00s)
===RUNTestCompositeWithoutOperand---PASS: TestCompositeWithoutOperand (0.00s)
===RUNTestGenerateOrderBy---PASS: TestGenerateOrderBy (0.00s)
===RUNTestGenerateOrderBy_WithSort---PASS: TestGenerateOrderBy_WithSort (0.00s)
===RUNTestGenerateGroupBy---PASS: TestGenerateGroupBy (0.00s)
===RUNTestGenerateJoin_JOIN---PASS: TestGenerateJoin_JOIN (0.00s)
===RUNTestGenerateJoin_INNER_JOIN---PASS: TestGenerateJoin_INNER_JOIN (0.00s)
===RUNTestGenerateJoin_LEFT_JOIN---PASS: TestGenerateJoin_LEFT_JOIN (0.00s)
===RUNTestGenerateJoin_RIGHT_JOIN---PASS: TestGenerateJoin_RIGHT_JOIN (0.00s)
===RUNTestGenerateHaving---PASS: TestGenerateHaving (0.00s)
===RUNTestGenerateWhere---PASS: TestGenerateWhere (0.00s)
===RUNTestGenerateConditions---PASS: TestGenerateConditions (0.00s)
===RUNTestGenerateConditions_SubQuery---PASS: TestGenerateConditions_SubQuery (0.00s)
===RUNTestLimit_Static---PASS: TestLimit_Static (0.00s)
===RUNTestLimit_ToParam---PASS: TestLimit_ToParam (0.00s)
===RUNTestOffset_Static---PASS: TestOffset_Static (0.00s)
===RUNTestOffset_ToParam---PASS: TestOffset_ToParam (0.00s)
===RUNTestBuildJsonToSql---PASS: TestBuildJsonToSql (0.00s)
===RUNTestGenerateJsonToSql---PASS: TestGenerateJsonToSql (0.00s)
===RUNTestBuildRawUnion---PASS: TestBuildRawUnion (0.00s)
===RUNTestGenerateUnion---PASS: TestGenerateUnion (0.00s)
===RUNTestGenerateBuild_PreventInjection---PASS: TestGenerateBuild_PreventInjection (0.00s)
===RUNTestGenerate_PreventInjection---PASS: TestGenerate_PreventInjection (0.00s)
===RUNTestGenerateBuildUnion_PreventInjection---PASS: TestGenerateBuildUnion_PreventInjection (0.00s)
===RUNTestGenerateUnion_PreventInjection---PASS: TestGenerateUnion_PreventInjection (0.00s)
===RUNTestIsValidDataType---PASS: TestIsValidDataType (0.00s)
===RUNTestGetValueFromDataType---PASS: TestGetValueFromDataType (0.00s)
===RUNTestCheckArrayType---PASS: TestCheckArrayType (0.00s)
===RUNTestArrayConversionToStringExpression---PASS: TestArrayConversionToStringExpression (0.00s)
===RUNTestExtractValueByDataType---PASS: TestExtractValueByDataType (0.00s)
===RUNTestGetSqlExpression---PASS: TestGetSqlExpression (0.00s)
===RUNTestIsValidOperator---PASS: TestIsValidOperator (0.00s)
===RUNTestGetValueFromOperator---PASS: TestGetValueFromOperator (0.00s)
PASS
coverage: 100.0%ofstatements

Benchmarking

Specs:

MacBook Pro M1 (2020)
8-Cores (arm64)
8GB of RAM

>gotest-bench=. -benchmem
goos: darwin
goarch: arm64
pkg: github.com/bonkzero404/gojson2sqlBenchmarkJson2Sql_BuildRaw-82939039416ns/op29289B/op507allocs/opBenchmarkJson2Sql_Generate-81757567617ns/op41164B/op607allocs/opBenchmarkJson2Sql_Union_BuildRaw-81378986378ns/op65804B/op1016allocs/opBenchmarkJson2Sql_Union_Generate-88659131424ns/op76072B/op1143allocs/opBenchmarkJson2Sql_BuildRaw_WithSanitizedSQLi-88269146946ns/op49644B/op627allocs/opBenchmarkJson2Sql_Generate_WithSanitizedSQLi-85640211519ns/op61368B/op727allocs/opBenchmarkJson2Sql_Union_BuildRaw_WithSanitizedSQLi-84190282480ns/op87426B/op1137allocs/opBenchmarkJson2Sql_Union_Generate_WithSanitizedSQLi-82959401434ns/op97889B/op1263allocs/op

Name		Name	Last commit message	Last commit date
.github/workflows		.github/workflows
.editorconfig		.editorconfig
.gitignore		.gitignore
README.md		README.md
benchmark_test.go		benchmark_test.go
coverage.txt		coverage.txt
go.mod		go.mod
go.sum		go.sum
jql.go		jql.go
jql_test.go		jql_test.go
sql_datatype.go		sql_datatype.go
sql_datatype_enum.go		sql_datatype_enum.go
sql_datatype_test.go		sql_datatype_test.go
sql_expression.go		sql_expression.go
sql_expression_test.go		sql_expression_test.go
sql_operator.go		sql_operator.go
sql_operator_enum.go		sql_operator_enum.go
sql_operator_test.go		sql_operator_test.go
sql_struct.go		sql_struct.go

Navigation Menu

Repository files navigation

GoJSON2SQL

Limitations

Features

TODO:

Installation

Simple Example

Example Union Query Operation

Config Parameters

Operator Lists

Datatype Lists

isStatic / isField Properties

JSON Format

Convert to Raw Query

Full Example Advance Query

Complete Example JSON

Testing

Benchmarking

About

Releases

Packages

Used by

Contributors

Languages

Latest commit

History

Folders and files

Repository files navigation

GoJSON2SQL

Limitations

Features

TODO:

Installation

Simple Example

Example Union Query Operation

Config Parameters

Operator Lists

Datatype Lists

isStatic / isField Properties

JSON Format

Convert to Raw Query

Full Example Advance Query

Complete Example JSON

Testing

Benchmarking

About

Topics

Resources

Stars

Watchers

Forks

Releases

Packages

Used by

Contributors

Languages