NAME

JsonSQL::Query::Select - JsonSQL::Query::Select object. Stores a Perl representation of a SELECT statement created from a JSON string.

VERSION

version 0.41

SYNOPSIS

Use this to generate an SQL SELECT statement from a JSON string.

To use this:

use JsonSQL::Query::Select;

my $jsonString = '{
    "fields": [
        {"column": "*"}
    ],
    "from": [
        {"table": "my_table"}
    ]
}';

my $whitelisting_rules = [
    { schema => '#anySchema', 'my_table' => [ '#anyColumn' ] }
];

my ( $selectObj, $err ) = JsonSQL::Query::Select->new($whitelisting_rules, $jsonString);
if ( $selectObj ) {
    my ( $sql, $binds ) = $selectObj->get_select;
    <...>
} else {
    die $err;
}

Now you can go ahead and use $sql and $binds directly with the DBI module to do the query.

DESCRIPTION

This is a JsonSQL Query module that supports SQL generation for a broad range of the most common SQL SELECT features, including JOINs.

Examples of SELECT features supported by this module:

A simple SELECT statement (minimum),

{
    "fields": [
        {"column": "*"}
    ],
    "from": [
        {"table": "my_table"}
    ]
}

A more complicated SELECT statement,

{
    "fields": [
        {"column": "field1"},
        {"column": "field2", "alias": "test"}
    ],
    "from": [
        {"table": "table1", "schema": "MySchema"}
    ], 
    "where": {
        "and": [
            { "eq": {"field": {"column": "field2"}, "value": "Test.Field2"} },
            { "eq": {"field": {"column": "field1"}, "value": "453.6"} },
            { "or": [
                { "eq": {"field": {"column": "field2"}, "value": "field3"} },
                { "gt": {"field": {"column": "field3"}, "value": "45"} }
            ]}
        ]
    }
}

A SELECT statement with JOINs,

{
    "fields": [
        {"column": "field1"},
        {"column": "field2", "alias": "test"}
    ],
    "joins": [
        {"jointype": "inner", "from": {"table": "table1", "schema": "MySchema"}, "to": {"table": "table2", "schema": "MySchema"}, "on": {"eq": {"field": {"column": "field2"}, "value": {"column": "field1"}} }}
    ],
    "where": {
        "and": [
            { "eq": {"field": {"column": "field2"}, "value": "Test.Field2"} },
            { "eq": {"field": {"column": "field1"}, "value": "453.6"} },
            { "or": [
                { "eq": {"field": {"column": "field2"}, "value": "field3"} },
                { "gt": {"field": {"column": "field3"}, "value": "45"} }
            ]}
        ]
    }
}

Mapping of JSON object properties to SELECT parameters:

Required,

fields => [ { table => "table1", column => "column1" }, { table => "table1", column => "column2" } ]

Generates: "table1"."column1", "table1"."column2"
See L<JsonSQL::Param::Fields> and L<JsonSQL::Param::Field> for more info.

from => [ { schema => "myschema", table = "table1" } ] ( if you are using a JOIN, you can omit the FROM )

Generates FROM "myschema"."table1"
See L<JsonSQL::Param::Tables> and L<JsonSQL::Param::Table> for more info.

Optional,

joins => [ { jointype => "inner", from => { table => "table1" }, to => { table => "table2" }, on => { eq => { field => { table => "table1", column => "column1" }, value => { table => "table2", column: "column2"}} } } ]

Generates: FROM "table1" INNER JOIN "table2" ON "table1"."column1" = "table2"."column2"
See L<JsonSQL::Param::Joins> and L<JsonSQL::Param::Join> for more info.

where => { eq => { field => { table => "table1", column => "column1" }, value => 32 } }

Generates: WHERE "table1"."column1" = ?
    Bind: [ 32 ]
See L<JsonSQL::Param::Condition> and L<JsonSQL::Param::ConditionDispatcher> for more info.

orderby => [ { field => { table => "table1", column => "column1" }, order => 'ASC'} ]

Generates: ORDER BY "table"."column1" ASC
See L<JsonSQL::Param::OrderBy> and L<JsonSQL::Param::Order> for more info.

groupby => [ { table => "table1", column => "column1" } ]

Generates: GROUP BY "table1"."column1"
See L<JsonSQL::Param::Fields> and L<JsonSQL::Param::Field> for more info.

having => { eq => { field => { table => "table1", column => "column1" }, value => 32 } }

Generates: HAVING "table1"."column1" = ?
    Bind: [ 32 ]
See L<JsonSQL::Param::Condition> and L<JsonSQL::Param::ConditionDispatcher> for more info.

distinct => 'true'

Generates: DISTINCT

limit => 23

Generates: LIMIT ?
    Bind: [ 23 ]

offset => 12

Generates: OFFSET ?
    Bind: [ 12 ]

Additional Properties,

defaultschema => 'myschema'

If you are using DB schemas, this property can be used to generate the schema identifier for your queries. Particularly useful for per-user DB schemas.

See JsonSQL::Schemas::select to view the restrictions enforced by the JSON schema.

Whitelisting Module

A set of whitelisting rules is required to successfully use this module to generate SQL. See JsonSQL::Validator to learn how this works.

METHODS

Constructor new($query_rulesets, $json_query, $quote_char)

Instantiates and returns a new JsonSQL::Query::Select object.

$query_rulesets      => The whitelisting rules to validate the query with.
$json_query          => A stringified JSON object representing the query.
$quote_char          => Optional: the character to use for quoting identifiers. The SUPER defaults to ANSI double quotes.

Returns (0, <error message>) on failure.

ObjectMethod get_select -> ( $sql, $binds )

Generates the SQL statement represented by the object. Returns:

$sql            => An SQL SELECT string.
$binds          => An arrayref of parameterized values to pass to the query.

AUTHOR

Chris Hoefler <bhoefler@draper.com>

COPYRIGHT AND LICENSE

This software is copyright (c) 2017 by Chris Hoefler.

This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.

cpanm JsonSQL

perl -MCPAN -e shell
install JsonSQL