flatten.sqlx

config { hasOutput: true }
/*
 * Copyright 2023 Google LLC
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

CREATE OR REPLACE FUNCTION ${self()}(input JSON, path STRING, outer_ BOOL, recursive_ BOOL, mode STRING)
RETURNS ARRAY<STRUCT<SEQ INT64, KEY STRING, PATH STRING, INDEX INT64, VALUE JSON, THIS JSON>>
LANGUAGE js
OPTIONS (
  description = "Emulates the 'flatten' function present in Snowflake."
) AS r"""
  mode = mode.toLowerCase();
  if (path.length > 0) {
    for (part of path.matchAll(/(\.?\w+)|(\[\d+\])/g)) {
      input = input[part[0].replace(/[\[\].]/g, '')];
    }
  }
  let f = function(i, p) {
    let result = [];
    let index = 0;
    let arr = Array.isArray(i);
    for (const [key, value] of Object.entries(i)) {
      if (value === null) {
        continue;
      }
      index++;
      let path = p;
      if (p.length > 0 && !arr) {
        path = path + '.';
      }
      path = path + (arr ? '[' + key + ']' : key);
      result.push({
        SEQ: index,
        KEY: arr ? null : key,
        PATH: path,
        INDEX: arr ? key : null,
        VALUE: value,
        THIS: i
      });

      const isArray = Array.isArray(value);
      const arrayRecurse = (mode === 'array' || mode === 'both') && isArray;
      const objectRecurse = (mode === 'object' || mode === 'both') &&
          !isArray && Object.isExtensible(value);
      if (recursive_ && (arrayRecurse || objectRecurse)) {
        result = result.concat(f(value, path));
      }
    }
    return result;
  };
  let result = f(input, path);
  if (outer_ && result.length === 0) {
    return [{
      SEQ: 1,
      KEY: null,
      PATH: null,
      INDEX: null,
      VALUE: null,
      THIS: input
    }];
  }
  return result;
""";