How do I parameterize this jsonb array contains query?

I have the following sql:

WITH
ni AS (SELECT DISTINCT ON(issuercik) issuercik FROM new_insiders
WHERE date_filed > (CURRENT_DATE - INTERVAL '360 day')::date AND ( tickers @> '["MSFT"]'::jsonb ) ORDER BY issuercik, date_filed DESC),
t AS (SELECT cik FROM companies WHERE data->'tickers' @> '["MSFT"]'::jsonb ) 
SELECT ni.issuercik AS first, t.cik AS second FROM ni FULL OUTER JOIN t ON ni.issuercik = t.cik;

Which gives me (as expected):

789019 | 789019

Now, I need to parameterize the query but am unable to:

DEALLOCATE stmt;
PREPARE stmt AS WITH
ni AS (SELECT DISTINCT ON(issuercik) issuercik FROM new_insiders
WHERE date_filed > (CURRENT_DATE - INTERVAL '360 day')::date AND ( tickers @> '["$1"]'::jsonb ) ORDER BY issuercik, date_filed DESC),
t AS (SELECT cik FROM companies WHERE data->'tickers' @> '["$1"]'::jsonb ) 
SELECT ni.issuercik AS first, t.cik AS second FROM ni FULL OUTER JOIN t ON ni.issuercik = t.cik;

EXECUTE stmt('MSFT');

I get an empty row and that’s because it is literally searching for "$1". I try to remove the double quotes then it complains

ERROR:  invalid input syntax for type json
LINE 4: ...DATE - INTERVAL '360 day')::date AND ( tickers @> '[$1]'::js...
                                                             ^
DETAIL:  Token "$" is invalid.

I’m not sure how to resolve this one.

Answer

If you just want to check if an array contains a given value, you can use ? instead of @>.

That would look like:

DEALLOCATE stmt;

PREPARE stmt AS
WITH
    ni AS (
        SELECT DISTINCT ON(issuercik) issuercik 
        FROM new_insiders
        WHERE 
            date_filed > (CURRENT_DATE - INTERVAL '360 day')::date 
            AND tickers ? $1
        ORDER BY issuercik, date_filed DESC
    ),
    t AS (
        SELECT cik 
        FROM companies 
        WHERE data->'tickers' ? $1
    ) 
SELECT ni.issuercik AS first, t.cik AS second 
FROM ni 
FULL OUTER JOIN t ON ni.issuercik = t.cik;

EXECUTE stmt('MSFT');