public SqlBuilder Filter(string column, JsonParameter json, Func <string, string> map = null)
{
if (json == null)
{
return(this);
}
if (json.Eq != null)
{
foreach (var(path, value) in json.Eq)
{
AppendFilter($@"""{column}"" @> {Param(JsonPath.Merge(path, value))}::jsonb");
if (path.Any(x => x.Type == JsonPathType.Index))
{
AppendFilter($@"""{column}"" #> {Param(JsonPath.Select(path))} = {Param(value)}::jsonb");
}
}
}
if (json.Ne != null)
{
foreach (var(path, value) in json.Ne)
{
AppendFilter(path.Any(x => x.Type == JsonPathType.Any)
? $@"NOT (""{column}"" @> {Param(JsonPath.Merge(path, value))}::jsonb)"
: $@"NOT (""{column}"" #> {Param(JsonPath.Select(path))} = {Param(value)}::jsonb)");
}
}
if (json.Gt != null)
{
foreach (var(path, value) in json.Gt)
{
var val = Param(value);
var fld = $@"""{column}"" #>> {Param(JsonPath.Select(path))}";
var len = $"greatest(length({fld}), length({val}))";
AppendFilter(Regex.IsMatch(value, @"^\d+$")
? $@"lpad({fld}, {len}, '0') > lpad({val}, {len}, '0')"
: $@"{fld} > {val}");
}
}
if (json.Ge != null)
{
foreach (var(path, value) in json.Ge)
{
var val = Param(value);
var fld = $@"""{column}"" #>> {Param(JsonPath.Select(path))}";
var len = $"greatest(length({fld}), length({val}))";
AppendFilter(Regex.IsMatch(value, @"^\d+$")
? $@"lpad({fld}, {len}, '0') >= lpad({val}, {len}, '0')"
: $@"{fld} >= {val}");
}
}
if (json.Lt != null)
{
foreach (var(path, value) in json.Lt)
{
var val = Param(value);
var fld = $@"""{column}"" #>> {Param(JsonPath.Select(path))}";
var len = $"greatest(length({fld}), length({val}))";
AppendFilter(Regex.IsMatch(value, @"^\d+$")
? $@"lpad({fld}, {len}, '0') < lpad({val}, {len}, '0')"
: $@"{fld} < {val}");
}
}
if (json.Le != null)
{
foreach (var(path, value) in json.Le)
{
var val = Param(value);
var fld = $@"""{column}"" #>> {Param(JsonPath.Select(path))}";
var len = $"greatest(length({fld}), length({val}))";
AppendFilter(Regex.IsMatch(value, @"^\d+$")
? $@"lpad({fld}, {len}, '0') <= lpad({val}, {len}, '0')"
: $@"{fld} <= {val}");
}
}
if (json.As != null)
{
foreach (var(path, value) in json.As)
{
AppendFilter($@"""{column}"" #>> {Param(JsonPath.Select(path))} LIKE {Param(value)}");
}
}
if (json.Un != null)
{
foreach (var(path, value) in json.Un)
{
AppendFilter($@"NOT (""{column}"" #>> {Param(JsonPath.Select(path))} LIKE {Param(value)})");
}
}
if (json.In != null)
{
foreach (var(path, values) in json.In)
{
var sqls = new List <string>(values.Length);
foreach (var value in values)
{
var sql = $@"""{column}"" @> {Param(JsonPath.Merge(path, value))}::jsonb";
if (path.Any(x => x.Type == JsonPathType.Index))
{
sql += $@" AND ""{column}"" #> {Param(JsonPath.Select(path))} = {Param(value)}::jsonb";
}
sqls.Add(sql);
}
AppendFilter($"({string.Join(" OR ", sqls)})");
}
}
if (json.Ni != null)
{
foreach (var(path, values) in json.Ni)
{
foreach (var value in values)
{
AppendFilter(path.Any(x => x.Type == JsonPathType.Any)
? $@"NOT (""{column}"" @> {Param(JsonPath.Merge(path, value))}::jsonb)"
: $@"NOT (""{column}"" #> {Param(JsonPath.Select(path))} = {Param(value)}::jsonb)");
}
}
}
if (json.Null != null)
{
foreach (var(path, value) in json.Null)
{
if (path.Length == 0)
{
AppendFilter($@"""{column}"" IS {(value ? "" : "NOT ")}NULL");
}
else
{
if (value)
{
AppendFilter($@"""{column}"" IS NOT NULL");
}
AppendFilter($@"""{column}"" #>> {Param(JsonPath.Select(path))} IS {(value ? "" : "NOT ")}NULL");
}
}
}
return(this);
}
