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 bigint
s, 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'));