Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 3 down vote favorite

How would you go about searching for an element inside an array stored in a json column?

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

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

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

This one of the nice things about MongoDB: you can just match elements within an array without doing anything special.

share|improve this question

2 Answers

up vote 1 down vote

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');
share|improve this answer
1  
+1 Nice work. Note, if elements within the JSON arrays are not unique you might get duplicate rows in the result. And yes, processing bigint is generally faster than processing text. Also makes for a smaller index, but simplify elem::text::bigint to elem::bigint. BTW, no need for double-quotes around perfectly valid identifiers in Postgres. –  Erwin Brandstetter Sep 16 at 20:34
 
Thanks for the help! Unfortunately casting from json directly to bigint does not work. I have to pass it through text first. –  Joe Shaw Sep 17 at 13:30
up vote 0 down vote

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 Element in JSON array

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.