如何用SQL查询CONTENT全为JSON格式的DATE_CREATED日期？

数据库里的CLOB字段CONTENT原本是CSV格式，后来切换成了JSON，但没留下切换时间的记录。有些DATE_CREATED日期的记录里，既有旧的CSV/XML格式，也有被更新成JSON的记录，没法靠排序确定切换时间。现在要写SQL，找出所有记录的CONTENT都是JSON格式的DATE_CREATED日期对应的全部记录，用来定位切换发生的日期范围。

示例输入数据 #

DATE_CREATED | DATE_UPDATED | CONTENT
01-APR-12      01-APR-12      <XML STUFF>
01-APR-12      01-APR-12      <XML STUFF>
01-APR-12      21-JUN-20      {JSON STUFF}
05-APR-12      05-APR-12      <XML STUFF>
01-APR-14      08-MAR-20      {JSON STUFF}
01-APR-16      21-JUN-21      <XML STUFF>
01-APR-14      11-SEP-22      {JSON STUFF}
01-APR-14      01-JAN-21      {JSON STUFF}
01-APR-17      21-JUN-19      <XML STUFF>
11-FEB-23      11-FEB-20      {JSON STUFF}
11-FEB-23      11-FEB-20      {JSON STUFF}
11-FEB-23      11-FEB-20      {JSON STUFF}

解决方案 #

核心思路是先筛选出没有非JSON记录的DATE_CREATED日期，再基于这些日期取出原表中的完整记录。

最终SQL语句 #

SELECT t.DATE_CREATED, t.DATE_UPDATED, t.CONTENT
FROM your_table_name t
JOIN (
    -- 先找出所有记录都是JSON的DATE_CREATED日期
    SELECT DATE_CREATED
    FROM your_table_name
    GROUP BY DATE_CREATED
    -- 统计该日期下非JSON记录的数量，等于0说明全是JSON
    HAVING COUNT(CASE WHEN SUBSTR(CONTENT, 1, 1) != '{' THEN 1 END) = 0
) valid_dates ON t.DATE_CREATED = valid_dates.DATE_CREATED;

细节调整说明 #

• 这里用SUBSTR(CONTENT, 1, 1) != '{'判断非JSON，是基于示例中JSON以{开头、XML以<开头的特征。如果实际格式判断逻辑不同，可以替换：
  • 比如用正则匹配JSON结构：Oracle可以用REGEXP_LIKE(CONTENT, '^\{.*\}$')，MySQL用CONTENT REGEXP '^\{.*\}$'
  • 如果是CLOB类型，部分数据库需要先转换为字符串，比如Oracle用DBMS_LOB.SUBSTR(CONTENT, 1, 1)替代SUBSTR

示例输出结果 #

DATE_CREATED | DATE_UPDATED | CONTENT
11-FEB-23      11-FEB-20      {JSON STUFF}
11-FEB-23      11-FEB-20      {JSON STUFF}
11-FEB-23      11-FEB-20      {JSON STUFF}

内容的提问来源于stack exchange，提问作者edjm