Chapter 3. Working with complex data

Table of Contents

SQLAdvancedExamples Script
Working with Timestamps
Working With Arrays
Working with Records
Using ORDER BY to Sort Results
Working With Maps
Using the size() Function

In this chapter, we walk you through query examples that use complex types (arrays, maps, records). If you want to follow along with the examples, get the Examples download from http://www.oracle.com/technetwork/database/database-technologies/nosqldb/downloads/index.html and run the SQLAdvancedExamples script found in the sql folder. This creates the table and imports the data used.

SQLAdvancedExamples Script

The SQLAdvancedExamples script creates the following table:

create table Persons (
  id integer,
  firstname string,
  lastname string,
  age integer,
  income integer,
  lastLogin timestamp(4),
  address record(street string,
                 city string,
                 state string,
                 phones array(record(type enum(work, home),
                                     areacode integer,
                                     number integer
                                   )
                            )
                ),
  connections array(integer),
  expenses map(integer),
  primary key (id)
); 

The script also imports the following table rows:

{
  "id":1,
  "firstname":"David",
  "lastname":"Morrison",
  "age":25,
  "income":100000,
  "lastLogin" : "2016-10-29T18:43:59.8319",
  "address":{"street":"150 Route 2",
             "city":"Antioch",
             "state":"TN",
             "zipcode" : 37013,
             "phones":[{"type":"home", "areacode":423, 
                        "number":8634379}]
            },
  "connections":[2, 3],
  "expenses":{"food":1000, "gas":180}
}

{
  "id":2,
  "firstname":"John",
  "lastname":"Anderson",
  "age":35,
  "income":100000,
  "lastLogin" : "2016-11-28T13:01:11.2088",
  "address":{"street":"187 Hill Street",
             "city":"Beloit",
             "state":"WI",
             "zipcode" : 53511,
             "phones":[{"type":"home", "areacode":339, 
             "number":1684972}]
            },
  "connections":[1, 3],
  "expenses":{"books":100, "food":1700, "travel":2100}
}

{
  "id":3,
  "firstname":"John",
  "lastname":"Morgan",
  "age":38,
  "income":100000000,
  "lastLogin" : "2016-11-29T08:21:35.4971",
  "address":{"street":"187 Aspen Drive",
             "city":"Middleburg",
             "state":"FL",
             "phones":[{"type":"work", "areacode":305, 
                        "number":1234079},
                      {"type":"home", "areacode":305, 
                       "number":2066401}
                     ]
            },
  "connections":[1, 4, 2],
  "expenses":{"food":2000, "travel":700, "gas":10}
}

{
  "id":4,
  "firstname":"Peter",
  "lastname":"Smith",
  "age":38,
  "income":80000,
  "lastLogin" : "2016-10-19T09:18:05.5555",
  "address":{"street":"364 Mulberry Street",
             "city":"Leominster",
             "state":"MA",
             "phones":[{"type":"work", "areacode":339, 
                        "number":4120211},
                      {"type":"work", "areacode":339, 
                       "number":8694021},
                      {"type":"home", "areacode":339, 
                       "number":1205678},
                      {"type":"home", "areacode":305, 
                       "number":8064321}
                     ]
            },
  "connections":[3, 5, 1, 2],
  "expenses":{"food":6000, "books":240, "clothes":2000, "shoes":1200}
}

{
  "id":5,
  "firstname":"Dana",
  "lastname":"Scully",
  "age":47,
  "income":400000,
  "lastLogin" : "2016-11-08T09:16:46.3929",
  "address":{"street":"427 Linden Avenue",
             "city":"Monroe Township",
             "state":"NJ",
             "phones":[{"type":"work", "areacode":201, 
                        "number":3213267},
                      {"type":"work", "areacode":201, 
                       "number":8765421},
                      {"type":"home", "areacode":339, 
                       "number":3414578}
                     ]
           },
  "connections":[2, 4, 1, 3],
  "expenses":{"food":900, "shoes":1000, "clothes":1500}
}

You run the SQLAdvancedExamples script using the load command:

> cd <installdir>/examples/sql
> java -jar <KVHOME>/lib/sql.jar -helper-hosts <host>:<port> \
-store <storename> load \
-file <KVHOME>/examples/sql/SQLAdvancedExamples.cli

Note

The Persons table models people that might be connected to other people in the same table. These connections are stored in the "connections" column, which is an array holding the ids of other people that a person is connected with. It is assumed that the entries of each "connections" array are sorted (in descending order) by a measure of the strength of the connection. For example, person 3 is most strongly connected with person 1, less strongly connected with person 4, and the least strongly connected with person 2.

The Persons table includes an "expenses" column, which is a map of integers. It stores, for each person, the amount of money spent on various categories of items. Because the categories may be different for each person, and/or because we may want to add or delete categories dynamically (without changing the schema of the table), it makes sense to model this information in a map.