Match array query doesn't work the same as Mongo

[mattbishop]

Mongo has the ability to search for a specific array value, meaning arrays that have the same elements in the same order:

https://docs.mongodb.com/manual/tutorial/query-arrays/#match-an-array

If I run a similar query with mongo-query-to-postgres-jsonb, I get back results that have all of the array elements, but the ordering is irrelevant. Given the Mongo tutorial example, if I have the same data in jsonb tables, then issue this query:

const sql = mongoToPostgres("inventory", { tags: ["red", "blank"] }, ["tags"]);

Instead of getting back a single result, 4 rows are returned, which is the behaviour for the $all operator. I would expect four results for this query:

const sql = mongoToPostgres("inventory", { tags: { "$all": ["red", "blank"] } }, ["tags"]);

[mattbishop]

I don't think Postgres is going to be able to implement the same 'exactly this array' semantics: https://www.postgresql.org/docs/13/datatype-json.html#JSON-CONTAINMENT

[thomas4019]

Ahh, that's interesting. It does look like using the containment operator won't work, but it should be possible using the equality operator, right? Perhaps when matching arrays with arrays, we could change it to not use the the containment. I can investigate more this weekend.

[mattbishop]

I am messing about with how to handle queries like { “an-array-field”: “$elemMatch”: { “$gte”: 4, “$le”: 6 } } and am thinking generating JSONPath queries will be better, since the containment and other operators cannot do this sort of comparison. Reading through https://www.postgresql.org/docs/13/functions-json.html Section "The SQL/JSON Path language"

[mattbishop]

@thomas4019 do you have a minimum Postgres version you want to support?

[thomas4019]

I’m not opinionated on the version. @dannyzaken @romayalon Do you have an opinion about what PostreSQL versions to support?

Depending on the case, it could make sense to the the version as a parameter so the library can do the best possible with the features available

[dannyzaken]

@thomas4019 currently we are using PostgreSQL 12

[mattbishop]

My attempts to use JSON Path mixed with other JSON operators has failed. I don't think it's possible to mix the two.

Might be interesting to consider refactoring this lib to use JSON Path exclusively.

[thomas4019]

@mattbishop Thanks for the update! Can you share an example of a query using both?