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