您将如何在存储在json
列中的数组内搜索元素?(更新:另请参见9.4更新的答案jsonb
列)。
如果我有这样的JSON文档,则存储在json
名为的列中blob
:
{"name": "Wolf",
"ids": [185603363281305602,185603363289694211]}
我想要做的是这样的:
SELECT * from "mytable" WHERE 185603363289694211 = ANY("blob"->'ids');
并取出所有匹配的行。但这不起作用,因为它"blob"->'ids'
返回JSON值,而不是Postgres数组。
如果可能,我还想在各个ID上建立索引。
以下原始答案仅适用于Postgres 9.3。有关Postgres 9.4的答案,请参见下面的更新。
这建立在Erwin的参考答案的基础上,但对该问题更为明确。
在这种情况下bigint
,这些ID是s,因此请创建一个用于将JSON数组转换为Postgresbigint
数组的辅助函数:
CREATE OR REPLACE FUNCTION json_array_bigint(_j json)
RETURNS bigint[] AS
$$
SELECT array_agg(elem::text::bigint)
FROM json_array_elements(_j) AS elem
$$
LANGUAGE sql IMMUTABLE;
我们可以轻松地(也许更可重用)text
在这里返回一个数组。我怀疑建立索引的bigint
速度比text
以前快很多,但是我很难在网上找到证据来证明这一点。
用于建立索引:
CREATE INDEX "myindex" ON "mytable"
USING GIN (json_array_bigint("blob"->'ids'));
对于查询,这有效并使用索引:
SELECT * FROM "mytable"
WHERE '{185603363289694211}' <@ json_array_bigint("blob"->'ids');
这样做也可以用于查询,但是它不使用索引:
SELECT * FROM "mytable"
WHERE 185603363289694211 = ANY(json_array_bigint("blob"->'ids'));
9.4更新
Postgres 9.4引入了这种jsonb
类型。这是一个很好的SO答案,有关jsonb
何时使用它的建议json
。简而言之,如果您要查询JSON,则应使用jsonb
。
如果将列构建为jsonb
,则可以使用以下查询:
SELECT * FROM "mytable"
WHERE blob @> '{"ids": [185603363289694211]}';
该@>
是Postgres的包含操作,记录了jsonb
这里。感谢Alain的回答,这引起了我的注意。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句