Question 205715 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

JSON query returns data until I search for it

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?

Tags:

2

Answers


  1. 0

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

    Use a subquery that retrieves only json rows, then run JSON_VALUE() over that:

    select *
from (
  select code_data
  from code_table
  where code_group = 'pptoken' 
  and code_data like '{%'
  and ISJSON(code_data) = 1
) json_rows
where JSON_VALUE(code_data,'$.subscriptionID') = 'I-AMJM4M6AX3RP'
    Login or Signup to reply.
  2. 0

    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';
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search