Automatic Union of Multiple JSON files

#   _____           _              _____        __      
#  |  __ \         (_)            |_   _|      / _|     
#  | |__) |___  ___ _ _ __   ___    | |  _ __ | |_ ___  
#  |  _  // _ \/ __| | '_ \ / _ \   | | | '_ \|  _/ _ \ 
#  | | \ \  __/ (__| | |_) |  __/  _| |_| | | | || (_) |
#  |_|  \_\___|\___|_| .__/ \___| |_____|_| |_|_| \___/ 
#                    | |                                
#                    |_|

# Project Name - AUTOMATIC UNION OF MULTIPLE JSON FILES

# Recipe URL - https://app.lingk.io/a/10932/tf/17833

# Description - # This recipe dynamically unions two or more like JSON files on Amazon S3 together into a single dataset for transformation and aggregration. 
                # To use this recipe, configure the S3 connector in your environment and upload the file into your Amazon S3 bucket. Then download the file from Local File area for your environment and "Run"

# Industry - Higher Ed
# Business Process - Graduate Reporting

# Systems - S3 Bucket
# Connectors - S3, JSON
# Data Flows - Single Direction
# Connection Type - S3Bucket to JSON

# Add Recipe notes / Change log information here!

#    _____                            _                 
#   / ____|                          | |                
#  | |     ___  _ __  _ __   ___  ___| |_ ___  _ __ ___ 
#  | |    / _ \| '_ \| '_ \ / _ \/ __| __/ _ \| '__/ __|
#  | |___| (_) | | | | | | |  __/ (__| || (_) | |  \__ \
#   \_____\___/|_| |_|_| |_|\___|\___|\__\___/|_|  |___/
#

# CONNECTORS specify what data will be pulled into the in-memory database during processing

connectors:

# Configure S3Bucket credentials in your Environment before running this recipe
  # S3Bucket Setup - https://help.lingk.io/en/articles/298-amazon-s3-connector-setup-guide
  # JSON Setup - https://help.lingk.io/en/articles/74-json-connector-reference

###### Start - s3Reader connectors ######

- name: s3FileReader
    type: s3Reader
    parameterizedBy: filteredFileList
    format: jsonFormat
    properties:
      s3BucketName: testing-data-private
      s3ObjectKey: "{{var.file}}"

###### End - s3Reader connectors ######

###### Start - JSON connectors ######

- name: fileList
    type: json
    properties:
      jsonObject: |
        [
          { "file":"1. portfolio.json" },
          { "file":"2. relationship.json" },
          { "file":"3. client.json" },
          { "file":"4. accounts 1-250.json" },
          { "file":"5. accounts 251-500.json" },
          { "file":"6. accounts 501-750.json" }
        ]

###### End - JSON connectors ######

#   ______                         _       
#  |  ____|                       | |      
#  | |__ ___  _ __ _ __ ___   __ _| |_ ___ 
#  |  __/ _ \| '__| '_ ` _ \ / _` | __/ __|
#  | | | (_) | |  | | | | | | (_| | |_\__ \
#  |_|  \___/|_|  |_| |_| |_|\__,_|\__|___/

# FORMATS specify how files should be processed as they are being read or written to

readFormats:

- name: jsonFormat
    type: json

#    _____ _        _                            _       
#   / ____| |      | |                          | |      
#  | (___ | |_ __ _| |_ ___ _ __ ___   ___ _ __ | |_ ___ 
#   \___ \| __/ _` | __/ _ \ '_ ` _ \ / _ \ '_ \| __/ __|
#   ____) | || (_| | ||  __/ | | | | |  __/ | | | |_\__ \
#  |_____/ \__\__,_|\__\___|_| |_| |_|\___|_| |_|\__|___/

# 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 ***********************************************************************************************

# Note: you can use dot notation in your SELECT statement to reach into nested data. You can also access nested arrays with explore() or inline() functions.
  - statement:                      # limit to 3 json file
      (filteredFileList) => SELECT file from fileList where file like
      '%accounts%'
  #- statement: print filteredFileList
  - statement: refresh s3FileReader
  #- statement: print s3FileReader
  - statement: (fileRowCount) => SELECT count(*) from s3FileReader
  #- statement: print fileRowCount   # outputs 538

# Add more statements to convert, join, aggregrate, transform, and integrate your data