Appendix

The following code creates Users2 and User3 tables.

CREATE TABLE Users2 (
    id INTEGER,
    income INTEGER,
    address RECORD(
        street STRING,
        city STRING,
        state STRING,
        phones ARRAY(
            RECORD( 
                area INTEGER, 
                number INTEGER, 
                kind STRING
            )
        )
    ),
    connections ARRAY(INTEGER),
    expenses MAP(INTEGER),
    PRIMARY KEY (id)
);

CREATE TABLE users3 (id INTEGER, info JSON, PRIMARY KEY(id));

The following code populates Users2 and User3 tables with sample rows.

INSERT INTO Users2 VALUES (
    0,
    1000,
    {
        "street" : "somewhere",
        "city": "Boston",
        "state" : "MA",
        "phones" : [ 
            { "area":408, "number":50, "kind":"work" },
            { "area":415, "number":60, "kind":"work" },
            { "area":NULL, "number":52, "kind":"home" }
        ]
    },
    [ 100, 20, 20, 10, 20],
    { "housing" : 1000, "clothes" : 230, "books" : 20 }
);

INSERT INTO Users2 VALUES (
    1,
    NULL,
    {
        "street" : "everywhere",
        "city": "San Fransisco",
        "state" : "CA",
        "phones" : [ 
            { "area":408, "number":50, "kind":"work" },
            { "area":408, "number":60, "kind":"home" }
        ]
    },
    [],
    { "housing" : 1000, "travel" : 300 }
);

INSERT INTO Users2 VALUES (
    2,
    2000,
    {
        "street" : "nowhere",
        "city": "San Jose",
        "state" : "CA",
        "phones" : [ ]
    },
    NULL,
    NULL
);


INSERT INTO users3 VALUES (
    0,
    {
        "income" : 1000,
        "address": {
            "street" : "somewhere",
            "city": "Boston",
            "state" : "MA",
            "phones" : [ 
                { "area":408, "number":50, "kind":"work" },
                { "area":415, "number":60, "kind":"work" },
                { "area":null, "number":52, "kind":"home" }
            ]
        },
        "expenses" : { "housing" : 1000, "clothes" : 230, "books" : 20 },
        "connections" : [ 100, 20, 20, 10, 20]
    }
);

INSERT INTO users3 VALUES (
    1,
    {
        "income" : null,
        "address": {
            "street" : "everywhere",
            "city": "San Fransisco",
            "state" : "CA",
            "phones" : [ 
                { "area":408, "number":50, "kind":"work" },
                { "area":408, "number":60, "kind":"home" },
                "4083451232"
            ]
        },
        "expenses" : { "housing" : 1000, "travel" : 300 },
        "connections" : [ ]
    }
);

INSERT INTO users3 VALUES (
    2,
    {
        "income" : 2000,
        "address": {
            "street" : "nowhere",
            "city": "San Jose",
            "state" : "CA",
            "phones" : [ ]
        },
        "expenses" : null,
        "connections" : null
    }
);

INSERT INTO users3 VALUES (3,{});

INSERT INTO users3 VALUES (
    4,
    {
        "address": {
            "street" : "top of the hill",
            "city": "San Fransisco",
            "state" : "CA",
            "phones" : { "area":408, "number":50, "kind":"work" }
        },
        "expenses" : { "housing" : 1000, "travel" : 300},
        "connections" : [ 30, 5, null ]
    }
);

INSERT INTO Users3 VALUES (
    5,
    {
        "address": {
            "street" : "end of the road",
            "city": "Portland",
            "state" : "OR"
        }
    }
);

The following are some examples of indexes.

Example 9-22 Simple Index

CREATE INDEX idx1 ON Users2 (income);

It creates an index with one entry per user in the Users table. The entry contains the income and id of the user represented by the row. The contents of this index for the sample rows in Users2 are:

[ 1000, 0 ]
[ 2000, 2 ]
[ NULL, 1 ]

If the WITH NO NULLS clause were used in the above create index statement, the last of the above 3 entries would not appear in the index.

Example 9-23 Simple Index

CREATE INDEX idx2 ON Users2 (address.state, address.city, income);

It creates an index with one entry per user in the Users table. The entry contains the state, city, income and id of the user represented by the row. The contents of this index for the sample rows in Users2 are:

[ "CA", "San Fransisco", NULL, 1 ]
[ "CA", "San Jose", 2000, 2 ]
[ "MA", "Boston", 1000, 0 ]

Example 9-24 Simple Index

CREATE INDEX idx3 ON Users2 (expenses.books);

