pg: Cannot interpolate values into Postgres 14-style JSON queries

[ejmartin504]

Example queries in code, where PrivacyMode is an enum with property public, which evaluates to "public"

Attempt 1 (single and double quotes)

import { sql } from '@databases/pg';

return DB.query(sql`
    SELECT * from users
   WHERE
     privacy['profile'] = '"${sql.value(PrivacyMode.public)}"';
`;

error: "could not determine data type of parameter $1"

Attempt 2 (single quotes)

return DB.query(sql`
    SELECT * from users
   WHERE
     privacy['profile'] = '${sql.value(PrivacyMode.public)}'
`;

error: "invalid input syntax for type json"

Attempt 3 (no quotes):

return DB.query(sql`
    SELECT * from users
   WHERE
     privacy['profile'] = ${sql.value(PrivacyMode.public)}
`;

error: "invalid input syntax for type json"

Attempt 4 (no sql.value):

return DB.query(sql`
    SELECT * from users
   WHERE
     privacy['profile'] = '"${PrivacyMode.public)}"'
`;

error: "You do not need to wrap values in 'quotes' when using @databases. Any JavaScript string passed via ${...} syntax is already treated as a string. Please remove the quotes around this value."

The other options, without sql.value, all fail in similar ways.

The only thing that does work is not interpolating the value:

return DB.query(sql`
    SELECT * from users
   WHERE
     privacy['profile'] = '"public"'
`;