Can not filter by jsonb property

[SharpBCD]

DB: postgresql, version: latest (just upgraded).
Working SQL:
select * from products where segmentation ->> 'type' = 'WT';
feathers code:

app.service('products').find({
				query: {
					// id: 1008 - this works! so no other issues
					segmentation: {type: "WT"} // returning error
				}
			});

not working with error: 'select count(distinct("products"."id")) as "total" from ' + '"products" where "segmentation" = $1 - invalid input syntax for ' + 'type json',

Other syntax like {type: '"WT"'} return 0 results. Do I do something wrong or there is an issue here?

[SharpBCD]

Note: problem solved. I had to add 'object' in json schema.
That was a quick fix but may I suggest an update on documentation?

[dekelev]

Sure, I'll add an example of setting the JSON schema with object/array properties.

[SharpBCD]

This costed me a few days of frustration. :-)
I knew it has to be something simple. I even tried to define it but as "json" or "jsonb" not as "Object".
It will be really helpful if you write the full example.

[dekelev]

Do you think that quering an object column with a JS object isn't trivial?

I did add example on setting the JSON schema properly, but then again, JSON schema has docs of it's own on how to set types and restrictions properly. There is also a link to Objection.js docs Models section that explains that and refers to the JSON schema docs.

[SharpBCD]

It is trivial after knowing what to do.

I'm aware of the docs for Json, it was just not clear from specs that I have to declare it in order to be able to query. I switched from knex to Objection particularly for this and my previous code was working just fine.

Thanks for the update.

[dekelev]

Thanks for the feedback. I'll add a note on that later.

[SharpBCD]

Please post that example. Maybe because I run an yarn upgrade because I can't really think of any reason, I have the same error again. invalid input syntax for type json started to haunt me.

the error is: message: "select count(distinct("products"."id")) as "total" from "products" where "products"."segmentation"#>'{type}' = $1 - invalid input syntax for type json"

query is: query.segmentation = {type: 'WT' }; and yes, I do have:

				segmentation: {type: 'object'},

			}``` in my product.model.js

So what do I do wrong this time?!

[dekelev]

Not sure. I can check that in few days. Currently on vacation.

[dekelev]

v4.4.1 contains a fix for this issue. the fix restores the use of castText method on references to JSON columns when queried with a comparison operator.

Docs now mentioned that JSON column must be defined in the model class and a query by string example was added to the JSON Column section.