search for multiple values in comma separated column (postgresql)

Question

I need to fetch all records where these (5565bffd-b1c8-4556-ae5d-4bef61af48f5","5565bffd-cd78-4e6f-ae13-4bef61af48f5) values exists in categories_id column. The value that I need to search in categories_id can be multiple because it coming from the form.

+--------------------------------------+-------------+-------------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                                   | name        | alias       | description | categories_id                                                                                                                                                 |
+--------------------------------------+-------------+-------------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 5565c08d-9f18-4b76-9cae-4a8261af48f5 | Honeycolony | honeycolony | null        | ["5565bffd-7f64-494c-8950-4bef61af48f5"]                                                                                                                      |
+--------------------------------------+-------------+-------------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
| c8f16660-32cf-11e6-b73c-1924f891ba4d | LOFT        | loft        | null        | ["5565bffd-25bc-4b09-8a83-4bef61af48f5","5565bffd-b1c8-4556-ae5d-4bef61af48f5","5565bffd-cd78-4e6f-ae13-4bef61af48f5"]                                        |
+--------------------------------------+-------------+-------------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 5565c17f-80d8-4390-aadf-4a8061af48f5 | Fawn Shoppe | fawn-shoppe | null        | ["5565bffd-25bc-4b09-8a83-4bef61af48f5","5565bffd-0744-4740-81f5-4bef61af48f5","5565bffd-b1c8-4556-ae5d-4bef61af48f5","5565bffd-cd78-4e6f-ae13-4bef61af48f5"] |
+--------------------------------------+-------------+-------------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+

I have this function which work as in_array function php.

CREATE OR REPLACE FUNCTION public.arraycontain(
    x json,
    y json)
    RETURNS boolean
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE 
AS $BODY$
  DECLARE a text;b text;
  BEGIN
    FOR a IN SELECT json_array_elements_text($1)
    LOOP
      FOR b IN SELECT json_array_elements_text($2)
      LOOP
        IF a = b THEN
          RETURN TRUE;
        END IF;
      END LOOP;
    END LOOP;
    RETURN FALSE ;
  END;
  $BODY$;

ALTER FUNCTION public.arraycontain(json, json)
    OWNER TO postgres;

But when I do this:

select * from "stores" 
where arrayContain(stores.categories_id::JSON,'["5565bffd-b1c8-4556-ae5d-4bef61af48f5","5565bffd-cd78-4e6f-ae13-4bef61af48f5"]')

it shows

ERROR: invalid input syntax for type json
DETAIL: The input string ended unexpectedly.
CONTEXT: JSON data, line 1: SQL state: 22P02

here is the sqlfiddle (I couldn't update the arraycontain function in fiddle.)

My expected output from the fiddle is it should return last 3 rows that is Furbish Studio,Fawn Shoppe AND LOFT if search using this values ["5565bffd-b1c8-4556-ae5d-4bef61af48f5","5565bffd-cd78-4e6f-ae13-4bef61af48f5"])

I am open for any recommendation. I also tried this query below but it returns empty.

select id
from stores
where string_to_array(categories_id,',') && array['5565bffd-cd78-4e6f-ae13-4bef61af48f5','5565bffd-b1c8-4556-ae5d-4bef61af48f5'];

EDIT:

This code is actually a filter to filter data. So if I only filter using categories it didn't work but if there is a query before it it works

select * from "stores" 
    where name like '%ab%' and  arrayContain(stores.categories_id::JSON,'["5565bffd-b1c8-4556-ae5d-4bef61af48f5","5565bffd-cd78-4e6f-ae13-4bef61af48f5"]')

also the thing that amaze me is that the '%ab%' must contain more than two character if there's below <2 it will throw error. what could be wrong.

Why isn't categories_id defined as jsonb or text[]? That would make things a lot easier. — user330315
– user330315, Commented Dec 12, 2019 at 9:35
@a_horse_with_no_name categories_id is defined as text. converting categories_id from text to jsonb can cause any errors? — danish-khan-I
– danish-khan-I, Commented Dec 12, 2019 at 9:49

S-Man · Accepted Answer · 2019-12-12 09:30:45Z

1

Click: demo:db<>fiddle

You can use the ?| operator, which takes a jsonb array (your column in that case) and checks a text array if any elements are included:

SELECT
    *
FROM 
    mytable
WHERE categories_id ?| '{5565bffd-b1c8-4556-ae5d-4bef61af48f5,5565bffd-cd78-4e6f-ae13-4bef61af48f5}'

If your categories_id is not of type json (which is what the error message says) but a simple text array, you can compare two text arrays directly using the && operator:

Click: demo:db<>fiddle

SELECT
    *
FROM 
    mytable
WHERE categories_id && '{5565bffd-b1c8-4556-ae5d-4bef61af48f5,5565bffd-cd78-4e6f-ae13-4bef61af48f5}'

edited Dec 12, 2019 at 9:30

answered Dec 12, 2019 at 9:24

S-Man

24k9 gold badges51 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

danish-khan-I Over a year ago

Both of your code works in fiddle. The second one is having { braces where else i have [ square brackets. So when i tried. it doesn't work because i saved it as array. The question is do i need to convert [] to {} or i convert datatype for categories_id from text to jsonb?

S-Man Over a year ago

Try to reproduce it in a fiddle as well

Bjarni Ragnarsson · Accepted Answer · 2019-12-12 12:40:51Z

1

Your code seems to work fine so perhaps sqlfiddle is the problem. Try changing the separator in the schema building part to / (instead of ;) and make sure you have the correct version for Postgresql. json_array_elements_text is not supported in 9.3 (you can use json_array_elements instead in this case).

Also skip the " in the select statement.

Look here http://sqlfiddle.com/#!17/918b75/1

There might be an error in your data. Perhaps categories_id is an empty string somewhere.

Try this to see the offending data if any.

do $$ 
declare 
    r record;
    b boolean;
begin
for r in (select * from stores) loop
b:= arrayContain(r.categories_id::JSON,'["5565bffd-b1c8-4556-ae5d-4bef61af48f5","5565bffd-cd78-4e6f-ae13-4bef61af48f5"]') ;
end loop;
exception
    when others 
        then raise notice '%,%',r,r.categories_id;
        return;
end;
$$

Best regards.
Bjarni

edited Dec 12, 2019 at 12:40

answered Dec 12, 2019 at 9:54

Bjarni Ragnarsson

1,7811 gold badge8 silver badges8 bronze badges

12 Comments

danish-khan-I Over a year ago

I tried this for the record of 30300 and still the same error ERROR: invalid input syntax for type json DETAIL: The input string ended unexpectedly. but if i limit it to 10 it works anything greater than 13 doesn't work.

Bjarni Ragnarsson Over a year ago

What do you mean by "record of 30300"?

danish-khan-I Over a year ago

I have this much(number) of rows in my table.

Bjarni Ragnarsson Over a year ago

I suspect an error in your data. I edited my answer for a code to look for it.

danish-khan-I Over a year ago

Yes i have received this error just paste it here

|

Collectives™ on Stack Overflow

search for multiple values in comma separated column (postgresql)

2 Answers 2

2 Comments

12 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

12 Comments

Your Answer

Sign up or log in

Post as a guest

Related