无法从 Snowflake 中的 JSON 中提取数据-解网

问：

我有一个JSON数据，如下所示

    [
  "{\"category\":\"a\"}",
  "{\"category\":\"b\"}",
  "{\"category\":\"c\"}",
  "{\"category\":\"d\"}",
  "{\"category\":\"e\"}",
  "{\"category\":\"f\"}",
  "{\"category\":\"g\"}",
  "{\"category\":\"h\"}",
  "{\"category\":\"i\"}",
  "{\"category\":\"j\"}",
  "{\"category\":\"k\"}",
  "{\"category\":\"l\"}",
  "{\"category\":\"m\"}",
  "{\"category\":\"n\"}",
  "{\"category\":\"o\"}",
  "{\"category\":\"p\"}",
  "{\"category\":\"q\"}"
]

我正在使用以下查询，我得到的结果是 NULL

SELECT DISTINCT T_ID,
                ITC,
                JSON_EXTRACT_PATH_TEXT(REPLACE(ITC,'\\',''), 'category') AS cat,
                prop.VALUE::string AS cat1
FROM MDCD.M.T01 a,
     LATERAL FLATTEN(input => a.ITC, outer => true) prop

cat 和 cat1 的结果都是 NULL

snowflake-cloud-data-platform json-extract

create or replace temporary table t0 (cat variant) as
select parse_json(column1) from values
('[{"category":"a"},  {"category":"b"},  {"category":"c"},  {"category":"d"},  {"category":"e"},  {"category":"f"},  {"category":"g"},  {"category":"h"},  {"category":"i"},  {"category":"j"},  {"category":"k"},  {"category":"l"},  {"category":"m"},  {"category":"n"},  {"category":"o"},  {"category":"p"},  {"category":"q"}]');

select 
json_extract_path_text(cat[index], 'category') as cat,
prop.value::string as cat1
from t0,
lateral flatten(input => t0.cat, outer => true) prop;

猫	类别1
一个	{“类别”：“a”}
b	{“类别”：“b”}
c	{“类别”：“c”}
d	{“类别”：“d”}
e	{“类别”：“e”}
f	{“类别”：“f”}
g	{“类别”：“g”}
h	{“类别”：“h”}
我	{“类别”：“我”}
j	{“类别”：“j”}
k	{“类别”：“k”}
l	{“类别”：“l”}
m	{“类别”：“m”}
n	{“类别”：“n”}
o	{“类别”：“o”}
p	{“类别”：“p”}
q	{“类别”：“q”}

无法从 Snowflake 中的 JSON 中提取数据

Unable to extract data from JSON in Snowflake

评论

评论