def prop_filter_json_extract(
prop: Property,
idx: int,
prepend: str = "",
prop_var: str = "properties") -> Tuple[str, Dict[str, Any]]:
operator = prop.operator
if operator == "is_not":
params = {
"k{}_{}".format(prepend, idx): prop.key,
"v{}_{}".format(prepend, idx): prop.value
}
return (
"AND NOT (trim(BOTH '\"' FROM JSONExtractRaw({prop_var}, %(k{prepend}_{idx})s)) = %(v{prepend}_{idx})s)"
.format(idx=idx, prepend=prepend, prop_var=prop_var),
params,
)
elif operator == "icontains":
value = "%{}%".format(prop.value)
params = {
"k{}_{}".format(prepend, idx): prop.key,
"v{}_{}".format(prepend, idx): value
}
return (
"AND trim(BOTH '\"' FROM JSONExtractRaw({prop_var}, %(k{prepend}_{idx})s)) LIKE %(v{prepend}_{idx})s"
.format(idx=idx, prepend=prepend, prop_var=prop_var),
params,
)
elif operator == "not_icontains":
value = "%{}%".format(prop.value)
params = {
"k{}_{}".format(prepend, idx): prop.key,
"v{}_{}".format(prepend, idx): value
}
return (
"AND NOT (trim(BOTH '\"' FROM JSONExtractRaw({prop_var}, %(k{prepend}_{idx})s)) LIKE %(v{prepend}_{idx})s)"
.format(idx=idx, prepend=prepend, prop_var=prop_var),
params,
)
elif operator == "regex":
params = {
"k{}_{}".format(prepend, idx): prop.key,
"v{}_{}".format(prepend, idx): prop.value
}
return (
"AND match(trim(BOTH '\"' FROM JSONExtractRaw({prop_var}, %(k{prepend}_{idx})s)), %(v{prepend}_{idx})s)"
.format(idx=idx, prepend=prepend, prop_var=prop_var),
params,
)
elif operator == "not_regex":
params = {
"k{}_{}".format(prepend, idx): prop.key,
"v{}_{}".format(prepend, idx): prop.value
}
return (
"AND NOT match(trim(BOTH '\"' FROM JSONExtractRaw({prop_var}, %(k{prepend}_{idx})s)), %(v{prepend}_{idx})s)"
.format(idx=idx, prepend=prepend, prop_var=prop_var),
params,
)
elif operator == "is_set":
params = {
"k{}_{}".format(prepend, idx): prop.key,
"v{}_{}".format(prepend, idx): prop.value
}
return (
"AND JSONHas({prop_var}, %(k{prepend}_{idx})s)".format(
idx=idx, prepend=prepend, prop_var=prop_var),
params,
)
elif operator == "is_not_set":
params = {
"k{}_{}".format(prepend, idx): prop.key,
"v{}_{}".format(prepend, idx): prop.value
}
return (
"AND (isNull(trim(BOTH '\"' FROM JSONExtractRaw({prop_var}, %(k{prepend}_{idx})s))) OR NOT JSONHas({prop_var}, %(k{prepend}_{idx})s))"
.format(idx=idx, prepend=prepend, prop_var=prop_var),
params,
)
elif operator == "gt":
params = {
"k{}_{}".format(prepend, idx): prop.key,
"v{}_{}".format(prepend, idx): prop.value
}
return (
"AND toInt64OrNull(replaceRegexpAll(visitParamExtractRaw({prop_var}, %(k{prepend}_{idx})s), ' ', '')) > %(v{prepend}_{idx})s"
.format(idx=idx, prepend=prepend, prop_var=prop_var),
params,
)
elif operator == "lt":
params = {
"k{}_{}".format(prepend, idx): prop.key,
"v{}_{}".format(prepend, idx): prop.value
}
return (
"AND toInt64OrNull(replaceRegexpAll(visitParamExtractRaw({prop_var}, %(k{prepend}_{idx})s), ' ', '')) < %(v{prepend}_{idx})s"
.format(idx=idx, prepend=prepend, prop_var=prop_var),
params,
)
else:
if is_int(prop.value):
clause = "AND JSONExtractInt({prop_var}, %(k{prepend}_{idx})s) = %(v{prepend}_{idx})s"
elif is_json(prop.value):
clause = "AND replaceRegexpAll(visitParamExtractRaw({prop_var}, %(k{prepend}_{idx})s),' ', '') = replaceRegexpAll(toString(%(v{prepend}_{idx})s),' ', '')"
else:
clause = "AND trim(BOTH '\"' FROM JSONExtractRaw({prop_var}, %(k{prepend}_{idx})s)) = %(v{prepend}_{idx})s"
params = {
"k{}_{}".format(prepend, idx): prop.key,
"v{}_{}".format(prepend, idx): prop.value
}
return (
clause.format(idx=idx, prepend=prepend, prop_var=prop_var),
params,
)
def prop_filter_json_extract(
prop: Property,
idx: int,
prepend: str = "",
prop_var: str = "properties",
allow_denormalized_props: bool = False) -> Tuple[str, Dict[str, Any]]:
# Once all queries are migrated over we can get rid of allow_denormalized_props
is_denormalized = prop.key.lower(
) in settings.CLICKHOUSE_DENORMALIZED_PROPERTIES and allow_denormalized_props
json_extract = "trim(BOTH '\"' FROM JSONExtractRaw({prop_var}, %(k{prepend}_{idx})s))".format(
idx=idx, prepend=prepend, prop_var=prop_var)
denormalized = "properties_{}".format(prop.key.lower())
operator = prop.operator
if operator == "is_not":
params = {
"k{}_{}".format(prepend, idx): prop.key,
"v{}_{}".format(prepend, idx): prop.value
}
return (
"AND NOT ({left} = %(v{prepend}_{idx})s)".format(
idx=idx,
prepend=prepend,
left=denormalized if is_denormalized else json_extract),
params,
)
elif operator == "icontains":
value = "%{}%".format(prop.value)
params = {
"k{}_{}".format(prepend, idx): prop.key,
"v{}_{}".format(prepend, idx): value
}
return (
"AND {left} LIKE %(v{prepend}_{idx})s".format(
idx=idx,
prepend=prepend,
left=denormalized if is_denormalized else json_extract),
params,
)
elif operator == "not_icontains":
value = "%{}%".format(prop.value)
params = {
"k{}_{}".format(prepend, idx): prop.key,
"v{}_{}".format(prepend, idx): value
}
return (
"AND NOT ({left} LIKE %(v{prepend}_{idx})s)".format(
idx=idx,
prepend=prepend,
left=denormalized if is_denormalized else json_extract),
params,
)
elif operator == "regex":
params = {
"k{}_{}".format(prepend, idx): prop.key,
"v{}_{}".format(prepend, idx): prop.value
}
return (
"AND match({left}, %(v{prepend}_{idx})s)".format(
idx=idx,
prepend=prepend,
left=denormalized if is_denormalized else json_extract),
params,
)
elif operator == "not_regex":
params = {
"k{}_{}".format(prepend, idx): prop.key,
"v{}_{}".format(prepend, idx): prop.value
}
return (
"AND NOT match({left}, %(v{prepend}_{idx})s)".format(
idx=idx,
prepend=prepend,
left=denormalized if is_denormalized else json_extract),
params,
)
elif operator == "is_set":
params = {
"k{}_{}".format(prepend, idx): prop.key,
"v{}_{}".format(prepend, idx): prop.value
}
if is_denormalized:
return (
"AND NOT isNull({left})".format(left=denormalized),
params,
)
return (
"AND JSONHas({prop_var}, %(k{prepend}_{idx})s)".format(
idx=idx, prepend=prepend, prop_var=prop_var),
params,
)
elif operator == "is_not_set":
params = {
"k{}_{}".format(prepend, idx): prop.key,
"v{}_{}".format(prepend, idx): prop.value
}
if is_denormalized:
return (
"AND isNull({left})".format(left=denormalized),
params,
)
return (
"AND (isNull({left}) OR NOT JSONHas({prop_var}, %(k{prepend}_{idx})s))"
.format(idx=idx,
prepend=prepend,
prop_var=prop_var,
left=json_extract),
params,
)
elif operator == "gt":
params = {
"k{}_{}".format(prepend, idx): prop.key,
"v{}_{}".format(prepend, idx): prop.value
}
return (
"AND toInt64OrNull(replaceRegexpAll({left}, ' ', '')) > %(v{prepend}_{idx})s"
.format(
idx=idx,
prepend=prepend,
left=denormalized if is_denormalized else
"visitParamExtractRaw({prop_var}, %(k{prepend}_{idx})s)".
format(
idx=idx,
prepend=prepend,
prop_var=prop_var,
),
),
params,
)
elif operator == "lt":
params = {
"k{}_{}".format(prepend, idx): prop.key,
"v{}_{}".format(prepend, idx): prop.value
}
return (
"AND toInt64OrNull(replaceRegexpAll({left}, ' ', '')) < %(v{prepend}_{idx})s"
.format(
idx=idx,
prepend=prepend,
left=denormalized if is_denormalized else
"visitParamExtractRaw({prop_var}, %(k{prepend}_{idx})s)".
format(
idx=idx,
prepend=prepend,
prop_var=prop_var,
),
),
params,
)
else:
if is_int(prop.value) and not is_denormalized:
clause = "AND JSONExtractInt({prop_var}, %(k{prepend}_{idx})s) = %(v{prepend}_{idx})s"
elif is_int(prop.value) and is_denormalized:
clause = "AND toInt64OrNull({left}) = %(v{prepend}_{idx})s"
elif is_json(prop.value) and not is_denormalized:
clause = "AND replaceRegexpAll(visitParamExtractRaw({prop_var}, %(k{prepend}_{idx})s),' ', '') = replaceRegexpAll(toString(%(v{prepend}_{idx})s),' ', '')"
else:
clause = "AND {left} = %(v{prepend}_{idx})s"
params = {
"k{}_{}".format(prepend, idx): prop.key,
"v{}_{}".format(prepend, idx): prop.value
}
return (
clause.format(
left=denormalized if is_denormalized else json_extract,
idx=idx,
prepend=prepend,
prop_var=prop_var),
params,
)