Parsing a column with LIST, String, and NULL

Follow

Anson Chan

• February 02, 2021 10:43

Hello Team,

I am trying to parse a column that contains either a LIST, a NULL, or a String. As you can see it is the policy action that describes an AWS S3 Bucket.

When I do JSON_ELEMENTS on the column many rows (both NULL, and single String) will disappear.

When I do JSON_ELEMENT I will have to manually define and the values became separate columns.

I would like to have the list be splitted in the same column (different rows) rather than split string in columns.

Can I please have your advice? what is the best practice to deal with this scenario?

Thanks, 

anson

 

0

• 

  Konsta Danyliuk

  • February 02, 2021 14:12

  Hello Anson.
  I hope you are doing fine and staying safe these days.
  
  I guess we would need more details about this use-case to be able to suggest the right approach.

  • As far as I understand, the data type of the policyStatementAction column is STRING, isn't it?
  • What is the final goal for the transformation? Do you need to have a list of policy elements in a single column, so you can use each element separately for further analysis?

  0

  Comment actions Permalink
• 

  Anson Chan

  • February 03, 2021 00:36

  Hello Konsta,

  Yes. you are correct. the data type of the policyStatementAction column is STRING

  The final goal for the transformation is to have the list of policy elements in a single column.

  Hope you are doing fine and safe as well.

  Thanks,

  anson

  0

  Comment actions Permalink
• 

  Konsta Danyliuk

  • February 03, 2021 07:36
  • Edited

  Hello Anson.
  In this case, I would suggest the following approach.

  • Remove brackets and quotes from the initial string via the REPLACEALL function. This gives you the string with comma-separated values (\u0022 is a Unicode for the quote character).
    

    REPLACEALL(REPLACEALL(REPLACEALL(#policyStatementAction; "\\["; ""); "\\]"; ""); "\\u0022"; "")

  • Use the TOKENIZELIST function with a comma as a separator to make a list out of the string.
    
    

  I hope this is what you are looking for.

  0

  Comment actions Permalink

Please sign in to leave a comment.