Unable to extract data from JSON in Snowflake

Question

I have a JSON data as below

    [
  "{\"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\"}"
]

I am using the below query by the result I am getting is 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

Both the cat and cat1 are resulting as NULL

Answer 1

Not sure why cat1 is Null but the JSON_EXTRACT_PATH_TEXT returns null because this is an array. You would need to specify an array index for it to extract the JSON at that slot of the array.

EDITED: to include index column from flatten to specify the array index for JSON_EXTRACT_PATH_TEXT.

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;

CAT	CAT1
a	{"category":"a"}
b	{"category":"b"}
c	{"category":"c"}
d	{"category":"d"}
e	{"category":"e"}
f	{"category":"f"}
g	{"category":"g"}
h	{"category":"h"}
i	{"category":"i"}
j	{"category":"j"}
k	{"category":"k"}
l	{"category":"l"}
m	{"category":"m"}
n	{"category":"n"}
o	{"category":"o"}
p	{"category":"p"}
q	{"category":"q"}