1

I have an array of records. The record is something like (id, text_value)

And I'd like to search such in a WHERE clause. I'd like to include rows which have a certain value within the array of records. However, the function ANY, for example, seems to take equality as a single value. For example:

WHERE 'one' = ANY('{one,two}'::text[])

Whereas, my clause would be ANY(array_of_records). And the left hand side would therefore need to access a column in the record.

Is this possible in a WHERE clause, or should I use plpgsql to loop over the rows?

I'm also happy to use json or jsonb to do any array of records searching if that helps.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
mmm111mmm
  • 3,607
  • 4
  • 27
  • 44
  • I am not sure if I get the correctly but what is the issue with `WHERE 'one' = ANY('{one,two}'::text[])` ?As this seems to be fine. – Rahul Tripathi Aug 31 '15 at 18:01
  • Yes, it is fine. But I'm using it with records, not strings. – mmm111mmm Aug 31 '15 at 18:05
  • **Is this possible in a WHERE clause** ... What were the results when you tried it? – Emacs User Aug 31 '15 at 18:07
  • I'm unable to try it, since the LHS of the ANY function takes a single value, whereas I'd need to, procedurally speaking, loop through the array of records and check the 'text_value' of each against each record looped – mmm111mmm Aug 31 '15 at 18:10
  • The array of records is created with array_agg() on values from a join table. – mmm111mmm Aug 31 '15 at 18:12
  • What's the structure of your "array of records"? Could you please provide some concrete DDL and/or examples? – Lukas Eder Aug 31 '15 at 18:25
  • The structure of the array is (integer, text). – mmm111mmm Aug 31 '15 at 18:34
  • `ANY` is neither function nor operator, but an SQL *construct*, btw. May seem like nitpicking, [but that can make a big difference](http://stackoverflow.com/a/29245753/939860). – Erwin Brandstetter Sep 01 '15 at 01:42

2 Answers2

2

Just unnest the array and join it to your table. I'm going to make some assumptions about your schema... This is the record you were referring to, from which you can create an array r[]:

CREATE TYPE r AS (
  id INT,
  text_value TEXT
);

This is the table that contains values which you want to search for in your array of records:

CREATE TABLE t(v) AS
VALUES ('a'), ('b'), ('c'), ('d');

Now, simply join the two:

SELECT *
FROM t
JOIN unnest(array[row(1, 'a')::r, row(2, 'b')::r]) u
ON t.v = u.text_value

This will yield

v  id  text_value
-----------------
a  1   a
b  2   b
Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
  • Thanks, this is basically how I solved it. Previously, I made the array of records with `array_agg(select my_join.id, my_join.text_value)`. Now, I've done an additional array_agg which just has the `text_value` which works fine with `ANY()`. – mmm111mmm Aug 31 '15 at 18:44
1

I think the easiest way would be to create some filters before your query.

$tmp = array();

for (i=0; i < array.length; i++){ if(condition) $tmp[] = array[i]; }

$query = "SELECT * FROM example WHERE id IN (".implode(',',$tmp).")";

maxito
  • 21
  • 4
  • Thanks, I'll try something like this if there isn't a less verbose solution. I'm thinking of an aggregate function that loops over the records and looks for a certain values in a certain column in the record. Would be nice if there were an inbuilt solution, however. – mmm111mmm Aug 31 '15 at 18:17