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

Dave Welden · Accepted Answer · 2023-11-17 20:08:22Z

2

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

edited Nov 17, 2023 at 20:08

answered Nov 16, 2023 at 14:17

Dave Welden

1,9791 gold badge10 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Yousuf Sultan Over a year ago

Thanks Dave. your query returns only 1 record. However I am expecting the number of records to be same as the no. of items in the underlying JSON.

Dave Welden Over a year ago

The output above is from the query. One line per category a-q using prop.value::string as cat1 from the lateral flatten. The JSON_EXTRACT_PATH_TEXT does not return the different categories as I only specified the first array element to demonstrate that it is an array.

Dave Welden Over a year ago

The query above is returning 17 rows. It is updated to use the index column from the lateral flatten to select from the array.

Yousuf Sultan Over a year ago

Cool Dave. This works exactly as my expectation.

Collectives™ on Stack Overflow

Unable to extract data from JSON in Snowflake

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related