Slonik: How to safely query a JSONB object using `JSONB_PATH_MATCH()`?

I have aggregated JSONB objects in a column of a PostgreSQL table that looks similar to this:

  id  | agg_jsonb_objects
------+---------------------------------------------------------
   1  | {"recordA": {"fieldA": "A"}, "recordB": {"fieldA": "B"}}

Next, I'd like to find all rows where any "fieldA" has value "B", which I could do as follows:

SELECT id
FROM ...
WHERE JSONB_PATH_MATCH(agg_jsonb_objects, 'exists($ ?(@.**.fieldA == "B"))')

This works well in the terminal, but I'm struggling to put together safe query in the server, which uses Slonik to build queries.

I get it to work using raw() but that's not acceptable as it's unsafe.

const filterValue = 'B';
sql`
    SELECT id
    FROM ...
    WHERE JSONB_PATH_MATCH(${sql.identifier(['agg_jsonb_objects'])}, 'exists($ ?(@.**.fieldA == ${raw(filterValue)}))')
`;
// Works.

Ideally, I'd like to do something like this but I'm get the following error:

const filterValue = 'B';
sql`
    SELECT id
    FROM ...
    WHERE JSONB_PATH_MATCH(${sql.identifier(['agg_jsonb_objects'])}, 'exists($ ?(@.**.fieldA == ${filterValue}))')
`;
// could not determine data type of parameter $1

Am I just doing something wrong with the syntax or is there a fundamentally different (and better) way to do this with Slonik?

EDIT1: Following @Bergi's advice, I changed it to the following, which does appear to work:

const filterValue = 'B';
const valueForJsonbPathMatchExpression = `exists($ ?(@.**.fieldA == "${filterValue}"))`;
sql`
    SELECT id
    FROM ...
    WHERE JSONB_PATH_MATCH(${sql.identifier(['agg_jsonb_objects'])}, ${valueForJsonbPathMatchExpression})
`;
// Works.

Clear step forward, thanks a bunch! Still requires some manual preparation, e.g. adding quotes for strings but not numeric values and adjusting comparator for greater/lesser than queries. I wonder if that can be simplified?

Solution

There's two levels of escaping that you need to consider when building this query. First, there's the JSON string literal within the JSON path expression. Second, there's the SQL jsonpath value that needs to be formatted as a literal or be passed via parameter.

You'd use JSON.stringify for the former, and Slonik interpolation to generate a parameterised query for the latter, and write

sql`
    SELECT id
    FROM ...
    WHERE JSONB_PATH_MATCH(
        ${ sql.identifier(['agg_jsonb_objects']) },
        ${ 'exists($ ?(@.**.fieldA == ' + JSON.stringify(filterValue) + '))' }
    )
`;

Alternatively, you can pass the filterValue as part of a vars object to jsonb_path_match, and reference it from a static JSON path expression:

sql`
    SELECT id
    FROM ...
    WHERE JSONB_PATH_MATCH(
        ${ sql.identifier(['agg_jsonb_objects']) },
        'exists($ ?(@.**.fieldA == $filterValue))',
        ${ sql.jsonb({ filterValue }) }
    )
`;

Btw, I recommend to use a strict JSON path expression when working with **, and you can simplify the whole expression to just $.**.fieldA == $filterValue.