In writing REST style interfaces, we would like to minimize code and increase transparancy by passing sort, limit and offset parameters from the HTTP request into your SQL queries.

Note that SQL::Abstract::More throws an error if you specify -offset without -limit, so I always include my own defaults for those instead of relying on the database.

# In your model

use List::Util qw(pairmap);
use SQL::Abstract::More;

my $sqla = SQL::Abstract::More->new;

sub _query_params {
    my ($self, @params) = @_;

    # Select request query parameters, skipping any omitted.
    # Mogrify keys from e.g., 'offset' → '-offset' as per
    # SQL::Abstract::More.  Any non-word characters in the
    # parameter's value are excised to prevent injection
    # attacks. When building hash, provide default values (using
    # -offset style) before calling this.
    map { my $value = $self->param($_) =~ s/\W/ /gr;
          defined $value ? 
              pairmap { ('-'.$a) => $b } ( $_, $value) :
              ()  # empty list skips
        } @params;
}

# Example model method

sub get_something {
    my ($self) = @_;   # $self passed from your Controller
    …
    my %qp = ( -offset => 10, -limit => 3,
               $self->_query_params(qw(offset limit order_by)) );
    my ($sql, @bind) = $sqla->select(-from => 'mytable', %qp );
    …
}

Here is a complete example:

 #!/usr/bin/env perl
 use Mojolicious::Lite;

 ################
 #  Gazetteer browser
 #  Demonstrating
 #  1. Retrieval of tab-delimited file from U.S. Census Bureau into a SQLite database
 #  2. Using SQL::Abstract::More to build queries
 #  3. Safely passing Mojolicious query parameters to the database query
 #  4. Returning JSON
 ################

 use Mojo::UserAgent;

 # Fetch remote resource.
 my $ua = Mojo::UserAgent->new;
 # This is the list of place names in Vermont from the 2015 U.S. Census
 my $tx = $ua->get('http://www2.census.gov/geo/docs/maps-data/data/gazetteer/2015_Gazetteer/2015_gaz_place_50.txt');

 # For now, assume resource exists in the first chunk.
 my $data_text = $tx->res->content->asset->get_chunk(0);

 my @lines = split(/\s*\n/, $data_text);

 my @fields = map { lc } split(/\t/, shift @lines);

 # data as:
 #USPS  GEOID   ANSICODE    NAME    LSAD    FUNCSTAT    ALAND   AWATER  ALAND_SQMI  AWATER_SQMI INTPTLAT    INTPTLONG
 #VT    5000400 02378297    Albany village  47  A   3992913 25599          1.542           0.010     44.729932   -72.381758

 use Mojo::SQLite;

 # Create a table, using field names from first line of file
 my $sql = Mojo::SQLite->new('sqlite:gaz_place.db');
 $sql->db->query('drop table if exists places');
 $sql->db->query('create table places (id integer primary key autoincrement, ' .  join(',', map { "$_ text" } @fields ). ')');

 # Populate table
 foreach my $place_line (@lines) {
     my %place;
     @place{@fields} = map { s/^\s+//; s/\s+$//; $_; } split (/\t/, $place_line);
     if (defined $place{name}) {  # Remove trailing USGS designation
         $place{name} =~ s/\s+(village|city|CDP)//;
     }
     $sql->db->query('insert into places (' . join(',', keys %place ). ') '.
                     'values (' . join(',', (map {'?'} keys %place)) . ');',
                     values %place);
 }

 use SQL::Abstract::More;
 my $sa = SQL::Abstract::More->new();

 use List::Util qw(pairmap);

 sub _query_params {
     my ($self, @params) = @_;

     # Select request query parameters, skipping any omitted.
     # Mogrify keys from e.g., 'offset' → '-offset' as per
     # SQL::Abstract::More.  Any non-word characters in the
     # parameter's value are excised to prevent injection
     # attacks. When building hash, provide default values (using
     # -offset style) before calling this.
     map { my $value = $self->param($_) =~ s/\W/ /gr;
           defined $value ? 
               pairmap { ('-'.$a) => $b } ( $_, $value) :
               ()  # empty list skips
         } @params;
 }

 get '/places' => sub {
     my ($c) = @_;

     # Build a SQL query parameter list with defaults, with selected HTTP query parameters.
     # Invoke with $c->_query_params once you move that into a package
     my %qp = ( -offset => 0, -limit => 10, _query_params($c, qw(offset limit order_by)));

     $c->render(json => $sql->db->query($sa->select(-columns => '*', -from => 'places', %qp))->hashes);
 };

 get '/' => sub {
   my $c = shift;
   $c->render(template => 'index');
 };

 app->start;
 __DATA__

 @@ index.html.ep
 % layout 'default';
 % title 'Gazetteer';
 <h1>Gazetteer Place Name Browser</h1>
 This demonstrates passing parametersthe
 <%= link_to 'list of place names' => '/places' %>.

 To see how parameters are passed through to the SQL,
   try appending query strings like this:

 <ul>
 <li><a href="/places">Example 1.</a> No parameter passed.</li>
 <li><a href="/places?offset=0&limit=20&order_by=aland_sqmi+desc">Example 2</a>
   has offset 0, limit 20, sorting by land area in square miles, largest first</l1>
 <li><a href="/places?offset=20&limit=10&order_by=aland_sqmi+desc">Example 3</a>
   has offset 20, limit 10, like a page following Example 2.</li>
 <li><a href="/places?offset=0&limit=10&order_by=name+desc">Example 4</a>
   has offset 0, limit 10, sorting by name, reverse alphabetically</l1>
 </ul>

 @@ layouts/default.html.ep
 <!DOCTYPE html>
 <html>
   <head><title><%= title %></title></head>
   <body><%= content %></body>
 </html>

Although templates are primarily used with HTML when writing programs in Mojolicious, it is entirely possible and quite simple to use them to respond to any of the MIME types that Mojolicious::Types defines. You merely use the appropriate one-word type name along with .ep, like this Mojolicious::Lite example:

@@ index.json.ep
% use Mojo::JSON qw(to_json);
%== to_json { foo => 3 };

Here, we permit the /index route to respond with JSON by using Mojo's convenient module. We use the double-equal percent-print form so that quote characters and other HTML-ish bits are output as-is, not ampersand-encoded.

It is important to not have empty lines above or below the content, as the newlines would appear in the output.

Also note the use of to_json (which returns characters) rather than encode_json (which returns bytes) to avoid double-encoding UTF8 characters.

With that in mind, you should be able to produce any file format you desire.