Preprocessing failed for performance counters zabbix, MSSQL server

Question

I've connected my MSSQL server to zabbix via ODBC.

I'm receiving results for some items but for other items errors are returned:

Preprocessing failed for: [{"object_name":"SQLServer:Buffer Manager","cntr_value":"4947","counter_name":"Background writer ...
1. Failed: cannot extract value from json by path "$[?(@.counter_name=='BufferCacheHitRatio')].cntr_value.first()": no data matches the specified path

image showing error1: https://i.sstatic.net/ZhrNL.png"

For these 3 metrics "[metrics concerned][2][2]: https://i.sstatic.net/stMjX.png"

aka: CacheHitRatio, WorktablesFromCacheRatio and BufferCacheHitRatio, which are being pulled with the item: MSSQL: Get performance counters this item uses this SQL Request to pull the performance counter metrics:

SELECT object_name,
  counter_name,
  instance_name,
  cntr_value
FROM sys.dm_os_performance_counters
UNION SELECT 'MSSQL$' + @@servicename AS object_name,
  'Version' AS counter_name,
  @@version AS instance_name,
  0 AS cntr_value
UNION SELECT 'MSSQL$' + @@servicename AS object_name,
  'Uptime' AS counter_name,
  '' AS instance_name,
  DATEDIFF(second, sqlserver_start_time, GETDATE()) AS cntr_value
FROM sys.dm_os_sys_info
UNION SELECT 'MSSQL$' + @@servicename + ':Databases' AS object_name,
  'State' AS counter_name,
  name AS instance_name,
  state AS cntr_value
FROM sys.databases
UNION SELECT a.object_name,
  'BufferCacheHitRatio' AS counter_name,
  '' AS instance_name,
  cast(a.cntr_value * ISNULL((100.0 / NULLIF(b.cntr_value,0)),0) AS dec(3, 0)) AS cntr_value
FROM sys.dm_os_performance_counters a
JOIN (
  SELECT cntr_value,
    OBJECT_NAME
  FROM sys.dm_os_performance_counters
  WHERE counter_name = 'Buffer cache hit ratio base'
    AND OBJECT_NAME = 'MSSQL$' + @@servicename + ':Buffer Manager'
) b
  ON a.OBJECT_NAME = b.OBJECT_NAME
WHERE a.counter_name = 'Buffer cache hit ratio'
  AND a.OBJECT_NAME = 'MSSQL$' + @@servicename + ':Buffer Manager'
UNION SELECT a.object_name,
  'WorktablesFromCacheRatio' AS counter_name,
  '' AS instance_name,
  cast(a.cntr_value * ISNULL((100.0 / NULLIF(b.cntr_value,0)),0) AS dec(3, 0)) AS cntr_value
FROM sys.dm_os_performance_counters a
JOIN (
  SELECT cntr_value,
    OBJECT_NAME
  FROM sys.dm_os_performance_counters
  WHERE counter_name = 'Worktables From Cache Base'
    AND OBJECT_NAME = 'MSSQL$' + @@servicename + ':Access Methods'
) b
  ON a.OBJECT_NAME = b.OBJECT_NAME
WHERE a.counter_name = 'Worktables From Cache Ratio'
  AND a.OBJECT_NAME = 'MSSQL$' + @@servicename + ':Access Methods'
UNION SELECT a.object_name,
  'CacheHitRatio' AS counter_name,
  '_Total' AS instance_name,
  cast(a.cntr_value * ISNULL((100.0 / NULLIF(b.cntr_value,0)),0) AS dec(3, 0)) AS cntr_value
FROM sys.dm_os_performance_counters a
JOIN (
  SELECT cntr_value,
    OBJECT_NAME
  FROM sys.dm_os_performance_counters
  WHERE counter_name = 'Cache Hit Ratio base'
    AND OBJECT_NAME = 'MSSQL$' + @@servicename + ':Plan Cache'
    AND instance_name = '_Total'
) b
  ON a.OBJECT_NAME = b.OBJECT_NAME
WHERE a.counter_name = 'Cache Hit Ratio'
  AND a.OBJECT_NAME = 'MSSQL$' + @@servicename + ':Plan Cache'
  AND instance_name = '_Total'

I have no clue what's wrong, note that when i log into my user with ODBC and isql and try to get one of those counter metrics such as the hit ratio one, I'm successful. result of isqli

so the issue is definitely not from the user. I also enabled the Pollers for ODBC inside the zabbix config file and set it to 5 then restarted zabbix. I don't know what could be the issue, any help would be appreciated. The json preprocessing step being used in zabbix's item is "$[?(@.counter_name=='BufferCacheHitRatio')].cntr_value.first()"