我在Django 1.5.4和PostgreSQL 9.3中工作,使用JSONField的django-jsonfield。
以下查询引发db错误(无法识别json类型的相等运算符):
ModelWithJsonField.objects.annotate(count=Count('field_to_count_by'))field_to_count_by不是JSONField,普通的整型字段。
有什么想法可以让我在解决这个问题的同时仍然使用annotate?
annotate在引擎盖后面做了什么?
发布于 2013-12-06 21:31:26
我遇到了同样的问题,最后(今天)通过在psql控制台中以admin身份运行以下命令实现了一个伪操作符:
-- This creates a function named hashjson that transforms the
-- json to texts and generates a hash
CREATE OR REPLACE FUNCTION hashjson(
json
) RETURNS INTEGER LANGUAGE SQL STRICT IMMUTABLE AS $$
SELECT hashtext($1::text);
$$;
-- This creates a function named json_eq that checks equality (as text)
CREATE OR REPLACE FUNCTION json_eq(
json,
json
) RETURNS BOOLEAN LANGUAGE SQL STRICT IMMUTABLE AS $$
SELECT bttextcmp($1::text, $2::text) = 0;
$$;
-- This creates an operator from the equality function
CREATE OPERATOR = (
LEFTARG = json,
RIGHTARG = json,
PROCEDURE = json_eq
);
-- Finaly, this defines a new default JSON operator family with the
-- operators and functions we just defined.
CREATE OPERATOR CLASS json_ops
DEFAULT FOR TYPE json USING hash AS
OPERATOR 1 =,
FUNCTION 1 hashjson(json);(灵感来自this线程)
我还在django-jsonfield GitHub issue中引用了您的问题。
注意:
发布于 2014-03-15 12:25:25
我经历了同样的问题,然后我尝试了Joachim Jablon的代码,尽管它看起来工作得很好,但仍然有问题。我将在这里进入重点,最长版本的is on my blog。
SELECT '{"a":1,"b":2}'::json = '{"b":2,"a":1}'::json基于字符串representation.false。允许对字段进行排序,因为operator类是hash而不是btree.然后,我在PL/V8中创建了一个json_cmp()函数,该函数可用于增强btree所需的运算符。
下面是完整的SQL脚本
CREATE OR REPLACE FUNCTION json_cmp(left json, right json)
RETURNS integer AS $$
function cleverType(obj) {
var type = typeof obj;
if (type === 'object') {
if (obj === null) {
type = 'null';
} else if (obj instanceof Array) {
type = 'array';
}
}
return type;
}
function cmp(left, right) {
var leftType = cleverType(left),
rightType = cleverType(right),
i,
buf,
leftKeys,
rightKeys,
output = 0;
if (leftType !== rightType) {
output = leftType.localeCompare(rightType);
} else if (leftType === 'number'
|| leftType === 'boolean'
|| leftType === 'string') {
if (left < right) {
output = -1;
} else if (left > right) {
output = 1;
} else {
output = 0;
}
} else if (leftType === 'array') {
if (left.length !== right.length) {
output = cmp(left.length, right.length);
} else {
for (i = 0; i < left.length; i += 1) {
buf = cmp(left[i], right[i]);
if (buf !== 0) {
output = buf;
break;
}
}
}
} else if (leftType === 'object') {
leftKeys = Object.keys(left);
rightKeys = Object.keys(right);
if (leftKeys.length !== rightKeys.length) {
leftKeys.sort();
rightKeys.sort();
buf = cmp(leftKeys, rightKeys);
} else {
buf = cmp(leftKeys.length, rightKeys.length);
}
if (buf !== 0) {
output = buf;
} else {
for (i = 0; i < leftKeys.length; i += 1) {
buf = cmp(left[leftKeys[i]], right[leftKeys[i]]);
if (buf !== 0) {
output = buf;
break;
}
}
}
}
return output;
}
return cmp(left, right);
$$ LANGUAGE plv8 IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION json_eq(json, json)
RETURNS BOOLEAN LANGUAGE SQL STRICT IMMUTABLE AS $$
SELECT json_cmp($1, $2) = 0;
$$;
CREATE OR REPLACE FUNCTION json_lt(json, json)
RETURNS BOOLEAN LANGUAGE SQL STRICT IMMUTABLE AS $$
SELECT json_cmp($1, $2) < 0;
$$;
CREATE OR REPLACE FUNCTION json_lte(json, json)
RETURNS BOOLEAN LANGUAGE SQL STRICT IMMUTABLE AS $$
SELECT json_cmp($1, $2) <= 0;
$$;
CREATE OR REPLACE FUNCTION json_gt(json, json)
RETURNS BOOLEAN LANGUAGE SQL STRICT IMMUTABLE AS $$
SELECT json_cmp($1, $2) > 0;
$$;
CREATE OR REPLACE FUNCTION json_gte(json, json)
RETURNS BOOLEAN LANGUAGE SQL STRICT IMMUTABLE AS $$
SELECT json_cmp($1, $2) >= 0;
$$;
CREATE OPERATOR = (LEFTARG = json, RIGHTARG = json, PROCEDURE = json_eq);
CREATE OPERATOR < (LEFTARG = json, RIGHTARG = json, PROCEDURE = json_lt);
CREATE OPERATOR <= (LEFTARG = json, RIGHTARG = json, PROCEDURE = json_lte);
CREATE OPERATOR > (LEFTARG = json, RIGHTARG = json, PROCEDURE = json_gt);
CREATE OPERATOR >= (LEFTARG = json, RIGHTARG = json, PROCEDURE = json_gte);
CREATE OPERATOR CLASS json_ops
DEFAULT FOR TYPE json USING btree AS
OPERATOR 1 <,
OPERATOR 2 <=,
OPERATOR 3 =,
OPERATOR 4 >=,
OPERATOR 5 >,
FUNCTION 1 json_cmp(json, json);当然,这往往比简单的字符串比较慢得多,但它的优点是生成更健壮的结果。
请注意,如果您使用South进行迁移,您可以创建一个空迁移并从forwards()方法执行SQL。这将在您迁移应用程序时自动安装函数。
发布于 2014-02-27 19:22:18
我的解决方案使用PL/Python,它解析并重新转储对键进行排序的json,然后生成结果的FNV1a散列:https://github.com/ifad/chronomodel/blob/master/sql/json_ops.sql。
我没有使用hashtext(),因为它仅供内部使用:http://www.postgresql.org/message-id/24463.1329854466@sss.pgh.pa.us。
这不是灵丹妙药,只是一个粗糙的破解。真正的解决方案是等待Postgres的完全支持。
https://stackoverflow.com/questions/19117933
复制相似问题