Skip to content

Magic Paths

Jump to bottom Edit New page
Oxford Harrison edited this page Nov 8, 2024 · 10 revisions

Example 1:

// A basic query with JSON formatting
const result = await client.query(
    `SELECT
        name,
        { email, phone AS mobile } AS contact1,
        [ email, phone ] AS contact2
    FROM users`
);
console.log(result);
Console 
[
    {
        name: 'John Doe',
        contact1: {
            email: 'johndoed@example.com',
            mobile: '(555) 123-4567'
        },
        contact2: [ 'johndoed@example.com', '(555) 123-4567' ]
    },
    {
        name: 'Alice Blue',
        contact1: {
            email: 'aliceblue@example.com',
            mobile: '(888) 123-4567'
        },
        contact2: [ 'aliceblue@example.com', '(888) 123-4567' ]
    }
]

Example 2:

Schema 
-- The users table
CREATE TABLE users (
    id int primary key generated always as identity,
    name varchar,
    email varchar,
    phone varchar,
    role varchar,
    created_time timestamp
);
-- The books table
CREATE TABLE books (
    id int primary key generated always as identity,
    title varchar,
    content varchar,
    author int references users (id),
    created_time timestamp
);
// A basic JOIN using magic paths | MANY-TO-ONE
const result = await client.query(
    `SELECT
        title,
        content,
        author ~> name AS author_name
    FROM books
    WHERE author ~> role = $1`,
    ['admin']
);
console.log(result);
Console 
[
    {
        title: 'Beauty and the Beast',
        content: '(C) 2024 johndoed@example.com\nBeauty and the Beast...',
        author_name: 'John Doe'
    },
    {
        title: 'The Secrets of Midnight Garden',
        content: '(C) 2024 aliceblue@example.com\nThe Secrets of Midnight Garden...',
        author_name: 'Alice Blue'
    }
]

Example 3:

Schema 
-- The users table
CREATE TABLE users (
    id int primary key generated always as identity,
    name varchar,
    email varchar,
    phone varchar,
    role varchar,
    created_time timestamp
);
-- The books table
CREATE TABLE books (
    id int primary key generated always as identity,
    title varchar,
    content varchar,
    author int references users (id),
    created_time timestamp
);
// Same query but structured via JSON formatting | MANY-TO-ONE
const result = await client.query(
    `SELECT
        title,
        content,
        author: { name, email } AS author
    FROM books
    WHERE author ~> role = $1`,
    ['admin']
);
console.log(result);
Console 
[
    {
        title: 'Beauty and the Beast',
        content: '(C) 2024 johndoed@example.com\nBeauty and the Beast...',
        author: {
            name: 'John Doe',
            email: 'johndoed@example.com'
        }
    },
    {
        title: 'The Secrets of Midnight Garden',
        content: '(C) 2024 aliceblue@example.com\nThe Secrets of Midnight Garden...',
        author: {
            name: 'Alice Blue',
            email: 'aliceblue@example.com'
        }
    }
]

Example 4:

