System.FormatException: 24141: A number is expected at position 47 of the input. The input has )

I have a table in sqlserverdb which contains column name EntityType, EntityJson.In EntityJson column contains following json value,

{"EntityId":{"Type":"String","Value":"bf58bbc6-0a5a-b9e0-4fb6-5e55604a1b68"},"Name":{"Type":"string","Value":"GasAssets"},"TenantName":{"Type":"string","Value":"v2rdemo"},"TenantId":{"Type":"string","Value":"c091e548-b45a-49b4-b8ec-2cb5e27c7af6"},"EntityType":{"Type":"string","Value":"EntityAssets"},"CreatedBy":{"Type":"string","Value":""},"ModifiedBy":{"Type":"string","Value":""},"Created":{"Type":"string","Value":""},"Modified":{"Type":"string","Value":""},"Parent":{"Type":"string","Value":""},"LastChat":{"Type":"string","Value":""},"Latitude":{"Type":"string","Value":-33.3479019999504},"Longitude":{"Type":"string","Value":6.203906305532271}}

and EntityType column value is "EntityAssets".

On GeoServer I have written the following query

SELECT
  geometry::STGeomFromText('POINT(' + CAST(JSON_VALUE(EntityJson, '$.Latitude.Value') As CHAR(20)) + ' ' + CAST(JSON_VALUE(EntityJson, '$.Longitude.Value') AS CHAR(20)) + ')', 4326) as geometria 
FROM
   dbo.Entities 
where
   EntityType = 'EntityAssets'

While previewing layer on click openlayer I'm getting same exception.

But Inside Microsoft Sql Server Mgmt Studio running it's working fine with where clause and if i remove where clause and Execute again Query without where clause getting same Exception on Sql Server Mgmt studio.

A number is expected at position 47 of the input. The input has ).

Solution

It's hard to be sure without seeing your data, but the error sounds like you have a row with no or empty Latitude.Value and Longitude.Value. You will get a very similar error by running this query:

SELECT
  geometry::STGeomFromText('POINT(' + '' + ' ' + '' + ')', 4326) as geometria 
FROM
   dbo.Entities

This simulates your two CASTs returning empty strings.