Converting raw laravel mysql json query into postgres syntax

Question

I have the following query that works in MYSQL

$query->whereRaw('
    ST_Distance_Sphere(
      point(address->>"$.longitude", address->>"$.latitude"),
      point(?, ?)
    ) * .000621371192 < ?
', [$longitude, $latitude, $distance]);

When i deployed to heroku im getting this error

SQLSTATE[42703]: Undefined column: 7 ERROR: column "$.longitude" does not exist LINE 3: point(address->>"$.longitude", address->>"$.latitu...

Im using postgres on heroku and i know the issue is something to do with the json operator.

What is the $.<json key> syntax called? and how do i convert this raw sql query to postgres?

In the worse case i leaning on switching my database to mysql if i cant solve this by Wednesday as it wont be a big deal

Answer 1

Postgres uses double quotes as delimiter for columns, so you must use single quotes to indicate strings. Laravel will take care of it

st_distance_sphere(
    st_point(address->>'$.longitude',address->>'$.latitude'),st_point(?, ?)
)