JSON to SQL Query

This library converts a JSON object into a SQL WHERE clause.

Features

• Converts JSON to SQL.
• Supports nested conditions.
• Supports multiple database dialects (MySQL and PostgreSQL).

Installation

go get github.com/xbsoftware/querysql@v2.0.0

V2 API

GetSQL

funcGetSQL(dataFilter, config*SQLConfig, dbArr...DBDriver) (string, []interface{}, error)

• data: The Filter object, which can be created from JSON using FromJSON.
• config: An optional SQLConfig for advanced configuration.
• dbArr: An optional DBDriver for database-specific SQL generation. Defaults to MySQL{}.

Filter Struct

The Filter struct is the main data structure for building queries.

typeFilterstruct {
Gluestring`json:"glue"`Fieldstring`json:"field"`Typestring`json:"type"`Predicatestring`json:"predicate"`Filterstring`json:"filter"`Valueinterface{} `json:"value"`Includes []interface{} `json:"includes"`Rules []Filter`json:"rules"`
}

SQLConfig

The SQLConfig struct allows you to customize the behavior of GetSQL.

typeSQLConfigstruct {
WhitelistFuncCheckFunctionWhitelistmap[string]boolOperationsmap[string]CustomOperationPredicatesmap[string]CustomPredicate
}

• Whitelist and WhitelistFunc: Restrict which fields can be used in the query.
• Operations: Define custom operations.
• Predicates: Define custom predicates.

CustomPredicate

A CustomPredicate allows you to modify the field name in the SQL query, for example, by wrapping it in a function call.

Example:

You can define a custom predicate to extract the year from a date field.

jsonString:=`{ "field": "created_at", "predicate": "year", "filter": "equal", "value": 2024	}`filter, _:=querysql.FromJSON([]byte(jsonString))
config:=&querysql.SQLConfig{
Predicates: map[string]querysql.CustomPredicate{
"year": func(nstring, pstring) (string, error) {
returnfmt.Sprintf("YEAR(%s)", n), nil
},
},
}
sql, values, _:=querysql.GetSQL(filter, config)

CustomOperation

A CustomOperation allows you to define a new, custom filter operation.

Example:

You can define a custom is_empty operation to check for empty strings.

jsonString:=`{ "field": "name", "filter": "is_empty"	}`filter, _:=querysql.FromJSON([]byte(jsonString))
config:=&querysql.SQLConfig{
Operations: map[string]querysql.CustomOperation{
"is_empty": func(fieldstring, filterstring, values []interface{}) (string, []interface{}, error) {
returnfmt.Sprintf("%s = ''", field), []interface{}{}, nil
},
},
}
sql, values, _:=querysql.GetSQL(filter, config)

Usage

Here is a basic example of how to use the library:

package main
import (
"fmt""github.com/xbsoftware/querysql"
)
funcmain() {
jsonString:=`{ "glue": "and", "rules": [{ "field": "age", "filter": "less", "value": 42 }, { "field": "region", "includes": [1, 2, 6] }] }`filter, err:=querysql.FromJSON([]byte(jsonString))
iferr!=nil {
panic(err)
}
// Using the default MySQL driversql, values, err:=querysql.GetSQL(filter, nil)
iferr!=nil {
panic(err)
}
fmt.Println(sql) // ( age < ? AND region IN(?,?,?) )fmt.Println(values) // [42 1 2 6]// Using the PostgreSQL driversql, values, err=querysql.GetSQL(filter, nil, &querysql.PostgreSQL{})
iferr!=nil {
panic(err)
}
fmt.Println(sql) // ( age < $1 AND region IN($2,$3,$4) )fmt.Println(values) // [42 1 2 6]
}

Migrating from V1 to V2

The V2 release introduces several breaking changes. Here's how to migrate your code.

Filter Struct Changes

The Filter struct has been redesigned.

V1:

typeFilterstruct {
Gluestring`json:"glue"`Fieldstring`json:"field"`ConditionCondition`json:"condition"`Includes []interface{} `json:"includes"`Kids []Filter`json:"rules"`
}

V2:

typeFilterstruct {
Gluestring`json:"glue"`Fieldstring`json:"field"`Typestring`json:"type"`Predicatestring`json:"predicate"`Filterstring`json:"filter"`Valueinterface{} `json:"value"`Includes []interface{} `json:"includes"`Rules []Filter`json:"rules"`
}

• The Condition field has been removed. Its functionality is now handled by the Type, Predicate, Filter, and Value fields.
• The Kids field has been renamed to Rules.

CustomPredicate Signature Change

The CustomPredicate function signature has been simplified.

V1:

typeCustomPredicatefunc(fieldNamestring, predicateNamestring, values []interface{}) (string, []interface{}, error)

V2:

typeCustomPredicatefunc(fieldNamestring, predicateNamestring) (string, error)

The values parameter and return value have been removed.

SQLConfig Changes

The SQLConfig struct now includes a Predicates map to support custom predicates.

V1:

typeSQLConfigstruct {
WhitelistFuncCheckFunctionWhitelistmap[string]boolOperationsmap[string]CustomOperation
}

V2:

typeSQLConfigstruct {
WhitelistFuncCheckFunctionWhitelistmap[string]boolOperationsmap[string]CustomOperationPredicatesmap[string]CustomPredicate
}

Supported operations

• equal
• notEqual
• contains
• notContains
• lessOrEqual
• greaterOrEqual
• less
• notBetween
• between
• greater
• beginsWith
• notBeginsWith
• endsWith
• notEndsWith

Nesting

Blocks can be nested as follows:

{
"glue": "and",
"rules": [
ruleA,
{
"glue": "or",
"rules": [
ruleC,ruleD
]
}
]
}

between / notBetween

For these operations, both start and end values can be provided.

{
"field": "age",
"filter": "between",
"value": { "start": 10, "end": 99 }
}

If only start or end is provided, the operation will change to less or greater automatically.