In sql package for postgresql jsonb how can I use the @>
operator with a string query for example
WHERE data @> '{"stringdata": $1}'
orWHERE data @> '{"stringdata": "$1"}'
doesn’t workThe alternative is data->>'string_data' = $1
which I don’t prefer for multiple statements
Error is:
"Message": "invalid input syntax for type json",
"Detail": "Token \"$\" is invalid.”,
"Where": "JSON data, line 1: $...",
"Routine": "report_invalid_token"
AFAIK, you can't interpolate inside strings, but you can build a JSON object using json_build_object
or json_object
:
SELECT data FROM test
WHERE data @> json_build_object('stringdata', $1::text, 'foo', $2::int)::jsonb;
This is basically an equivalent of
SELECT data FROM test
WHERE data @> '{"stringdata":"$1","foo":$2}';