Postgres的JSON数组里面查询 [英] Querying inside Postgres JSON arrays

问题描述

您会如何去寻找存储在 JSON 列的数组中的元素？的（更新：另请参见 jsonb 列9.4更新的答案）的

How would you go about searching for an element inside an array stored in a json column? (Update: Also see the 9.4 updated answer for jsonb columns.)

如果我有一个JSON文件这样，存储在一个名为 JSON 列一滴：

If I have a JSON document like this, stored in a json column named blob:

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

我希望能够做的是一样的东西：

what I'd like to be able to do is something like:

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

和得到所有匹配的行了。但是，这并不工作，因为斑点 - 方式＆gt;IDS返回JSON值，而不是Postgres的阵列

and get all matching rows out. But this doesn't work because "blob"->'ids' returns JSON values, not Postgres arrays.

我也想建立在各自的ID的索引，如果可能的话。

I'd also like to build an index on the individual IDs, if that's possible.

推荐答案

的下面原来的答案，只适用于Postgres的9.3。对于一个Postgres 9.4的回答，请参见下面的更新。的

此基础上欧文的引用答案的，但更明确这个问题一点点。

This builds on Erwin's referenced answers, but is a little bit more explicit to this question.

在这种情况下，ID为 BIGINT S，所以JSON数组转换成一个Postgres BIGINT 数组：

The IDs in this case are bigints, so create a helper function for converting a JSON array to a Postgres bigint array:

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;

我们可能只是很容易地（也许更重usably）返回文本阵列，而不是在这里。我怀疑在索引 BIGINT 比文本快了很多，但我有困难的时候发现网上证据支持这件事。

We could just have easily (and perhaps more re-usably) returned a text array here instead. I suspect indexing on bigint is a lot faster than text but I'm having a difficult time finding evidence online to back that up.

有关创建索引：

CREATE INDEX "myindex" ON "mytable" 
  USING GIN (json_array_bigint("blob"->'ids'));

有关查询，这个工作和使用索引：

For querying, this works and uses the index:

SELECT * FROM "mytable" 
  WHERE '{185603363289694211}' <@ json_array_bigint("blob"->'ids');

这样做也会用于查询工作，但它不使用索引

Doing this will also work for querying, but it doesn't use the index:

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

更新9.4

Postgres的9.4引入了 jsonb 键入。 <一href=\"http://stackoverflow.com/questions/22654170/explanation-of-jsonb-introduced-by-postgresql\">This是一个很好的SO回答关于 jsonb ，当你应该使用它在 JSON 。总之，如果你曾经查询JSON，你应该使用 jsonb 。

Postgres 9.4 introduced the jsonb type. This is a good SO answer about jsonb and when you should use it over json. In short, if you're ever querying the JSON, you should use jsonb.

如果您打造为 jsonb 你列，您可以使用此查询：

If you build your column as jsonb, you can use this query:

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

的 @＆GT; 是Postgres的包含运营商，<一个href=\"http://www.postgresql.org/docs/9.4/static/functions-json.html#FUNCTIONS-JSONB-OP-TABLE\">documented为 jsonb 这里。
由于阿兰的回答提出这个引起我的注意。

The @> is Postgres' contains operator, documented for jsonb here. Thanks to Alain's answer for bringing this to my attention.

这篇关于Postgres的JSON数组里面查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！