SELECT

DOCS • LANG

---

The SELECT statement.

See APIS ➞ client.query(), table.select()

Basic Select

// (a): SQL syntax
const result = await client.query(
    `SELECT *
    FROM public.users`
);

// (b): Object-based syntax
const result = await client.database('public').table('users').select();

Structuring

See also ➞ JSON Sugars

// (a): SQL syntax
const result = await client.query(
    `SELECT
        name,
        phone,
        { email, phone AS mobile } AS contact1,
        [ email, phone ] AS contact2
    FROM users`
);

// (b): Object-based syntax
const result = await client.database('public').table('users').select([
    { expr: 'name' },
    { expr: 'phone' },
    { expr: {
        fields: ['email', { expr: 'phone', as: 'mobile'}]
    }, as: 'contact1' },
    { expr: {
        items: ['email', 'phone']
    }, as: 'contact2' }
]);

// (c): Function-based syntax
const result = await client.database('public').table('users').select([
    (q) => q.expr('name'),
    (q) => q.expr('phone'),
    (q) => q.expr(
        (r) => r.fields('email', (s) => s.expr('phone').as('mobile'))
    ).as('contact1'),
    (q) => q.expr(
        (r) => r.items('email', 'phone')
    ).as('contact2')
]);

The WHERE Clause

Find by simple expression:

// (a): SQL syntax
const result = await client.query(
    `SELECT *
    FROM public.users
    WHERE name = 'John' AND role = 'guest'`
);

// (b): Object-based syntax
const result = await client.database('public').table('users').select(
    { where: [
        { eq: ['name', { value: 'John' }] },
        { eq: ['role', { value: 'guest' }] }
    ] }
);

// (c): Function-based syntax
const result = await client.database('public').table('users').select(
    { where: [
        (q) => q.eq('name', (r) => r.value('John')),
        (q) => q.eq('role', (r) => r.value('guest'))
    ] }
);

Find by simple expression:

// (a): SQL syntax
const result = await client.query(
    `SELECT
        name,
        email
    FROM public.users
    WHERE role IS NOT NULL AND COALESCE(email, phone) IS NOT NULL)`
);

// (b): Object-based syntax
const result = await client.database('public').table('users').select(
    [ 'name', 'email' ],
    { where: [
        { isNotNull: 'role' },
        { isNotNull: { fn: ['COALESCE', 'email', 'phone'] } }
    ] }
);

// (c): Function-based syntax
const result = await client.database('public').table('users').select(
    [ 'name', 'email' ],
    { where: [
        { isNotNull: 'role' },
        { isNotNull: { fn: ['COALESCE', 'email', 'phone'] } }
    ] }
);

Find by complex expression:

// (a): SQL syntax
const result = await client.query(
    `SELECT
        name,
        email
    FROM public.users
    WHERE (role = $1 OR role = $2) AND (
        email IS NOT NULL OR (
            phone IS NOT NULL AND country_code IS NOT NULL
        )
    )`
);

// (b): Object-based syntax
const result = await client.database('public').table('users').select(
    [ 'name', 'email' ],
    { where: [
        { some: [
            { eq: ['role', { binding: 'admin' }] },
            { eq: ['role', { binding: 'contributor' }] }
        ] },
        { some: [
            { isNotNull: 'email' },
            { every: [
                { isNotNull: 'phone' },
                { isNotNull: 'country_code' }
            ] }
        ] }
    ] }
);

// (c): Function-based syntax
const result = await client.database('public').table('users').select(
    [ 'name', 'email' ],
    { where: [
        (q) => q.some(
            (r) => r.eq('role', (s) => s.binding('admin')),
            (r) => r.eq('role', (s) => s.binding('contributor')),
        ),
        (q) => q.some(
            (r) => r.isNotNull('email'),
            (r) => r.every(
                (s) => s.isNotNull('phone'),
                (s) => s.isNotNull('country_code')
            )
        )
    ] }
);

The ORDER BY Clause

Order by complex expression:

// (a): SQL syntax
const result = await client.query(
    `SELECT
        name,
        email
    FROM public.users
    ORDER BY
        CASE role WHEN 'admin' THEN 1 WHEN 'contributor' THEN 2 ELSE 3 END ASC,
        CASE WHEN phone IS NULL THEN 0 ELSE 1 END DESC,
        name ASC`
);

// (b): Object-based syntax
const result = await client.database('public').table('users').select(
    [ 'name', 'email' ],
    { orderBy: [
        { expr: {
            switch: 'role',
            cases: [
                { when: { value: 'admin' }, then: 1 },
                { when: { value: 'contributor' }, then: 2 }
            ],
            default: 0
        }, asc: true },
        { expr: {
            cases: [ { when: { isNull: 'phone' }, then: 0 } ],
            default: 1
        }, desc: true },
        { expr: 'name', asc: true }
    ] }
);

// (c): Function-based syntax
const result = await client.database('public').table('users').select(
    [ 'name', 'email' ],
    { orderBy: [
        (q) => q.expr(
            (r) => r.switch('role').cases(
                (s) => s.when((t) => t.value('admin')).then(1),
                (s) => s.when((t) => t.value('contributor')).then(2)
            ).default(3)
        ).asc(),
        (q) => q.expr(
            (r) => r.cases(
                (s) => s.when((t) => t.isNull('phone')).then(0)
            ).default(1)
        ).desc(),
        (q) => q.expr('name').asc()
    ] }
);

Relational Queries

See also ➞ Magic Paths

Basic paths:

// (a): SQL syntax
const result = await client.query(
    `SELECT
        title,
        content,
        author ~> name AS author_name
    FROM public.books
    WHERE author ~> role = $1`,
    ['admin']
);

// (b): Object-based syntax
const result = await table.select(
    { fields: [
        { expr: 'title' },
        { expr: 'content' },
        { expr: {
            path: ['author', '~>', 'name']
        }, as: 'author_name' }
    ], where: [
        { eq: [
            { path: ['author', '~>', 'role'] },
            { binding: ['admin'] }
        ] }
    ] }
);

// (c): Function-based syntax
const result = await client.database('public').table('users').select(
    { fields: [
        (q) => q.expr('title'),
        (q) => q.expr('content'),
        (q) => q.expr(
            (r) => r.path('author', '~>', 'name')
        ).as('author_name'),
    ], where: [
        (q) => q.eq(
            (r) => r.path('author', '~>', 'role'),
            (r) => r.binding('admin')
        )
    ] }
);

Structured paths:

// (a): SQL syntax
const result = await client.query(
    `SELECT
        title,
        content,
        author: { name, email } AS author
    FROM books
    WHERE author ~> role = $1`,
    ['admin']
);

// (b): Object-based syntax
const result = await client.database('public').table('books').select(
    { fields: [
        { expr: 'title' },
        { expr: 'content' },
        { expr: {
            path: ['author', { fields: ['name', 'email'] }]
        }, as: 'author' }
    ], where: [
        { eq: [
            { path: ['author', '~>', 'role'] },
            { binding: ['admin'] }
        ] }
    ] }
);

// (c): Function-based syntax
const result = await client.database('public').table('books').select(
    { fields: [
        (q) => q.expr('title'),
        (q) => q.expr('content'),
        (q) => q.expr(
            (r) => r.path('author', (s) => s.fields('name', 'email'))
        ).as('author'),
    ], where: [
        (q) => q.eq(
            (r) => r.path('author', '~>', 'role'),
            (r) => r.binding('admin')
        )
    ] }
);