Unconventional Queries

Appearance

Where ​

The SQL where clause filters the results of a query by one or multiple columns. The same is achieved in this library by adding a where field to any query definition. E.g:

ts
import { selectMany } from 'unconventional-pg-queries';

const response = await selectMany(client, {
    table: 'public.users',
    where: [
        { field: 'age', operator: SqlWhereOperator.Lt, value: 30 }
    ]
});
import { selectMany } from 'unconventional-pg-queries';

const response = await selectMany(client, {
    table: 'public.users',
    where: [
        { field: 'age', operator: SqlWhereOperator.Lt, value: 30 }
    ]
});

Generated SQL

sql
SELECT * FROM public.users fromref
WHERE (fromref."age")::int < 30 
SELECT * FROM public.users fromref
WHERE (fromref."age")::int < 30

Operators ​

The following operators are supported by the where field:

Equal ​

ts
{
    table: 'public.users',
    where: [ 
        { field: 'firstName', operator: SqlWhereOperator.Eq, value: 'John' } 
    ] 
}
{
    table: 'public.users',
    where: [ 
        { field: 'firstName', operator: SqlWhereOperator.Eq, value: 'John' } 
    ] 
}
sql
SELECT * FROM public.users fromref
WHERE fromref."firstName" = 'John' 
SELECT * FROM public.users fromref
WHERE fromref."firstName" = 'John'

Not Equal ​

ts
{
    table: 'public.users',
    where: [ 
        { field: 'firstName', operator: SqlWhereOperator.Neq, value: 'John' } 
    ] 
}
{
    table: 'public.users',
    where: [ 
        { field: 'firstName', operator: SqlWhereOperator.Neq, value: 'John' } 
    ] 
}
sql
SELECT * FROM public.users fromref
WHERE fromref."firstName" <> 'John' 
SELECT * FROM public.users fromref
WHERE fromref."firstName" <> 'John'

Greater Than ​

ts
{
    table: 'public.users',
    where: [ 
        { field: 'age', operator: SqlWhereOperator.Gt, value: 30 } 
    ] 
}
{
    table: 'public.users',
    where: [ 
        { field: 'age', operator: SqlWhereOperator.Gt, value: 30 } 
    ] 
}
sql
SELECT * FROM public.users fromref
WHERE (fromref."age")::int > 30 
SELECT * FROM public.users fromref
WHERE (fromref."age")::int > 30

Greater Than Or Equal ​

ts
{
    table: 'public.users',
    where: [ 
        { field: 'age', operator: SqlWhereOperator.Gt, value: 30 } 
    ] 
}
{
    table: 'public.users',
    where: [ 
        { field: 'age', operator: SqlWhereOperator.Gt, value: 30 } 
    ] 
}
sql
SELECT * FROM public.users fromref
WHERE (fromref."age")::int >= 30 
SELECT * FROM public.users fromref
WHERE (fromref."age")::int >= 30

Less Than ​

ts
{
    table: 'public.users',
    where: [ 
        { field: 'age', operator: SqlWhereOperator.Lt, value: 30 } 
    ] 
}
{
    table: 'public.users',
    where: [ 
        { field: 'age', operator: SqlWhereOperator.Lt, value: 30 } 
    ] 
}
sql
SELECT * FROM public.users fromref
WHERE (fromref."age")::int < 30 
SELECT * FROM public.users fromref
WHERE (fromref."age")::int < 30

Less Than Or Equal ​

ts
{
    table: 'public.users',
    where: [ 
        { field: 'age', operator: SqlWhereOperator.Lte, value: 30 } 
    ] 
}
{
    table: 'public.users',
    where: [ 
        { field: 'age', operator: SqlWhereOperator.Lte, value: 30 } 
    ] 
}
sql
SELECT * FROM public.users fromref
WHERE (fromref."age")::int <= 30 
SELECT * FROM public.users fromref
WHERE (fromref."age")::int <= 30

Like ​

ts
{
    table: 'public.users',
    where: [ 
        { field: 'firstName', operator: SqlWhereOperator.Like, value: 'J%' } 
    ] 
}
{
    table: 'public.users',
    where: [ 
        { field: 'firstName', operator: SqlWhereOperator.Like, value: 'J%' } 
    ] 
}
sql
SELECT * FROM public.users fromref
WHERE fromref."firstName" LIKE 'J%' 
SELECT * FROM public.users fromref
WHERE fromref."firstName" LIKE 'J%'

ILike ​

ILike is the same as Like but case-insensitive

ts
{
    table: 'public.users',
    where: [ 
        { field: 'firstName', operator: SqlWhereOperator.ILike, value: 'j%' } 
    ] 
}
{
    table: 'public.users',
    where: [ 
        { field: 'firstName', operator: SqlWhereOperator.ILike, value: 'j%' } 
    ] 
}
sql
SELECT * FROM public.users fromref
WHERE fromref."firstName" ILIKE 'j%' 
SELECT * FROM public.users fromref
WHERE fromref."firstName" ILIKE 'j%'

Not Like ​

ts
{
    table: 'public.users',
    where: [ 
        { field: 'firstName', operator: SqlWhereOperator.NotLike, value: 'John' } 
    ] 
}
{
    table: 'public.users',
    where: [ 
        { field: 'firstName', operator: SqlWhereOperator.NotLike, value: 'John' } 
    ] 
}
sql
SELECT * FROM public.users fromref
WHERE fromref."firstName" NOT LIKE 'John' 
SELECT * FROM public.users fromref
WHERE fromref."firstName" NOT LIKE 'John'

In ​

