在Postgres JSON数组内查询

Question

您将如何在存储在json列中的数组中搜索元素？（更新：另请参阅jsonb列的9.4更新答案。）

如果我有这样一个JSON文档，存储在名为blob的列中：

{"name": "Wolf",
 "ids": [185603363281305602,185603363289694211]}

我想做的是：

SELECT * from "mytable" WHERE 185603363289694211 = ANY("blob"->'ids');

并取出所有匹配的行。但这不起作用，因为"blob"-

如果可能的话，我还想在各个ID上建立一个索引。




Answer 1

我知道已经有一段时间了...

在postgresql-9.5中，现在可以轻松地查询它。

select '{"name": "Wolf",
         "ids": [185603363281305602,185603363289694211]}'::jsonb
       @> '{"ids":[185603363281305602]}'

我认为您应该使用jsonb字段，然后可以对其进行索引。

CREATE INDEX idx_gin_ids ON mytable USING gin ((blob -> 'ids'));

Answer 2

首先，尝试操作员-

接下来，查询可以这样工作：
如何使用新PostgreSQL JSON数据类型内的字段进行查询？

索引可能是这样工作的：
索引用于在JSON数组中查找元素

Answer 3

以下原始答案仅适用于Postgres 9.3。有关Postgres 9.4的答案，请参阅下面的更新。

这建立在欧文的参考答案之上，但对这个问题更明确一点。

本例中的ID是bigint，因此创建一个helper函数，用于将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;

我们本可以在这里轻松地（也许更易于重用）返回一个数组。我怀疑在bigint上建立索引要比文本快得多，但我很难在网上找到支持这一点的证据。

用于构建索引：

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类型。这是一个很好的关于jsonb的答案，以及何时应该在json上使用它。简而言之，如果要查询JSON，应该使用jsonb。

如果您将列构建为jsonb，则可以使用此查询：

SELECT * FROM "mytable"
  WHERE blob @> '{"ids": [185603363289694211]}';

<代码>@