array searches produce broken SQL (even with arrayFields)

[mattbishop]

I have table with a 'data' JSONB column that has an object like this:

{ "players": [ "Mark", "Matt", "Minhwa", "Melinda" ] }

Instead of using $in, I am using a query like this:

const sql = mongoToPostgres("data", {players: ["Matt", "Melinda"]}, ["players"])

The resulting SQL looks like this:

(data @> '{ "players": Matt,Melinda }' OR EXISTS (SELECT * FROM jsonb_array_elements(data->'players') WHERE jsonb_typeof(data->'players')='array' AND value @> '["Matt","Melinda"]'::jsonb))

Postgres throws an error:

invalid input syntax for type json
...
      "name": "error",
      "severity": "ERROR",
      "code": "22P02",
      "detail": "Token \"Matt\" is invalid.",
      "position": "233",
      "where": "JSON data, line 1: { \"players\": Matt...",
      "file": "json.c",
      "line": "1248",
      "routine": "report_invalid_token"

the first part of the SQL should be omitted because it's an array select, which is covered by the EXISTS... statement.

[mattbishop]

Or, the first part fixed to emit a proper JSON array. This query works as expected:

select * from events where (data @> '{ "players": ["Matt","Melinda"] }' OR EXISTS (SELECT * FROM jsonb_array_elements(data->'players') WHERE jsonb_typeof(data->'players')='array' AND value @> '["Matt","Melinda"]'::jsonb))

Expected: data @> '{ "players": ["Matt","Melinda"] }'
Actual: data @> '{ "players": Matt,Melinda }'

[thomas4019]

@mattbishop Thanks for filling this. I think I fixed this and pushed out a new release. Can you try and let me know if it solves your issue?

[mattbishop]

Latest version fixes this for me. Thanks!