SteemSQL Tutorial - How to Fix &;;;JSON text is not properly formated. Unexpected character '.' is found at position 0.&;;;?

MSSQL Database

I have a SQL (mssql) to fetch the data from Comments table thanks to STEEMSQL, like this:

select top 10 
    title
from
    Comments (NOLOCK)
where   
    (category='cn') or        
        (    
        (
             ( JSON_VALUE(json_metadata,'$.tags[4]') in ('cn','china') ) or   
             ( JSON_VALUE(json_metadata,'$.tags[3]') in ('cn','china') ) or   
             ( JSON_VALUE(json_metadata,'$.tags[2]') in ('cn','china') ) or   
             ( JSON_VALUE(json_metadata,'$.tags[1]') in ('cn','china') ) or   
             ( JSON_VALUE(json_metadata,'$.tags[0]') in ('cn','china') )
        )
      )
order by 
    created desc

select top 10 
    title
from
    Comments (NOLOCK)
where   
    (category='cn') or        
        (    
        (
             ( JSON_VALUE(json_metadata,'$.tags[4]') in ('cn','china') ) or   
             ( JSON_VALUE(json_metadata,'$.tags[3]') in ('cn','china') ) or   
             ( JSON_VALUE(json_metadata,'$.tags[2]') in ('cn','china') ) or   
             ( JSON_VALUE(json_metadata,'$.tags[1]') in ('cn','china') ) or   
             ( JSON_VALUE(json_metadata,'$.tags[0]') in ('cn','china') )
        )
      )
order by 
    created desc

It is supposed to return all posts (including comments) that are tagged ‘cn’ in one of the tags. The field category is the first tag (main tag). However, in some cases, for example, when the tag string is empty for comments, the SQL failed:

Error 13609: JSON text is not properly formatted. Unexpected character ‘.’ is found at position 0.

The easy fix is to add the following test to avoid evaluating the invalid JSON string via JSON_VALUE function.

1	ISJSON(json_metadataa) > 0

ISJSON(json_metadataa) > 0

The query becomes:

select top 10 
    title
from
    Comments (NOLOCK)
where   
    (category='cn') or        
        (    
        (ISJSON(json_metadataa) > 0) and
        (
             ( JSON_VALUE(json_metadata,'$.tags[4]') in ('cn','china') ) or   
             ( JSON_VALUE(json_metadata,'$.tags[3]') in ('cn','china') ) or   
             ( JSON_VALUE(json_metadata,'$.tags[2]') in ('cn','china') ) or   
             ( JSON_VALUE(json_metadata,'$.tags[1]') in ('cn','china') ) or   
             ( JSON_VALUE(json_metadata,'$.tags[0]') in ('cn','china') )
        )
      )
order by 
    created desc

select top 10 
    title
from
    Comments (NOLOCK)
where   
    (category='cn') or        
        (    
        (ISJSON(json_metadataa) > 0) and
        (
             ( JSON_VALUE(json_metadata,'$.tags[4]') in ('cn','china') ) or   
             ( JSON_VALUE(json_metadata,'$.tags[3]') in ('cn','china') ) or   
             ( JSON_VALUE(json_metadata,'$.tags[2]') in ('cn','china') ) or   
             ( JSON_VALUE(json_metadata,'$.tags[1]') in ('cn','china') ) or   
             ( JSON_VALUE(json_metadata,'$.tags[0]') in ('cn','china') )
        )
      )
order by 
    created desc

Make sure ISJSON is placed before JSON_VALUE to allow boolean shortcut evaluation i.e. when ISJSON is false, JSON_VALUE will not be evaluated. If you place JSON_VALUE after, it will still cause the same error.

You may also like: SteemSQL 教程 – 如何使用 ISJSON 和 JSON_VALUE 函数？