Querying inside Postgres JSON arrays

The following original answer applies only for Postgres 9.3. For a Postgres 9.4 answer, see the Update below.

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

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;

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.

For building the index:

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'));

Update for 9.4

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.

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

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

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

First, try the operator ->> instead of -> to strip the JSON layer from the array value.

Next, the query can work like this:
How do I query using fields inside the new PostgreSQL JSON datatype?

And indexing might work like this:
Index for finding an element in a JSON array

I know it's been a while...

In postgresql-9.5, it's now possible to query it easily.

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

I think you should use a jsonb field instead and you can index it afterward.

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