如何参数化这个jsonb数组包含的查询

Question

我有以下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;

这给了我(正如预期的那样(：

789019 | 789019

现在，我需要参数化查询，但无法：

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');

我得到一个空行，这是因为它实际上是在搜索"$1"。我试图删除双引号，然后它抱怨

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

我不知道如何解决这个问题。

Answer 1

如果只想检查数组是否包含给定值，可以使用?而不是@>。

这看起来像：

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');