时间戳

首页 > 解决方案 > 像 Snowflake 中的 VARCHAR 列一样解析 JSON

sql - 像 Snowflake 中的 VARCHAR 列一样解析 JSON

问题描述

我有一个TAG在 SQL 数据库中调用的 VARCHAR 列，其数据如下例所示。

示例：

{
  "list":[
     {
        "item":{
           "id":"aa1212dg3232",
           "name":"Joe Doe",
           "postScore":9000,
           "url":”google.com"
        }
     },
     {
        "item":{
           "id":"1111jjdjdjsdj11",
           "name":"Jane Doe",
           "postScore":12222,
           "url":"yahoo.com"
        }
     }
  ]
}

由于它不是 JSON 列，因此我很难解析该列以获取 postScore 值。

我试图将其从 a 转换VARCHAR为Variant数据类型，以便我可以执行以下操作来获取

SELECT d.TAGS['list']['item']['postScore']::string as PostScore
FROM data d;

任何有关我如何从这个 Varchar 列中获取 postScore 值的帮助将不胜感激！

标签： sqlsnowflake-cloud-data-platform

解决方案

使用 CTE 作为示例输入：

WITH x AS (
  SELECT '{
  "list":[
     {
        "item":{
           "id":"aa1212dg3232",
           "name":"Joe Doe",
           "postScore":9000,
           "url":"google.com"
        }
     },
     {
        "item":{
           "id":"1111jjdjdjsdj11",
           "name":"Jane Doe",
           "postScore":12222,
           "url":"yahoo.com"
        }
     }
  ]
  }' as str
)

您只需要在将字符串解析为 JSON 后展平数组：

SELECT y.value:item:postScore::varchar
FROM x,
LATERAL FLATTEN(input=>parse_json(str):list) y;

推荐阅读