如何在存储在json列中的数组中搜索元素？(Update: Also see the 9.4 updated answer for 101 columns.)
如果我有这样一个JSON文档，存储在一个名为blob的json列中:
{"name": "Wolf",
"ids": [185603363281305602,185603363289694211]}
我想做的是:
SELECT * from "mytable" WHERE 185603363289694211 = ANY("blob"->'ids');
把所有匹配的行都取出来.但这不起作用，因为"blob"->'ids'返回的是JSON值，而不是Postgresarray.
如果可能的话，我还想在各个ID上建立一个索引.
The following original answer applies only for Postgres 9.3. For a Postgres 9.4 answer, see the Update below.
这是建立在Erwin's referenced answers的基础上的，但对这个问题来说更明确一点.
本例中的ID是bigints，因此创建一个帮助函数，用于将JSON数组转换为Postgres bigint数组:
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;
我们本可以很容易地(也许更容易重用)在这里返回一个textarray.我怀疑在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'));
Update for 9.4
Postgres 9.4引入了jsonb类型.This is a good SO answer about jsonb and when you should use it over json.简而言之，如果你要查询JSON，应该使用jsonb.
如果将列构建为jsonb，则可以使用以下查询:
SELECT * FROM "mytable"
WHERE blob @> '{"ids": [185603363289694211]}';
@>是Postgres的包含运算符，documented for jsonb here.