Function radians(jsonb) does not exist-CodePudding

I have a location column of type jsonb in my issue_points table:

{
  "geo": {
    "lat": 57.994434,
    "lng": 78.35716
  },
  "fias": {
    "city": "some city fias code",
    "region": "some region fias code"
  },
  "address": "some address"
}

Using the Laravel DB facade, I want to find pickup points relative to the received coordinates using the Haversine formula:

select * from "issue_points" where acos(
            sin(radians(?))
            * sin(radians(location->'geo'->'lat'))
              cos(radians(?))
            * cos(radians(location->'geo'->'lat'))
            * cos(radians(?)- radians(location->'geo'->'lng'))
        ) * 6371 <= 20 order by "updated_at" desc

The problem is that if I want to get lat or lng using location->'geo'->'lat', the following postgresql exception is thrown:

"SQLSTATE[42883]: Undefined function: 7 ERROR: function radians(jsonb) does not exist\nLINE 3: * sin(radians(location->'geo'->'lat'))\n
^\nHINT: No function matches the given name and argument types. You might need to add explicit type casts. (SQL: select * from "issue_points" where acos(\n sin(radians(55.03441))\n

sin(radians(location->'geo'->'lat'))\n cos(radians(55.03441))\n * cos(radians(location->'geo'->'lat'))\n * cos(radians(73.46783)- radians(location->'geo'->'lng'))\n ) * 6371 <= 20 order by "updated_at" desc)",

p.s If you use atomic fields with scalar data types: lat (float) and lng (float), and try to use the following code, then issue items will return correctly:

select * from "issue_points" where acos(
            sin(radians(?))
            * sin(radians(lat))
              cos(radians(?))
            * cos(radians(lat))
            * cos(radians(?)- radians(lng))
        ) * 6371 <= 20 order by "updated_at" desc

CodePudding user response：

Every operator and function has a type. In the case of the -> you are using it is jsonb => jsonb - it can't be anything else really, your result could be a scalar value, an object, an array, an array of objects contiaining...

So - if you are confident that the values you are extracting will always have the type you expect then you can do: cos(radians( (location->'geo'->'lat')::double precision )).

However, if you do know that your json is always structured like this then there's no reason to make it json is there?