Creates an index entry for each user. The entry contains the user's spending on books, if the user does record spending on books, or EMPTY if there is no "books" entry in expenses, or NULL if there is no expenses map at all (i.e. the value of the expenses column is NULL). The contents of this index for the sample rows in Users2 are:

[ 20, 0 ]
[ EMPTY, 1 ]
[ NULL, 2 ]

If the WITH NO NULLS clause were used in the above create index statement, only the first of the above 3 entries would appear in the index.

Example 9-25 Simple Index

CREATE INDEX idx4 ON users2 (expenses.housing, expenses.travel);

Creates an index entry for each user. The entry contains the user's housing expenses, or EMPTY if the user does not record housing expenses, and the user's travel expenses, or EMPTY if the user does not record travel expenses. If expenses is NULL, both fields in the index entry will be NULL. The contents of this index for the sample rows in Users2 are:

[ 1000, 300, 1 ]
[ 1000, EMPTY, 0 ]
[ NULL, NULL, 2 ]

Example 9-26 Multi-Key Index

CREATE INDEX midx1 ON Users2 (connections[]);

Creates an index on the elements of the connections array. The contents of this index for the sample rows in Users2 are:

[ 10, 0 ]
[ 20, 0 ]
[ 100, 0 ]
[ EMPTY, 1 ]
[ NULL, 2 ]

If the WITH NO NULLS clause were used in the above create index statement, the last 2 of the above entries would not appear in the index.

Example 9-27 Multi-Key Index

CREATE INDEX midx2 ON Users2 (address.phones[].area, income);

Creates an index on the area codes and income of users. The contents of this index for the sample rows in Users2 are:

[ 408, 1000, 0 ]
[ 408, NULL, 1 ]
[ 415, 1000, 0 ]
[ EMPTY, 2000, 2 ]
[ NULL, 1000, 0 ]

Example 9-28 Multi-Key Index

CREATE INDEX midx3 ON Users2
    (address.phones[].area, address.phones[].kind, income);

Creates an index on the area codes, the phone number kinds, and the income of users. The contents of this index for the sample rows in Users2 are:

[ 408, "work", 1000, 0 ]
[ 408, "home", NULL, 1 ]
[ 408, "work", NULL, 1 ]
[ 415, "work", 1000, 0 ]
[ EMPTY, EMPTY, 2000, 2 ]
[ NULL, "home", 1000, 0 ]

Example 9-29 Multi-Key Index

CREATE INDEX midx4 ON Users2 (
    expenses.keys(), expenses.values());

Creates an index on the fields (both keys and values) of the expenses map. The contents of this index for the sample rows in Users2 are:

[ "books", 50, 0 ]
[ "clothes", 230, 0 ]
[ "housing", 1000, 0 ]
[ "housing", 1000, 1 ]
[ "travel", 300, 1 ]
[ NULL, NULL, 2 ]

Example 9-30 Simple Typed json Index

CREATE INDEX jidx1 ON users3(info.income AS INTEGER);

It creates an index with one entry per user in the Users table. The entry contains the income and id (the primary key) of the user represented by the row. The contents of this index for the sample rows in Users3 are:

[ 1000, 0 ]
[ 2000, 2 ]
[ EMPTY, 4 ]
[ EMPTY, 5 ]
[ JNULL, 1 ]
[ NULL, 3 ]

Example 9-31 Simple Typed json Index

CREATE INDEX jidx1u ON users3 (
    info.income AS ANYATOMIC);

It creates an untyped index on info.income. The contents of this index are the same as in jidx1 above, but the values 1000 and 200 are stored as Numbers instead of integers. If the following row is added to the users3 table:

INSERT INTO users3 VALUES (
    6,
    {
        "address": {},
        "expenses" : {},
        "connections" : []
    }
);

The index will look like this:

    [ "none", 6 ]
    [ EMPTY,  5 ]
    [ EMPTY,  4 ]
    [ NULL,   3 ]
    [ 2000,   2 ]
    [ JNULL,  1 ]
    [ 1000,   0 ]

Example 9-32 Simple Typed json Index

CREATE INDEX jidx2 ON users3 (
    info.address.state AS STRING,
    info.address.city AS STRING,
    info.income AS INTEGER);

It creates an index with one entry per user in the Users table. The entry contains the state, city, income and id (the primary key) of the user represented by the row. The contents of this index for the sample rows in Users3 are:

[ "CA", "San Fransisco", EMPTY, 4 ]
[ "CA", "San Fransisco", JNULL, 1 ]
[ "CA", "San Jose", 2000, 2 ]
[ "MA", "Boston", 1000, 0 ]
[ "OR", "Portland", EMPTY, 5 ]
[ NULL, NULL, NULL, 3 ]

