Go Back
Convert Nested Array to Table
Publisher
: Lingk
Run In Lingk
Description
This recipe demonstrates two ways to access nested array data using LingkQL using Spark SQL functions To run this recipe, click "run"
Browse the knowledge base
Twitter
E-Mail
# _____ _ _____ __ # | __ \ (_) |_ _| / _| # | |__) |___ ___ _ _ __ ___ | | _ __ | |_ ___ # | _ // _ \/ __| | '_ \ / _ \ | | | '_ \| _/ _ \ # | | \ \ __/ (__| | |_) | __/ _| |_| | | | || (_) | # |_| \_\___|\___|_| .__/ \___| |_____|_| |_|_| \___/ # | | # |_| # Project Name - CONVERT NESTED ARRAY TO TABLE # Recipe URL - https://app.lingk.io/a/10932/tf/17849 # Description - # This recipe demonstrates two ways to access nested array data using LingkQL using Spark SQL functions # To run this recipe, click "run" # Industry - Higher Ed # Business Process - Graduate Reporting # Systems - # Connectors - JSON # Data Flows - Single Direction # Connection Type - JSON # Add Recipe notes / Change log information here! # _____ _ # / ____| | | # | | ___ _ __ _ __ ___ ___| |_ ___ _ __ ___ # | | / _ \| '_ \| '_ \ / _ \/ __| __/ _ \| '__/ __| # | |___| (_) | | | | | | | __/ (__| || (_) | | \__ \ # \_____\___/|_| |_|_| |_|\___|\___|\__\___/|_| |___/ # # CONNECTORS specify what data will be pulled into the in-memory database during processing connectors: # JSON Setup - https://help.lingk.io/en/articles/74-json-connector-reference ###### Start: JSON Connectors ####### - name: nestedData type: json properties: jsonObject: | { "customer_id":"ada13232", "values": [ {"hello":"earth"}, {"hello":"mars"} ] } ###### End: JSON Connectors ####### # _____ _ _ _ # / ____| | | | | | # | (___ | |_ __ _| |_ ___ _ __ ___ ___ _ __ | |_ ___ # \___ \| __/ _` | __/ _ \ '_ ` _ \ / _ \ '_ \| __/ __| # ____) | || (_| | || __/ | | | | | __/ | | | |_\__ \ # |_____/ \__\__,_|\__\___|_| |_| |_|\___|_| |_|\__|___/ # STATEMENTS specify how the data should be processed while in memory statements: #******************************************************************** D I S C L A I M E R *********************************************************************************************** # * # Note that in an effort to keep recipes optimized for DPH (Data Processing Hours), print statements should be commented out after development has concluded for a recipe. * # For more information on DPH optimization, please visit the following help article - https://help.lingk.io/en/articles/212-minimizing-data-processing-hours-on-the-lingk-platform * # * #******************************************************************** D I S C L A I M E R *********************************************************************************************** #- statement: print nestedData # explode - flatten the array and add values to help join with other data - statement: | (flattenedArrayWithKey) => select source.customer_id, a.values.* from nestedData source lateral view explode(source.values) a as values #- statement: print flattenedArrayWithKey # inline - flatten the array to make it a basic table - statement: | (flattenedArray) => select inline(values) from nestedData #- statement: print flattenedArray # Add more statements to convert, join, aggregrate, transform, and integrate your data
Upsert Excel Data to Salesforce Contacts
Canvas Users to Salesforce Contacts Writer