I have the following sql:
x
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.
Advertisement
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');