Example 9-33 Simple Typed json Index

CREATE INDEX jidx3 ON users3 (
    info.expenses.books AS INTEGER);

Creates an index entry for each user. The entry contains the user's spending on books, if the user does record spending on books, or EMPTY if there is no "books" entry in expenses or there is no expenses map at all, or NULL if there is no info at all (i.e. the value of the info column is NULL). The contents of this index for the sample rows in Users3 are:

[ 20, 0 ]
[ EMPTY, 1 ]
[ EMPTY, 2 ]
[ EMPTY, 4 ]
[ EMPTY, 5 ]
[ NULL, 3 ]

Example 9-34 Simple Typed json Index

CREATE INDEX jidx4 ON users3 (
    info.expenses.housing AS INTEGER,
    info.expenses.travel AS INTEGER);

Creates an index entry for each user. The entry contains 2 fields: (a) the user's housing expenses, or EMPTY if the user does not record housing expenses or there is no expenses field at all, and (b) the user's travel expenses, or EMPTY if the user does not record travel expenses or there is no expenses field at all. If info is NULL, both fields in the index entry will be NULL. The contents of this index for the sample rows in Users3 are:

[ 1000, 300, 1 ]
[ 1000, 300, 4 ]
[ 1000, EMPTY, 0 ]
[ EMPTY, EMPTY, 2 ]
[ EMPTY, EMPTY, 5 ]
[ NULL, NULL, 3 ]

Example 9-35 Multi-Key Typed json Index

CREATE INDEX jmidx1 ON users3 (
    info.connections[] AS INTEGER);

Creates an index on the elements of the connections array. The contents of this index for the sample rows in Users3 are:

[ 5, 4 ]
[ 10, 0 ]
[ 20, 0 ]
[ 30, 4 ]
[ 100, 0 ]
[ EMPTY, 1 ]
[ EMPTY, 5 ]
[ JNULL, 2 ]
[ JNULL, 4 ]
[ NULL, 3 ]

Example 9-36 Multi-Key Typed json Index

CREATE INDEX jmidx2 ON users3 (
    info.address.phones[].area AS INTEGER,
    info.income AS INTEGER);

Creates an index on the area codes and income of users. The contents of this index for the sample rows in Users3 are:

[ 408, 1000, 0 ]
[ 408, EMPTY, 4 ]
[ 408, JNULL, 1 ]
[ 415, 1000, 0 ]
[ EMPTY, 2000, 2 ]
[ EMPTY, EMPTY, 5 ]
[ EMPTY, JNULL, 1 ]
[ JNULL, 1000, 0 ]
[ NULL, NULL, 3 ]

Example 9-37 Multi-Key Typed json Index

CREATE INDEX jmidx2u ON users3 (
    info.address.phones[].area AS ANYATOMIC,
    info.income AS INTEGER);

This is a variation of the jmidx2 index, where the first index path is untyped and second is typed. The contents of jmidx2 and jmidx2u are the same, except that in jmidx2u the numeric values in the first column are stored as Numbers instead of integers.

Example 9-38 Multi-Key Typed json Index

CREATE INDEX jmidx3 ON users3 (
    info.address.phones[].area AS INTEGER,
    info.address.phones[].kind AS string,
    info.income AS INTEGER);

Creates an index on the area codes, the phone number kinds, and the income of users. The contents of this index for the sample rows in Users3 are:

[ 408, "home", JNULL, 1 ]
[ 408, "work", 1000, 0 ]
[ 408, "work", EMPTY, 4 ]
[ 408, "work", JNULL, 1 ]
[ 415, "work", 1000, 0 ]
[ EMPTY, EMPTY, 2000, 2 ]
[ EMPTY, EMPTY, EMPTY, 5 ]
[ EMPTY, EMPTY, JNULL, 1 ]
[ JNULL, "home", 1000, 0 ]
[ NULL, NULL, NULL, 3 ]

Example 9-39 Multi-Key Typed json Index

CREATE INDEX jmidx4 ON users3 (
    info.expenses.keys(),
    info.expenses.values() AS INTEGER);

Creates an index on the fields (both keys and values) of the expenses map. Notice that the keys() portion of the index definition must not declare a type. This is because the type will always be String. The contents of this index for the sample rows in Users2 are:

[ "books", 50, 0 ]
[ "clothes", 230, 0 ]
[ "housing", 1000, 0 ]
[ "housing", 1000, 1 ]
[ "housing", 1000, 4 ]
[ "travel", 300, 1 ]
[ "housing", 1000, 4 ]
[ EMPTY, EMPTY, 2 ]
[ EMPTY, EMPTY, 5 ]
[ NULL, NULL, 3 ]