.equalTo no working in string Arrays when using PostgreSQL

[strom-und-spiele]

New Issue Checklist

• I am not disclosing a vulnerability.
• I am not just asking a question.
• I have searched through existing issues.
• I can reproduce the issue with the latest version of Parse Server.

Issue Description

When using equalTo on an Array of Strings with a PostgreSQL DB, an error gets thrown.
Might be related to #6403
Seems to be an encoding issue for Postegres queries.

Steps to reproduce

1. Set up Parse with PostgreSQL
2. Add a new Class with an Array Field and add strings (it's stored as jsonb on the db)
3. Try to access it via equalTo

Using parse/node

"use strict";
const Parse = require("parse/node");

Parse.initialize("appId");
Parse.serverURL = "http://localhost:1337/parse/";

Parse.Config.get()
  .then((config) => {
    console.debug(`Parse API Connection established.`);
  })
  .catch(console.error)
  .then(() => new Parse.Query("testClass"))
  .then((qry) => qry.get("bPWn1BIwN3"))
  .then((res) => console.log("actual data:", res.get("stringArray")))
  .then(() => new Parse.Query("testClass"))
  .then((qry) => {
    qry.equalTo("stringArray", "stringOne");
    return qry.find();
  })
  .then(() => console.log("will not be triggered"))
  .catch((err) => console.error("we fail with:", err));

$ npm run start

> [email protected] start
> node ./index.js

Parse API Connection established.
actual data: [ 'stringOne', 'stringTwo' ]
we fail with: [ParseError: [object Object]
    at handleError (/home/bastian/datenhalde/pgTest/node_modules/parse/lib/node/RESTController.js:426:17)
    at processTicksAndRejections (node:internal/process/task_queues:94:5)] {
  code: 1
}

on the Parse Dashboard:

const qry = new Parse.Query("test")
qry.equalTo("stringArray", "stringOne");
console.log(await qry.find());  

Output:

{
  "message": {
    "length": 165,
    "name": "error",
    "severity": "ERROR",
    "code": "22P02",
    "detail": "Token \"stringOne\" is invalid.",
    "position": "44",
    "where": "JSON data, line 1: stringOne",
    "file": "jsonfuncs.c",
    "line": "617",
    "routine": "json_ereport_error"
  },
  "name": "Error",
  "stack": "s@http://0.0.0.0:4040/bundles/dashboard.bundle.js:75:630545\nt@http://0.0.0.0:4040/bundles/dashboard.bundle.js:75:624686\nd</n<@http://0.0.0.0:4040/bundles/dashboard.bundle.js:16:97361\nr@http://0.0.0.0:4040/bundles/dashboard.bundle.js:16:97493\nhandleError@http://0.0.0.0:4040/bundles/dashboard.bundle.js:75:674336\n"
}

using

Server

• Parse Server version: 4.5.0
• Operating system: 5.10.23-1-MANJARO (localhost)
• Local or remote host (AWS, Azure, Google Cloud, Heroku, Digital Ocean, etc): localhost

Database

• System (MongoDB or Postgres): Postgres
• Database version: 13
• Local or remote host (MongoDB Atlas, mLab, AWS, Azure, Google Cloud, etc): localhost

Client

• SDK (iOS, Android, JavaScript, PHP, Unity, etc): parse/node / parse dashboard
• SDK version: 3.1 / 2.1.0

Logs

Parse Server Logs:

verbose: REQUEST for [GET] /parse/classes/test: {
  "where": {
    "stringArray": "stringOne"
  }
} {"method":"GET","url":"/parse/classes/test","headers":{"user-agent":"node-XMLHttpRequest, Parse/js3.1.0 (NodeJS 15.11.0)","accept":"*/*","content-type":"text/plain","host":"localhost:1337","content-length":"197","connection":"close"},"body":{"where":{"stringArray":"stringOne"}}}
error: Parse error:  error: invalid input syntax for type json {"code":1,"stack":"Error: error: invalid input syntax for type json\n    at /path/to/local/parse/node_modules/parse-server/lib/Controllers/DatabaseController.js:1170:21\n    at processTicksAndRejections (node:internal/process/task_queues:94:5)"}

[mtrezza]

Thanks for reporting.

I classified this as a bug. To help determine the severity of this bug:

• Is there a known workaround until this bug is fixed?

[strom-und-spiele]

Thanks for the update.

The only workaround I found was going back to MongoDB. However, as the last MongoDB that has a usable license is soon at its EOL, this is only a temporal fix.

[mtrezza]

However, as the last MongoDB that has a usable license is soon at its EOL, this is only a temporal fix.

Can you explain further?

[strom-und-spiele]

(Just noticed that my answer somehow wasn't saved.)

Mongo v3.6 will expire in April. Starting with v4.0 its licence changes so that

A company that offers a publicly available MongoDB as a service must release the software it uses to offer such service under the terms of the SSPL, including the management software, user interfaces, application program interfaces, automation software, monitoring software, backup software, storage software and hosting software, all such that a user could run an instance of the service using the source code made available.

It's not really clear where "public available MonogoDB aaS" starts and ends (I'd argue that parse with mongo is a a MongoDB aaS). Therefore I (and many others) will avoid MongoDB >= v4.

[mtrezza]

Parse Server does not fall into this category, it is not a DBaaS, nor are apps built with Parse Server, unless the app is a DBaaS product.

An example of MongoDB aaS would be ObjectRocket or the former mLab. These license changes have their roots in several historic precedents in the open source ecosystem, where cloud service providers offer open sourced software for-profit without contributing back to the original open source project. A recent example has been the discussion around Elastic Search offered by cloud service providers.

You may inquire directly with MongoDB whether your product falls into that category or not.

Disclaimer: this is a personal opinion and does not constitute legal advice or an official statement from the Parse Platform Organization.

[mtrezza]

On a separate note I should mention that the next version of Parse Server will naturally drop support for MongoDB 3.6 as it reached its EoL. You may want to adapt your app to MongoDB >= 4.0 rather sooner than later.

[strom-und-spiele]

Is there a known workaround until this bug is fixed?
As I have a scenario in which I want to run the setup on a Debain machine which won't feature mongo in the default packages, I decided to use Relations. As I'm only operating on <1000 datasets this is a usable but limited workaround.

[strom-und-spiele]

Better workaround:
use .containsAll("listField", [singleEntry]) instead of .equalTo("listField", singleEntry)

(I just stumpled over this in the docs. Sorry for not investigating further)

[mtrezza]

Could you please post a link to the specific section in the docs?