COALESCE default value with no double quote

I have postgresql table :

test_field (character varying(255)) | id
Hello  | 2
[null] | 3

My query is :

 SELECT
                    "id"
                    FROM
                        my_table
                    WHERE
                     string_to_array(COALESCE("test_field"::TEXT, 'null'), ',') && '{hello, null}'
                    ORDER BY "Date" DESC
                    LIMIT 200

I’m trying to convert NULL value into ‘null’ in string to match my search query.
Problem is it didn’t grab the [null] values from ‘{hello, null}’

Then i tried this query :

select string_to_array(COALESCE("test_field"::TEXT, 'null'), ',') from my_table

And result is

{hello}
{"null"}

So i understand COALESCE or something else is adding double quote inside the array. But i don’t want because the search will not work with this double quotes.

I tried to remove double quote with replace function but i don’t succeed, any idea please ?

>Solution :

You can do it using REPLACE to replace null to "null" and ::TEXT[] to cast our string into array as follows :

SELECT
"id"
FROM my_table
WHERE string_to_array(COALESCE("test_field"::TEXT, 'null'), ',') 
      && REPLACE('{Hello, null}', 'null' , '"null"')::TEXT[];

Or just search on {Hello, "null"} instead of {hello, null} if it is possible :

SELECT
"id"
FROM my_table
WHERE string_to_array(COALESCE("test_field"::TEXT, 'null'), ',') && '{Hello, "null"}'

Demo here