JSON Flip with Snowflake CLI

commands.md

JSON Flip: Complete Command Reference

A comprehensive collection of all commands and patterns from the "JSON Flip" technique for JSON-to-CSV conversion.

Basic JSON-to-CSV Conversion

Standard JSON Flip Pattern

# Basic pattern for converting JSON arrays to CSV
jq -r '(.[0] | keys_unsorted) as $keys | 
  ($keys | map(ascii_downcase) | join(",")), 
  (.[] | [.[$keys[]]] | @csv)' input.json > output.csv

Snowflake Integration

Export Snowflake Query Results to CSV

# Export data directly from Snowflake to CSV
snowflake sql -q "SELECT * FROM PENGUINS_ML_APP.DATA.PENGUINS" --format=json | \
  jq -r '(.[0] | keys_unsorted) as $keys |
  ($keys | map(ascii_downcase) | join(",")),
  (.[] | [.[$keys[]]] | @csv)' > penguins.csv

Query with Connection Profile

# Use specific connection profile from ~/.snowsql/config
snowflake sql -c my_connection -q "SELECT * FROM PENGUINS_ML_APP.DATA.PENGUINS" --format=json > data.json

Advanced Techniques

Filtering Data During Conversion

# Filter records during conversion (only include records where ISLAND="Biscoe")
jq -r 'map(select(.ISLAND == "Biscoe")) |
  (.[0] | keys_unsorted) as $keys |
  ($keys | map(ascii_downcase) | join(",")),
  (.[] | [.[$keys[]]] | @csv)' input.json > filtered.csv

Field Selection and Reordering

# Select specific fields and define their order
jq -r '(["species", "island", "bill_length_mm", "body_mass_g"]) as $keys |
  ($keys | join(",")),
  (.[] | [.[$keys[]]] | @csv)' input.json > selected.csv

DevOps Integration

Environment Comparison Workflow

# Extract data from staging environment
snowflake sql -c staging_conn -q "SELECT * FROM PENGUINS_ML_APP.DATA.PENGUINS" --format=json > staging_data.json

# Extract data from production environment
snowflake sql -c prod_conn -q "SELECT * FROM PENGUINS_ML_APP.DATA.PENGUINS" --format=json > prod_data.json

# Convert both JSON files to CSV with the same field order
jq -r '(.[0] | keys_unsorted) as $keys |
  ($keys | join(",")), (.[] | [.[$keys[]]] | @csv)' staging_data.json > staging.csv
  
jq -r '(.[0] | keys_unsorted) as $keys |
  ($keys | join(",")), (.[] | [.[$keys[]]] | @csv)' prod_data.json > prod.csv
  
# Compare the results
diff -u staging.csv prod.csv

Creating Test Datasets

# Extract a subset of data for testing
curl -s https://api.example.com/data | \
jq 'map(select(.id <= 10))' | \
jq -r '(.[0] | keys_unsorted) as $keys |
($keys | map(ascii_downcase) | join(",")),
(.[] | [.[$keys[]]] | @csv)' > test_dataset.csv

GitHub Actions Workflow

# Complete GitHub Actions workflow for Snowflake reporting
name: Generate Penguins Report
on:
  schedule:
    - cron: '0 7 * * 1'  # Run every Monday at 7:00 AM
jobs:
  generate-report:
    runs-on: ubuntu-latest
    steps:
      - name: Checkout repository
        uses: actions/checkout@v3
        
      - name: Set up Snowflake CLI
        uses: snowflakedb/[email protected]
        with:
          cli-version: "3.6.0"
          
      - name: Generate Penguins Report from Snowflake
        env:
          SNOWFLAKE_ACCOUNT: ${{ secrets.SNOWFLAKE_ACCOUNT }}
          SNOWFLAKE_USER: ${{ secrets.SNOWFLAKE_PRIVATE_KEY_RAW }}
        run: |
          snowflake sql -q "SELECT * FROM PENGUINS_ML_APP.DATA.PENGUINS" --format=json > snowflake_data.json
          
          jq -r '(.[0] | keys_unsorted) as $keys |
          ($keys | map(ascii_downcase) | join(",")), 
          (.[] | [.[$keys[]]] | @csv)' snowflake_data.json > weekly_report.csv
          
      - name: Upload Report
        uses: actions/upload-artifact@v3
        with:
          name: penguins-weekly-report
          path: weekly_report.csv

Helpful jq Techniques

Extract Keys from First Object

# Extract keys from the first object in a JSON array
jq -r '.[0] | keys_unsorted' input.json

Convert Keys to Lowercase

# Convert all keys to lowercase
jq -r '.[0] | keys_unsorted | map(ascii_downcase)' input.json

Format as CSV with Header

# Create a CSV with header followed by data rows
jq -r '(["header1", "header2", "header3"] | join(",")),
  (.[] | [.field1, .field2, .field3] | @csv)' input.json