SteemSQL 教程 - 如何使用 ISJSON 和 JSON_VALUE 函数？

MSSQL Database

我的机器人每日前30名排行通过以下MSSQL来查询 STEEMSQL Comments 表中标签中有 cn 或者 china 的帖子或者评论:

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

主标签在 category 字段里定义, 但是其它标签则被保存于JSON格式的字符串, 所以我们需要通过 JSON_VALUE 这个函数来解析JSON, 有时候当被解析的字符串是空或者无效时, SQL就出错了:

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

最简单的解决方法就是通过布尔短路, 在 JSON_VALUE 前加入有效性判断:

1	ISJSON(json_metadataa) > 0

ISJSON(json_metadataa) > 0

正确的版本:

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

如果我们把 ISJSON 放在 JSON_VALUE 之后, 起不到布尔短路的作用, 因为查询还是会对每组数据(包括无效)进行 JSON_VALUE解析.

英文: SteemSQL Tutorial – How to Fix “JSON text is not properly formated. Unexpected character ‘.’ is found at position 0.”?