The HarperDB SQL ARRAY() function was released in HarperDB 1.1.0 on 4/18/2018 and will continue to be available in all newer releases of HarperDB.
How many times have you run into a situation where you wish you could do a SQL join without getting duplicate rows back? What if we could get a list “column” returned instead? HarperDB’s ARRAY() function enables just that. In this blog post we’re going to take a look at a basic example of people with addresses and phone numbers.
Use Cases for the ARRAY() Function
Most existing systems have trouble transforming relational data into hierarchical data. Typically large batch processes or ETL jobs exist to perform these data transformations. HarperDB can perform these transformations out-of-the-box with a single SQL query. This query effectively performs the job of an ORM without the need for bloated software. Don’t think this is possible? Keep reading.
How the ARRAY() Function Works
The HarperDB ARRAY() function, forthcoming in a future release, is an aggregate function, similar to COUNT, SUM, AVG. The difference is that while standard aggregate functions will return computation results, ARRAY() returns a list of data as a field. While this may not be intuitive to those, like myself, who have been using SQL for years, it does enable the developer to create complex JSON objects with a single query. Let’s take a look at an example use case…
Example Data
We’ll be working with People, Phone Numbers, and Addresses. Each Address and/or Phone Number links back to a single Person. We have 10 person records, each with one or more phone numbers and addresses for a total of 20 addresses and 24 phone numbers.
Array Example ERD
Connecting Person and Phone Number
Let’s say I want to get all of the phone numbers for a person with ID 1. That’s fairly simple, I just query the phone number table for that person. But what happens if I also want to get the person data? I have to execute two queries and connect the data in my application.
SELECT*FROM person WHERE person_id =1SELECT*FROM phone WHERE person_id =1
Now what happens if I want to get all people and all of their phone numbers. While I’d like to do a simple join, I can’t, because I’d end up with duplicate person data.
SELECT*FROM person LEFTJOIN phone ON person.person_id = phone.person_id
So, again, I have to run two queries and aggregate the data together in my application.
In HarperDB, we have the ARRAY() aggregate function which allows us to return this data, with no duplicates, in a single query. Remember, because ARRAY() is an aggregate function that we need to have a GROUP BY clause specified. In this case, since we are selecting multiple person fields, we need to specify all of them in our GROUP BY clause. Since we included our hash, person_id, we will safely retrieve each person record.
SELECT
person.person_id,
person.first_name,
person.middle_name,
person.last_name,
person.saluation,
person.dob,
ARRAY({
type: addr.address_type,
addressLine1: addr.address_line_1,
addressLine2: addr.address_line_2,
city: addr.city,
state: addr.state,
zip: addr.zip_code,
}) as address
FROM
arr.person AS person
LEFTJOIN arr.address AS addr
ON person.person_id = addr.person_id
GROUPBY
person.person_id,
person.first_name,
person.middle_name,
person.last_name,
person.saluation,
person.dob
This returns a list of complex JSON objects where each Person object contains a list of Phone objects. For example, the complex object for person ID 1 would look like this:
Now that we’ve shown how to aggregate list data from a single table let’s take a look at how we can retrieve multiple lists within our complex JSON objects. Ordinarily, if I wanted to pull data for person, phone, and address, then I would need three SQL queries.
SELECT*FROM person WHERE person_id =1SELECT*FROM phone WHERE person_id =1SELECT*FROM address WHERE person_id =1
Now, if I were to put all three of those tables into a JOIN statement, I would receive a lot of duplicate data across all three tables. Take a look, here, at what is returned by the below SQL statement.
SELECT*FROM person
LEFTJOIN phone
ON person.person_id = phone.person_id
LEFTJOIN address
ON person.person_id = address.person_id
Moving back to HarperDB we can query with the ARRAY() function to help us out with this. However, because we are joining across multiple tables we may still see some duplicate data in the phone and address lists. This is the inherent nature of SQL JOINS. In order to solve this problem, HarperDB created the DISTINCT_ARRAY() wrapper function. This function can be placed around a standard ARRAY() function call to ensure a distinct (deduplicated) results set is returned. Now to create our complex Person object with lists of both Phone and Address we can write a SQL statement like this:
SELECT
person.person_id,
person.first_name,
person.middle_name,
person.last_name,
person.saluation,
person.dob,
DISTINCT_ARRAY(ARRAY({
type: addr.address_type,
addressLine1: addr.address_line_1,
addressLine2: addr.address_line_2,
city: addr.city,
state: addr.state,
zip: addr.zip_code,
})) as address,
DISTINCT_ARRAY(ARRAY({
type: phone.phone_type,
num: phone.number,
primaryFlag: phone.primary_flag,
})) as phone
FROM arr.person AS person
LEFTJOIN arr.address AS addr
ON person.person_id = addr.person_id
LEFTJOIN arr.phone AS phone
ON person.person_id = phone.person_id
GROUPBY
person.person_id,
person.first_name,
person.middle_name,
person.last_name,
person.saluation,
person.dob
The complex object for Person ID 1 returned from the above query looks like this
Harper fuses database, cache, messaging, and application functions into a single process, delivering web performance, simplicity, and resilience unmatched by multi-technology stacks.
.png)
.png)
