snowflake-cloud-data-platformjson-extract

Unable to extract data from JSON in Snowflake


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


Solution

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