Esempio n. 1
0
        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);
        }