Convert JSON value by SQL query

Question

I have a JSON data in a field and I want to query base on one of the JSON columns but I receive converting error. I try these method but I receive convert error This is original query:

select * 
from TableName 
where json_value([Data],'$.Group.Field' ) >= 1.1

and this is custom query:

select *  
from TableName 
where cast(JSON_VALUE([Data],'$.Group.Field') as decimal) <> ''

this is another one:

select *  
from TableName 
where convert(decimal, json_value([Data], '$.Group.Field')) > 1.1 

but I get error with all of them:

Error converting data type nvarchar to numeric

this is JSON schema:

{
  "Group": {
    "Field": "1.1"
  },
  "Group2": {
    "Field2": "1.1"
  },
  "Group3": {
    "Field3": "1.1"
  }
}

Answer 1

The return value of JSON_VALUE() is of type nvarchar(4000), so if you want to compare the $.Group.Field part of the stored JSON use TRY_CONVERT() for explicit conversion:

SELECT *  
FROM TableName 
WHERE TRY_CONVERT(numeric(10, 1), JSON_VALUE([Data], '$.Group.Field')) > 1.1 

If you want to parse the whole JSON, the following approach is an option:

Sample data:

SELECT *
INTO TableName
FROM (VALUES
   ('{
  "Group": {
    "Field": "1.2"
  },
  "Group2": {
    "Field2": "x.1"
  },
  "Group3": {
    "Field3": "1.1"
  }
}')
) v (Data)

Statement:

SELECT t.*, j.[key] AS GroupId
FROM TableName t
CROSS APPLY OPENJSON(t.Data) j
WHERE TRY_CONVERT(numeric(10, 1), JSON_VALUE(j.[value], '$.Field')) > 1.1

Result:

Data	GroupId
{"Group": {"Field": "1.2"},"Group2": {"Field2": "x.1"},"Group3": {"Field3": "1.1"}}	Group