SELECT clause against values from array-CodePudding

I have a table containing the column regions

Column type: citext
Column Default: '[]'::text

Sample values recorded in column

 Value1: {"florida, united states"}
 Value2: {"texas, united states"}
 Value3: {"ohio, united states","north dakota, united states"}

Need to perform a search query on the table returning all the values containing India and united kingdom

Row can contain any number of values, I need rows where in regions array both india and united kingdom are present

CodePudding user response：

Expand the array to a row set using unnest() function:

SELECT *
  FROM <table>
 WHERE
EXISTS
     ( SELECT 1
         FROM unnest(<value_column>)
           AS a(value)
        WHERE a.value ilike '%india'     
     )
   AND
EXISTS
     ( SELECT 1
         FROM unnest(<value_column>)
           AS a(value)
        WHERE a.value ilike '%united kingdom'     
     )

CodePudding user response：

An alternate solution:

UPDATED to include data in comment from @smvenk and change to query to address.

select id, varchar_array from array_test ;
 id |                     varchar_array                     
---- -------------------------------------------------------
  1 | {"florida, united states"}
  2 | {"texas, united states"}
  3 | {"ohio, united states","north dakota, united states"}
  4 | {"london, united kingdom","mumbai, india"}
  5 | {"indiana, united states","wales, united kingdom"}


SELECT
    id,
    array_val
FROM
    array_test,
    array_to_string(varchar_array, ',') AS un (array_val)
WHERE
    array_val ILIKE '%, india%'
    AND array_val ILIKE '%, united kingdom%';

id |              array_val               
---- --------------------------------------
  4 | london, united kingdom,mumbai, india

From here Array functions:

array_to_string ( array anyarray, delimiter text [, null_string text ] ) → text

Converts each array element to its text representation, and concatenates those separated by the delimiter string. If null_string is given and is not NULL, then NULL array entries are represented by that string; otherwise, they are omitted.

array_to_string(ARRAY[1, 2, 3, NULL, 5], ',', '') → 1,2,3,,5

This pulls all the separate values into a single string which is then searched for india and the united kingdom.