Typecasting in postgres does not work for jsonb query with operators

[woltob]

Describe the bug
PostgreSQL CamelCase to snail_case conversion does not work for jsonb query.

Stack trace

InvalidFieldNameException: select "e0".* from "location" as "e0" where "locationGeometry"->'bounds'->'northeast'->>'lat' >= 52.5353 and "locationGeometry"->'bounds'->'northeast'->>'lng' >= 13.4245 and "locationGeometry"->'bounds'->'southwest'->>'lat' <= 52.5353 and "locationGeometry"->'bounds'->'southwest'->>'lng' <= 13.4245 and "e0"."location_type" in (9, 0) and "e0"."deleted_at" is null - column "locationGeometry" does not exist

Field is called locationGeometry in the Entity but location_geometry in the databse.

To Reproduce
Steps to reproduce the behavior:

1. Entity of type JSON for Postgres DB with camelCase name.
2. Trying to search for nested JSON values in the camelCase column using em.find(Entity, {nested json query}

return this.em.find(Location, {
        locationGeometry: {
          bounds: {
            northeast: { lat: { $gte: coords.lat }, lng: { $gte: coords.lng } },
            southwest: { lat: { $lte: coords.lat }, lng: { $lte: coords.lng } },
          },
        },
        locationType: {
          $in: locationTypes,
        },
      });

Expected behavior
Should translate the camelCase column name to snake_case.

Additional context
Will be provided if necessary.

Versions

Dependency	Version
node	?
typescript	^4.2.3
mikro-orm	^4.5.3

Using NestJS and Postgres 13.

"@mikro-orm/cli": "^4.5.3",
"@mikro-orm/core": "^4.5.3",
"@mikro-orm/nestjs": "^4.2.0",
"@mikro-orm/postgresql": "^4.5.3",
"@mikro-orm/reflection": "^4.5.3",

[B4nan]

I believe this should be fixed in 4.5.4, so please try that version.

[woltob]

Amazing. Thank you so much for your awesome work and the quick response!

Nevertheless, the next problem arose:

location_geometry (jsonb, postgres) has the following data:

{
    "bounds": {
        "northeast": {
            "lat": 53.23451180000001,
            "lng": 14.3329223
        },
        "southwest": {
            "lat": 51.53564,
            "lng": 12.3015252
        }
    }
}

However, the above query does the comparison string-wise, yielding the following error:

DriverException: select "e0".* from "location" as "e0" where "location_geometry"->'bounds'->'northeast'->>'lat' >= 52.5353 and "location_geometry"->'bounds'->'northeast'->>'lng' >= 13.4245 and "location_geometry"->'bounds'->'southwest'->>'lat' <= 52.5353 and "location_geometry"->'bounds'->'southwest'->>'lng' <= 13.4245 and "e0"."location_type" in (9, 0) and "e0"."deleted_at" is null - operator does not exist: text >= numeric

So MikroORM is casting the lat and lng values as strings via the jsonb query. How can I solve this?

[B4nan]

Weird, casting is handled in postgres, are you really sure that the type is number? There is a typeof val check.

I guess it might be caused by the usage of operators, can you try strict comparison just to verify that?

[woltob]

No, it's how the query is translated.
Using -> would give an integer. Getting a float (which should be the type, as number can be either int or float) would require casting via the query, as shown here: https://stackoverflow.com/questions/24826385/how-to-convert-postgresql-9-4s-jsonb-type-to-float
See also JSON operators for PostgreSQL: https://www.postgresql.org/docs/13/functions-json.html

[B4nan]

I know how it works, and as I said it is implemented. Afaik -> would actually give you JSON and you would still have to type cast. The ORM handles this, if the value is number, it wil cast it to real.

https://github.com/mikro-orm/mikro-orm/blob/master/packages/postgresql/src/PostgreSqlPlatform.ts#L106

I asked you to verify that it is caused by the operators you are using, so please try whether this produces the type cast:

return this.em.find(Location, {
        locationGeometry: {
          bounds: {
            northeast: { lat: coords.lat },
            southwest: { lat: coords.lat },
          },
        },
        locationType: {
          $in: locationTypes,
        },
      });

[woltob]

Your query translates to this sql query which works:

select "e0".* from "location" as "e0" where ("location_geometry"->'bounds'->'northeast'->>'lat')::float8 = 52.5353 and ("location_geometry"->'bounds'->'southwest'->>'lat')::float8 = 52.5353 and "e0"."location_type" in (9, 0) and "e0"."deleted_at" is null

So it seems like it's caused by the use of the $gte and $lte operators.

[B4nan]

Ooops, wrong issue number, this is not fixed. Will look into it tomorrow. In the mean time you could try using sql fragment:

https://mikro-orm.io/docs/entity-manager/#using-custom-sql-fragments

[B4nan]

4.5.5-dev.2

there were some NPM issues, so its actually 4.5.5-dev.3