Schema 
-- The users table
CREATE TABLE users (
    id int primary key generated always as identity,
    name varchar,
    email varchar,
    phone varchar,
    role varchar,
    parent int references users (id),
    created_time timestamp
);
// A multi-level JOIN using magic paths | MANY-TO-ONE
const result = await client.query(
    `SELECT
        name,
        email,
        parent ~> parent ~> name AS grand_parent
    FROM users
    LIMIT 2
);
console.log(result);
Console 
[
    {
        name: 'John Doe',
        email: 'johndoed@example.com',
        grand_parent: 'Some user 1'
    },
    {
        name: 'Alice Blue',
        email: 'aliceblue@example.com',
        grand_parent: 'Some user 2'
    }
]

Example 5:

Schema 
-- The users table
CREATE TABLE users (
    id int primary key generated always as identity,
    name varchar,
    email varchar,
    phone varchar,
    role varchar,
    created_time timestamp
);
-- The books table
CREATE TABLE books (
    id int primary key generated always as identity,
    title varchar,
    content varchar,
    author int references users (id),
    created_time timestamp
);
// A basic one-to-many JOIN using magic paths | ONE-TO-MANY
const result = await client.query(
    `SELECT
        name,
        email,
        author <~ books ~> title AS book_title
    FROM books
    WHERE author <~ books ~> content LIKE '%(C) 2024%'`,
);
console.log(result);
Console 
[
    {
        name: 'John Doe',
        email: 'johndoed@example.com',
        book_title: 'Beauty and the Beast - Part 1'
    },
    {
        name: 'John Doe',
        email: 'johndoed@example.com',
        book_title: 'Beauty and the Beast - Part 2'
    },
    {
        name: 'Alice Blue',
        email: 'aliceblue@example.com',
        books: 'The Secrets of Midnight Garden - Part 1'
    },
    {
        name: 'Alice Blue',
        email: 'aliceblue@example.com',
        books: 'The Secrets of Midnight Garden - Part 2'
    }
]

Example 6:

Schema (as before) 
-- The users table
CREATE TABLE users (
    id int primary key generated always as identity,
    name varchar,
    email varchar,
    phone varchar,
    role varchar,
    created_time timestamp
);
-- The books table
CREATE TABLE books (
    id int primary key generated always as identity,
    title varchar,
    content varchar,
    author int references users (id),
    created_time timestamp
);
// Same query but structured and aggregated via JSON formatting | ONE-TO-MANY
const result = await client.query(
    `SELECT
        name,
        email,
        author <~ books: { title, content }[] AS books
    FROM books
    WHERE author <~ books ~> content LIKE '%(C) 2024%'`,
);
console.log(result);
Console 
[
    {
        name: 'John Doe',
        email: 'johndoed@example.com',
        books: [
            {
                title: 'Beauty and the Beast - Part 1',
                content: '(C) 2024 johndoed@example.com\nBeauty and the Beast...'
            },
            {
                title: 'Beauty and the Beast - Part 2',
                content: '(C) 2024 johndoed@example.com\nBeauty and the Beast...'
            }
        ]
    },
    {
        name: 'Alice Blue',
        email: 'aliceblue@example.com',
        books: [
            {
                title: 'The Secrets of Midnight Garden - Part 1',
                content: '(C) 2024 aliceblue@example.com\nThe Secrets of Midnight Garden...'
            },
            {
                title: 'The Secrets of Midnight Garden - Part 2',
                content: '(C) 2024 aliceblue@example.com\nThe Secrets of Midnight Garden...'
            }
        ]
    }
]

Example 7:

Schema (as before) 
-- The users table
CREATE TABLE users (
    id int primary key generated always as identity,
    name varchar,
    email varchar,
    phone varchar,
    role varchar,
    created_time timestamp
);
-- The books table
CREATE TABLE books (
    id int primary key generated always as identity,
    title varchar,
    content varchar,
    author int references users (id),
    created_time timestamp
);
// A basic multi-dimensional INSERT | MANY-TO-ONE
// TIP: for each book entry CREATED, CREATE a user with specified email
const result = await client.query(
    `INSERT INTO books (
        title,
        content,
        author ~> email
    ) VALUES (
        'Beauty and the Beast',
        '(C) 2024 johndoed@example.com\nBeauty and the Beast...',
        'johndoed@example.com'
    ), (
        'The Secrets of Midnight Garden'
        '(C) 2024 aliceblue@example.com\nThe Secrets of Midnight Garden...',
        'aliceblue@example.com'
    )`
);
console.log(result); // true
// A basic multi-dimensional UPSERT | MANY-TO-ONE
// TIP: for each book entry CREATED or UPDATED, CREATE or UPDATE a user with specified email
const result = await client.query(
    `UPSERT INTO books (
        title,
        content,
        author ~> email
    ) VALUES (
        'Beauty and the Beast',
        '(C) 2024 johndoed@example.com\nBeauty and the Beast...',
        'johndoed@example.com'
    ), (
        'The Secrets of Midnight Garden'
        '(C) 2024 aliceblue@example.com\nThe Secrets of Midnight Garden...',
        'aliceblue@example.com'
    )`
);
console.log(result); // true
// A basic multi-dimensional UPDATE | MANY-TO-ONE
// TIP: for each book entry UPDATED, CREATE or UPDATE a user with specified email
const result = await client.query(
    `UPDATE books
    SET
        title = 'Beauty and the Beast',
        content = '(C) 2024 johndoed@example.com\nBeauty and the Beast...',
        author ~> email = 'johndoed@example.com'
    `
);
console.log(result); // true

Example 8:

Schema (as before) 
-- The users table
CREATE TABLE users (
    id int primary key generated always as identity,
    name varchar,
    email varchar,
    phone varchar,
    role varchar,
    created_time timestamp
);
-- The books table
CREATE TABLE books (
    id int primary key generated always as identity,
    title varchar,
    content varchar,
    author int references users (id),
    created_time timestamp
);
// A multi-dimensional INSERT
// TIP: for each book entry CREATED, CREATE a user with specified name and email, RETURNING entire tree
const result = await client.query(
    `INSERT INTO books (
        title,
        content,
        author: (
            name,
            email
        )
    ) VALUES (
        'Beauty and the Beast',
        '(C) 2024 johndoed@example.com\nBeauty and the Beast...',
        (
            'John Doe',
            'johndoed@example.com'
        )
    ), (
        'The Secrets of Midnight Garden'
        '(C) 2024 aliceblue@example.com\nThe Secrets of Midnight Garden...',
        (
            'Alice Blue',
            'aliceblue@example.com'
        )
    ) RETURNING *`
);
console.log(result);
Console 
[
    {
        id: 1,
        title: 'Beauty and the Beast',
        content: '(C) 2024 johndoed@example.com\nBeauty and the Beast...',
        author: {
            id: 1,
            name: 'John Doe',
            email: 'johndoed@example.com',
            phone: '(555) 123-4567',
            role: 'admin',
            created_time: '2024-11-06T18:22:46.709Z'
        },
        created_time: '2024-11-06T18:22:46.709Z'
    },
    {
        id: 2,
        title: 'The Secrets of Midnight Garden',
        content: '(C) 2024 aliceblue@example.com\nThe Secrets of Midnight Garden...',
        author: {
            id: 2,
            name: 'Alice Blue',
            email: 'aliceblue@example.com',
            phone: '(888) 123-4567',
            role: 'admin',
            created_time: '2024-11-06T18:22:46.709Z'
        },
        created_time: '2024-11-06T18:22:46.709Z'
    }
]

Example 9:

Schema (as before) 
-- The users table
CREATE TABLE users (
    id int primary key generated always as identity,
    name varchar,
    email varchar,
    phone varchar,
    role varchar,
    created_time timestamp
);
-- The books table
CREATE TABLE books (
    id int primary key generated always as identity,
    title varchar,
    content varchar,
    author int references users (id),
    created_time timestamp
);
// A multi-dimensional INSERT
// TIP: for each user CREATED, CREATE a book entry with specified title and content, RETURNING entire tree
const result = await client.query(
    `INSERT INTO users (
        name,
        email,
        author <~ books: (
            title,
            content
        )
    ) VALUES (
        'John Doe',
        'johndoed@example.com',
        (
            'Beauty and the Beast',
            '(C) 2024 johndoed@example.com\nBeauty and the Beast...'
        )
    ), (
        'Alice Blue',
        'aliceblue@example.com',
        (
            'The Secrets of Midnight Garden',
            '(C) 2024 aliceblue@example.com\nThe Secrets of Midnight Garden...'
        )
    ) RETURNING *`
);
console.log(result);
Console 
[
    {
        id: 1,
        name: 'John Doe',
        email: 'johndoed@example.com',
        phone: '(555) 123-4567',
        role: 'admin',
        created_time: '2024-11-06T18:22:46.709Z'
        'author <~ books': [
            {
                id: 1,
                title: 'Beauty and the Beast',
                content: '(C) 2024 johndoed@example.com\nBeauty and the Beast...',
                created_time: '2024-11-06T18:22:46.709Z'
            }
        ]
    },
    {
        id: 1,
        name: 'Alice Blue',
        email: 'aliceblue@example.com',
        phone: '(888) 123-4567',
        role: 'admin',
        created_time: '2024-11-06T18:22:46.709Z'
        'author <~ books': [
            {
                id: 1,
                title: 'The Secrets of Midnight Garden',
                content: '(C) 2024 johndoed@example.com\nThe Secrets of Midnight Garden...',
                created_time: '2024-11-06T18:22:46.709Z'
            }
        ]
    }
]

Example 10:

Schema (as before) 
-- The users table
CREATE TABLE users (
    id int primary key generated always as identity,
    name varchar,
    email varchar,
    phone varchar,
    role varchar,
    created_time timestamp
);
-- The books table
CREATE TABLE books (
    id int primary key generated always as identity,
    title varchar,
    content varchar,
    author int references users (id),
    created_time timestamp
);
// A multi-dimensional INSERT
// TIP: for each user CREATED, CREATE two book entries with specified titles and contents, RETURNING entire tree
const result = await client.query(
    `INSERT INTO users (
        name,
        email,
        author <~ books: (
            title,
            content
        )
    ) VALUES (
        'John Doe',
        'johndoed@example.com',
        VALUES (
            (
                'Beauty and the Beast - Part 1',
                '(C) 2024 johndoed@example.com\nBeauty and the Beast...'
            ), (
                'Beauty and the Beast - Part 2',
                '(C) 2024 johndoed@example.com\nBeauty and the Beast...'
            )
        )
    ), (
        'Alice Blue',
        'aliceblue@example.com',
        VALUES (
            (
                'The Secrets of Midnight Garden - Part 1',
                '(C) 2024 aliceblue@example.com\nThe Secrets of Midnight Garden...'
            ), (
                'The Secrets of Midnight Garden - Part 2',
                '(C) 2024 aliceblue@example.com\nThe Secrets of Midnight Garden...'
            )
        )
    ) RETURNING *`
);
console.log(result);
Console 
[
    {
        id: 1,
        name: 'John Doe',
        email: 'johndoed@example.com',
        phone: '(555) 123-4567',
        role: 'admin',
        created_time: '2024-11-06T18:22:46.709Z'
        'author <~ books': [
            {
                id: 1,
                title: 'Beauty and the Beast - Part 1',
                content: '(C) 2024 johndoed@example.com\nBeauty and the Beast...',
                created_time: '2024-11-06T18:22:46.709Z'
            },
            {
                id: 2,
                title: 'Beauty and the Beast - Part 2',
                content: '(C) 2024 johndoed@example.com\nBeauty and the Beast...',
                created_time: '2024-11-06T18:22:46.709Z'
            }
        ]
    },
    {
        id: 1,
        name: 'Alice Blue',
        email: 'aliceblue@example.com',
        phone: '(888) 123-4567',
        role: 'admin',
        created_time: '2024-11-06T18:22:46.709Z'
        'author <~ books': [
            {
                id: 1,
                title: 'The Secrets of Midnight Garden - Part 1',
                content: '(C) 2024 johndoed@example.com\nThe Secrets of Midnight Garden...',
                created_time: '2024-11-06T18:22:46.709Z'
            },
            {
                id: 2,
                title: 'The Secrets of Midnight Garden - Part 2',
                content: '(C) 2024 johndoed@example.com\nThe Secrets of Midnight Garden...',
                created_time: '2024-11-06T18:22:46.709Z'
            }
        ]
    }
]
Add a custom footer
Add a custom sidebar
Clone this wiki locally