Support for filtering jsonb array by contains string (jsonb operators)

[Aerlin]

I was having trouble filtering results by multiple relationship values. I opened a ticket on objection.js asking how to do it. While I wait on that I figured I'd try filtering using a jsonb column instead.

My postgresql table has a jsonb column with a array of strings. I'd like to filter results by checking if the array contains a string.

It works in Objection. Here's what the query looks like.

Article.query()
.where(ref("articles.jsonb_column:tags"), "?", "news")
.then(function(results) {
	console.log("articles: " + JSON.stringify( results , null, "\t"));
});

In Feathers-Objection, I added ? to the OPERATORS_MAP list.

const OPERATORS_MAP = {
	...
	"?": '?'
}

The castText() part in objectify() causes postgres to throw an error.

select count(distinct("articles"."id")) as "total" from "articles" where CAST("articles"."jsonb_column"#>>'{tags}' AS text) 'news' $1 - operator does not exist: text ? unknown

Removing castText() when the operator is ? seems to fix it.

if (columnType === 'object' || columnType === 'array') {
	if (operator == "?") {
		return query.where((0, _objection.ref)(`${this.Model.tableName}.${methodKey || column}:${(methodKey ? column : key).replace(/\(/g, '[').replace(/\)/g, ']')}`), operator, value);
	} else {
		return query.where((0, _objection.ref)(`${this.Model.tableName}.${methodKey || column}:${(methodKey ? column : key).replace(/\(/g, '[').replace(/\)/g, ']')}`).castText(), operator, value);
	}
}

After those two changes, this query works as expected.

app.service('articles').find({ 
	query: { 
		$and: [
			{
				"jsonb_column": { 
					"tags": { "?": "news" }
				}
			},
			{
				"jsonb_column": { 
					"tags": { "?": "weather" }
				}
			},
		]
	} 
}).then(function(results) {
	console.log("articles: " + JSON.stringify( results , null, "\t"));
});

Here's a list of jsonb operators. Hopping you can add support for them. :)
Table 9.44. Additional jsonb Operators

[dekelev]

Thanks @Aerlin , I'll check it out, probably over the weekend

[dekelev]

@Aerlin I've added support for more query operators in v3.2.0

See docs

[Aerlin]

Thank you @dekelev!