How to query jsonb (postgtresql) in feathers/objections ORM?

Question

I read the docs from: https://github.com/feathersjs-ecosystem/feathers-objection Still can not query a jsonb column.

My table contains: id, some_other_fields, segmentation: jsonb Segmentation columns has values like:

{"type": "WT", "group": "D", "style": 880, "design": 1, "subtype": "ABL"}

How can I query with, for example, type = "WT" ? Sql code is:

select *
from products
where segmentation ->> 'type' = 'WT';

but the ORM code:

const query = {             
segmentation: {type:"WT"}
};

it's not working.

All I manage to get was: Bad request.... "segmentation" = $1 - invalid input syntax for type json when playing with syntax or no result at all.

Any ideas?

Answer 1

In case anybody else stumble upon this, the answer is: in your_service_name.model.js you HAVE TO declare json schema with:

properties: {
                your_jsonb_property: {type: 'object'}
            }

Json schema is optional but it won't work without it.

Answer 2

With objection you should tell ORM when referring json attribute inside a column:

.where({'segmentation:type' : 'WT'})

Check

https://vincit.github.io/objection.js/recipes/json-queries.html

and

https://vincit.github.io/objection.js/api/types/#type-fieldexpression