loopback-connector-postgresql: jsonpath in the "where" condition

Question

The model is stored in postgresql. Something like:

{
    id: <serial>
    data: <json> {
        someIds: [<int>, ...]
    }
}

How to add a rule jsonb_path_match(data::jsonb, 'exists($.someIds[*] ? (@ == 3))') to the filter (where)? In this case, the value '3' '(@ == 3)' shall be determined by the user.

Answer 1

loopback-connector-postgresql does not support JSON/JSONB datatype yet. There is a pull request opened to contribute such feature, but it was never finished by the author - see #401.

As a workaround, you can execute a custom SQL query to perform jsonb_patch_match-based search of your data.

Instructions for LoopBack 3: https://loopback.io/doc/en/lb3/Executing-native-SQL.html

dataSource.connector.execute(sql_stmt, params, callback);

Instructions for LoopBack 4: https://loopback.io/doc/en/lb4/apidocs.repository.defaultcrudrepository.execute.html

const result = await modelRepository.execute(sql_stmt, params, options);