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
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"} |