ts
{
    table: 'public.users',
    where: [ 
        { field: 'id', operator: SqlWhereOperator.In, value: '(1,2,3)' } 
    ] 
}
{
    table: 'public.users',
    where: [ 
        { field: 'id', operator: SqlWhereOperator.In, value: '(1,2,3)' } 
    ] 
}
sql
SELECT * FROM public.users fromref
WHERE fromref."id" IN (1,2,3) 
SELECT * FROM public.users fromref
WHERE fromref."id" IN (1,2,3)

Not In ​

ts
{
    table: 'public.users',
    where: [ 
        { field: 'firstName', operator: SqlWhereOperator.NotIn, value: '(1,2,3)' } 
    ] 
}
{
    table: 'public.users',
    where: [ 
        { field: 'firstName', operator: SqlWhereOperator.NotIn, value: '(1,2,3)' } 
    ] 
}
sql
SELECT * FROM public.users fromref
WHERE fromref."id" NOT IN (1,2,3) 
SELECT * FROM public.users fromref
WHERE fromref."id" NOT IN (1,2,3)

Is Null ​

ts
{
    table: 'public.users',
    where: [ 
        { field: 'lastName', operator: SqlWhereOperator.IsNull, value: null } 
    ] 
}
{
    table: 'public.users',
    where: [ 
        { field: 'lastName', operator: SqlWhereOperator.IsNull, value: null } 
    ] 
}
sql
SELECT * FROM public.users fromref
WHERE fromref."id" IS NULL 
SELECT * FROM public.users fromref
WHERE fromref."id" IS NULL

NOTE

The value field is unused in IS NULL and IS NOT NULL clauses.

Is Not Null ​

ts
{
    table: 'public.users',
    where: [ 
        { field: 'lastName', operator: SqlWhereOperator.IsNotNull, value: null } 
    ] 
}
{
    table: 'public.users',
    where: [ 
        { field: 'lastName', operator: SqlWhereOperator.IsNotNull, value: null } 
    ] 
}
sql
SELECT * FROM public.users fromref
WHERE fromref."id" IS NOT NULL 
SELECT * FROM public.users fromref
WHERE fromref."id" IS NOT NULL

Bitwise AND ​

ts
{
    table: 'public.users',
    where: [ 
        { field: 'id', operator: SqlWhereOperator.BitwiseAnd, value: 2 } 
    ] 
}
{
    table: 'public.users',
    where: [ 
        { field: 'id', operator: SqlWhereOperator.BitwiseAnd, value: 2 } 
    ] 
}
sql
SELECT * FROM public.users fromref
WHERE (fromref."id")::int & 2 > 0 
SELECT * FROM public.users fromref
WHERE (fromref."id")::int & 2 > 0

TIP

The bitwise AND operator is useful when storing flag enums in an integer column, where each bit of the integer represents a different boolean value. To check if a particular bit is set, simply check field & bit > 0.

Combining Clauses ​

Filtering on multiple columns at once is possible using the andOr field. E.g.

ts
{
    table: 'public.users',
    where: [ 
        { field: 'lastName', operator: SqlWhereOperator.Like, value: 'S%' }, 
        { andOr: AndOr.Or, field: 'lastName', operator: SqlWhereOperator.IsNull, value: null }, 
        { andOr: AndOr.And, field: 'age', operator: SqlWhereOperator.Gt, value: 30 }, 
    ] 
}
{
    table: 'public.users',
    where: [ 
        { field: 'lastName', operator: SqlWhereOperator.Like, value: 'S%' }, 
        { andOr: AndOr.Or, field: 'lastName', operator: SqlWhereOperator.IsNull, value: null }, 
        { andOr: AndOr.And, field: 'age', operator: SqlWhereOperator.Gt, value: 30 }, 
    ] 
}
sql
SELECT * FROM public.users fromref
WHERE fromref."lastName" LIKE 'S%' 
OR fromref."lastName" IS NULL 
AND (fromref."age")::int > 30 
SELECT * FROM public.users fromref
WHERE fromref."lastName" LIKE 'S%' 
OR fromref."lastName" IS NULL 
AND (fromref."age")::int > 30

NOTE

Boolean operators are combined from left to right. Complex combinations involving parenthetical statements like (A OR B) AND (C OR D OR E) are not currently possible.

Filtering on JSON Columns ​

Filtering is supported on fields nested within JSONB columns as well. To do so, specify the JSONB column in field as usual, but also add the jsonPath field with the sub-field(s) to target. E.g.

ts
{
    table: 'public.users',
    where: [ 
        { 
            field: 'metadata', 
            jsonPath: ['website'], 
            operator: SqlWhereOperator.Like, 
            value: '%.com' 
        }, 
    ] 
}
{
    table: 'public.users',
    where: [ 
        { 
            field: 'metadata', 
            jsonPath: ['website'], 
            operator: SqlWhereOperator.Like, 
            value: '%.com' 
        }, 
    ] 
}
sql
SELECT * FROM public.users fromref
WHERE fromref."metadata"->>'website' LIKE '%.com' 
SELECT * FROM public.users fromref
WHERE fromref."metadata"->>'website' LIKE '%.com'

Or in the case of a nested sub-field:

ts
{
    table: 'public.users',
    where: [ 
        { 
            field: 'metadata', 
            jsonPath: ['links', 'avatar'], 
            operator: SqlWhereOperator.IsNotNull, 
            value: null 
        }, 
    ] 
}
{
    table: 'public.users',
    where: [ 
        { 
            field: 'metadata', 
            jsonPath: ['links', 'avatar'], 
            operator: SqlWhereOperator.IsNotNull, 
            value: null 
        }, 
    ] 
}
sql
SELECT * FROM public.users fromref
WHERE fromref."metadata"->'links'->>'avatar' IS NOT NULL 
SELECT * FROM public.users fromref
WHERE fromref."metadata"->'links'->>'avatar' IS NOT NULL