How can I query postgres jsonb column with knex adapter?

[kvagajack]

Steps to reproduce

I have a typebox schema like:

export const itemSchema = Type.Object({
    id: Type.Number(),
    meta_data: Type.Object({
      tie: Type.Optional(Type.String()),
      suit: Type.Optional(Type.String())
    }
})

And I want to query all items that have "meta_data.tie" as "BLUE".

With SQL I would query this like:

Select * from items where meta_data ->>'tie' = 'BLUE';

or

SELECT * FROM items WHERE meta_data @> '{"tie":"BLUE"}';

But I cannot figure out how to build a query in feathers client. Tried the following:

feathersClient.service('items').find({
    query: {
        meta_data: { tie: "BLUE"}

        "meta_data.tie": "BLUE"
        
        "meta_data ->>'tie'": "BLUE"
    }
})

[kvagajack]

If anybody needs this, here is how you can do it:

// Hook:
export function jsonFilter(context: any) {
    // Extract your custom query parameter
    const { jsonFilter } = context.params.query;

    if (jsonFilter) {
        // QueryBuilder_PostgreSQL:
        const query = context.service.createQuery(context.params)
        jsonFilter.forEach(({column, prop, value}: any) => {
            query.andWhereJsonPath(column, `$.${prop}`, '=', value);
        });
        context.params.knex = query;
    }
}

Add the hook to your db service find method:

before: { find: [ jsonFilter ] }

In UI pass the custom query option:

const jsonFilter = [
    {column: "meta_data", prop: "tie", value: "BLACK"},
    {column: "meta_data", prop: "suit", value: "GREEN"},
];
feathersClient.service("items").find({
    query: {
      $limit: 40,
      jsonFilter
    }
});

Links:

• Knex docs: https://knexjs.org/guide/query-builder.html#wherejsonpath
• Feathers: https://feathersjs.com/api/databases/knex.html#createquery-params