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?

SQL Server does not necessarily apply predicates in the order you specify, it will likely be evaluating the `json_value` function *before* filtering with `isjson`. Try using a row-goal in a sub-query. — Stu, Jul 11 '23 at 16:50

Charlieface · Accepted Answer · 2023-07-12T13:13:22.893

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'

Is there a need for 2x `ISJSON(code_data) = 1`, or did that slip in by accident? — Peter B, Jul 12 '23 at 00:04
Yes, I think it's probably best to have a separate filter as well. Given that it's a deterministic function, I would imagine there wouldn't be a perf impact — Charlieface, Jul 12 '23 at 01:03

Bohemian · Answer 2 · 2023-07-11T23:51:10.097

1

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).

edited Jul 11 '23 at 23:51

answered Jul 11 '23 at 16:55

Bohemian

412,405
93
575
722

1

That isn't guaranteed to work either I think. Best is to use a case when – siggemannen Jul 11 '23 at 17:00
@sig In what situation would this not work? – Bohemian Jul 11 '23 at 17:04
The query optimizer can still perform expansion on the sub-query table expression just like it can with a CTE; using a row-goal however should work, that will force the optimizer to process the rows in the subquery first; possibly a NOEXPAND hint might work, I have never tried. – Stu Jul 11 '23 at 17:16
I suspect the statement can be unwrapped or at least the possibility would make me consider a case when – siggemannen Jul 11 '23 at 18:32
The optimizer isn't stupid, and will just combine this back into one query, so you haven't gained at all. The only way is to use `CASE` – Charlieface Jul 11 '23 at 20:39
@stu fair enough. `case` applied looks simple enough (and easy to read). – Bohemian Jul 11 '23 at 23:52
@sig good point. hopefully looks better now. – Bohemian Jul 11 '23 at 23:52
1

It's even possible to use `and IIF(ISJSON(code_data)=1, JSON_VALUE(code_data,'$.subscriptionID'), NULL) = 'I-AMJM4M6AX3RP'`. IIF is equivalent to a CASE WHEN with 1 condition, and a bit less verbose. – Peter B Jul 12 '23 at 00:04
Doesnt iif evaluates both arguments before going? Or am I confusing it with how vb6 did it – siggemannen Jul 12 '23 at 10:06
1

@siggemannen No, it translates to the same execution plan as a CASE WHEN with 1 condition and THEN + ELSE parts. `IIF(4>4, 3/0, 2)` returns `2` and not a Divide by zero error. – Peter B Jul 12 '23 at 18:47

JSON query returns data until I search for it

2 Answers2