JSON query returns data until I search for it

Question

I have a table (code_table) with a lot of different types of records and values. One of the type of records contains a json field (but not all). The following returns the records with json fields:

select code_data from code_table where code_group='pptoken' 
and code_data like '{%' 
and ISJSON(code_data)=1

The query returns:

{"orderID":"8L076682TT5438735","subscriptionID":"I-AMJM4M6AX3RP","facilitatorAccessToken":"A21AALhD1svm_MsezAsaFxlEPOGNquXq8YOB3LC8PZ1RTPKr"}

However, if I add the value I'm actually searching for, it doesn't work:

select code_data from code_table where code_group='pptoken' 
and code_data like '{%' 
and ISJSON(code_data)=1
and JSON_VALUE(code_data,'$.subscriptionID') = 'I-AMJM4M6AX3RP'

Returns:

Msg 13609, Level 16, State 1, Line 21
JSON text is not properly formatted. Unexpected character '1' is found at position 0.

So... I don't understand what's going on. Why am I getting that error?

Answer 1

The optimizer can often rearrange WHERE predicates, so JSON_VALUE is done before ISJSON.

Do not do what the other answer says, to put it into a CTE. This often doesn't work either, as the optimizer will combine it all back into one query anyway.

Instead use CASE (and derivatives such as NULLIF and IIF), which is the only construct guaranteed to not short-circuit (at least when only scalar values are involved as opposed to aggregate functions).

select
  code_data
from code_table
where code_group = 'pptoken' 
  and code_data like '{%' 
  and ISJSON(code_data) = 1
  and JSON_VALUE(
    CASE WHEN ISJSON(code_data) = 1 THEN code_data END,
    '$.subscriptionID'
  ) = 'I-AMJM4M6AX3RP';

A slightly different syntax, may be more efficient

  and CASE WHEN ISJSON(code_data) = 1 THEN
       JSON_VALUE(code_data, '$.subscriptionID')
      END = 'I-AMJM4M6AX3RP'

Answer 2

JSON_VALUE() is exploding trying to run on the non-json rows.

Use a CASE to prevent JSON_VALUE()being executed over non-json values:

select code_data
from code_table
where code_group = 'pptoken' 
and code_data like '{%'
and case ISJSON(code_data) when 1 then JSON_VALUE(code_data,'$.subscriptionID') end = 'I-AMJM4M6AX3RP'

The case will return null for non-json values (which won't be equal to the search value).