query.sql

WITH input(query) AS (
  VALUES(
    text 'tabletop'
  )
),
decoded AS (
  SELECT x.* FROM (
    SELECT
      CONVERT_FROM(
        DECODE(files.data, 'BASE64'), 'UTF-8'
      ) AS string,
      files.id
    FROM
      files
  ) x
  WHERE 
    x.string LIKE CONCAT('%', (SELECT query FROM input), '%')
),
snippets AS (
  SELECT
    y.*,
    row_number() OVER(PARTITION BY y.id) AS row,
    POSITION((SELECT query FROM input) IN y.snippet) AS relative_index
  FROM
    (
      SELECT
        regexp_split_to_table(
          decoded.string, E'[\\n\\r]+'
        ) AS snippet,
        decoded.id
      FROM
        decoded
    ) y
),
data AS (
  SELECT
    snippets.*,
    (SUM(LENGTH(snippets.snippet)) OVER(
      PARTITION BY snippets.id ROWS UNBOUNDED PRECEDING
    )) + snippets.row AS cumulative_content_length
  FROM snippets
),
results AS (
  SELECT 
    data.id,
    json_agg(
      json_build_object(
        'relativeIndex', (data.relative_index - 1),
        'absoluteIndex', (
          data.cumulative_content_length - LENGTH(data.snippet) + data.relative_index
        ),
        'snippet', data.snippet,
        'row', data.row
      )
    ) AS json
  FROM
    data
  WHERE data.relative_index != 0
  GROUP BY data.id
)
SELECT (json_agg(
  json_build_object(
    'id', results.id,
    -- 'size', results.size,
    -- 'contentType', results.content_type,
    -- 'name', results.name,
    'data', results.json
  )
)) FROM results