1 of 21

SQL Guide

The purpose of this guide is to describe the available functionality of HarperDB as it relates to supported SQL functionality. The SQL parser is still actively being developed and this document will be updated as more features and functionality becomes available. A high-level view of supported features can be found here.

HarperDB adheres to the concept of schemas & tables. This allows developers to isolate table structures from each other all within one database.

Update

HarperDB supports updating existing table row(s) via UPDATE statements. Multiple conditions can be applied to filter the row(s) to update. At this time selecting from one table to update another is not supported.

UPDATE dev.dog
    SET owner_name = 'Kyle'
    WHERE id IN (1, 2)

SQL Functions

This SQL keywords reference contains the SQL functions available in HarperDB.

Functions

Aggregate

Keyword

Syntax

Description

*For more information on ARRAY() and DISTINCT_ARRAY() see this blog.

Conversion

Keyword

Syntax

Description

Date & Time

Logical

Mathematical

String

Operators

Logical Operators

Queries

General

Joins

Predicates

Statements

Insert

HarperDB supports inserting 1 to n records into a table. The primary key must be unique (not used by any other record). If no primary key is provided, it will be assigned an auto-generated UUID. HarperDB does not support selecting from one table to insert into another at this time.

INSERT INTO dev.dog (id, dog_name, age, breed_id)
  VALUES(1, 'Penny', 5, 347), (2, 'Kato', 4, 347)

SQL Features Matrix

HarperDB provides access to most SQL functions, and we’re always expanding that list. Check below to see if we cover what you need. If not, feel free to .

INSERT

UPDATE

DELETE

SELECT

FROM

SQL Date Functions

HarperDB utilizes in all internal SQL operations. This means that date values passed into any of the functions below will be assumed to be in UTC or in a format that can be translated to UTC.

When parsing date values passed to SQL date functions in HDB, we first check for formats, then for date-time format and then fall back to new Date(date_string)if a known format is not found.

CURRENT_DATE()

Returns the current date in UTC in YYYY-MM-DD String format.

CURRENT_TIME()

Returns the current time in UTC in HH:mm:ss.SSS String format.

CURRENT_TIMESTAMP

Referencing this variable will evaluate as the current Unix Timestamp in milliseconds.

DATE([date_string])

Formats and returns the date_string argument in UTC in YYYY-MM-DDTHH:mm:ss.SSSZZ String format.

If a date_string is not provided, the function will return the current UTC date/time value in the return format defined above.

DATE_ADD(date, value, interval)

Adds the defined amount of time to the date provided in UTC and returns the resulting Unix Timestamp in milliseconds. Accepted interval values: Either string value (key or shorthand) can be passed as the interval argument.

DATE_DIFF(date_1, date_2[, interval])

Returns the difference between the two date values passed based on the interval as a Number. If an interval is not provided, the function will return the difference value in milliseconds.

Accepted interval values:

years
months
weeks
days
hours
minutes
seconds

DATE_FORMAT(date, format)

DATE_SUB(date, value, interval)

Subtracts the defined amount of time from the date provided in UTC and returns the resulting Unix Timestamp in milliseconds. Accepted date_sub interval values- Either string value (key or shorthand) can be passed as the interval argument.

EXTRACT(date, date_part)

Extracts and returns the date_part requested as a String value. Accepted date_part values below show value returned for date = “2020-03-26T15:13:02.041+000”

GETDATE()

Returns the current Unix Timestamp in milliseconds.

GET_SERVER_TIME()

Returns the current date/time value based on the server’s timezone in YYYY-MM-DDTHH:mm:ss.SSSZZ String format.

OFFSET_UTC(date, offset)

Returns the UTC date time value with the offset provided included in the return String value formatted as YYYY-MM-DDTHH:mm:ss.SSSZZ. The offset argument will be added as minutes unless the value is less than 16 and greater than -16, in which case it will be treated as hours.

NOW()

Returns the current Unix Timestamp in milliseconds.

SELECT d.id, d.dog_name, d.owner_name, b.name, b.section
    FROM dev.dog AS d
    INNER JOIN dev.breed AS b ON d.breed_id = b.id
    WHERE d.owner_name IN ('Kyle', 'Zach', 'Stephen')
    AND b.section = 'Mutt'
    ORDER BY d.dog_name

SQL JSON Search

HarperDB automatically indexes all top level attributes in a row / object written to a table. However, any attributes which holds JSON does not have its nested attributes indexed. In order to make searching and/or transforming these JSON documents easy, HarperDB offers a special SQL function called SEARCH_JSON. The SEARCH_JSON function works in SELECT & WHERE clauses allowing queries to perform powerful filtering on any element of your JSON by implementing the JSONata library into our SQL engine.

Syntax

SEARCH_JSON(expression, attribute)

Executes the supplied string expression against data of the defined top level attribute for each row. The expression both filters and defines output from the JSON document.

Example 1

Search a string array

Here are two records in the database:

[
    {
      "id": 1,
      "name": ["Harper", "Penny"]
    },
    {
      "id": 2,
      "name": ["Penny"]
    }
]

Here is a simple query that gets any record with "Harper" found in the name.

SELECT *
FROM dev.dog
WHERE search_json('"Harper" in *', name)

Example 2

The purpose of this query is to give us every movie where at least two of our favorite actors from Marvel films have acted together. The results will return the movie title, the overview, release date and an object array of the actor’s name and their character name in the movie.

Both function calls evaluate the credits.cast attribute, this attribute is an object array of every cast member in a movie.

SELECT m.title,
    m.overview,
    m.release_date,
    SEARCH_JSON($[name in ["Robert Downey Jr.", "Chris Evans", "Scarlett Johansson", "Mark Ruffalo", "Chris Hemsworth", "Jeremy Renner", "Clark Gregg", "Samuel L. Jackson", "Gwyneth Paltrow", "Don Cheadle"]].{"actor": name, "character": character}, c.`cast`) AS characters
FROM movies.credits c
    INNER JOIN movies.movie m
    ON c.movie_id = m.id
WHERE SEARCH_JSON($count($[name in ["Robert Downey Jr.", "Chris Evans", "Scarlett Johansson", "Mark Ruffalo", "Chris Hemsworth", "Jeremy Renner", "Clark Gregg", "Samuel L. Jackson", "Gwyneth Paltrow", "Don Cheadle"]]), c.`cast`) >= 2

A sample of this data from the movie The Avengers looks like

[
    {
        "cast_id": 46,
        "character": "Tony Stark / Iron Man",
        "credit_id": "52fe4495c3a368484e02b251",
        "gender": "male",
        "id": 3223,
        "name": "Robert Downey Jr.",
        "order": 0
    },
    {
        "cast_id": 2,
        "character": "Steve Rogers / Captain America",
        "credit_id": "52fe4495c3a368484e02b19b",
        "gender": "male",
        "id": 16828,
        "name": "Chris Evans",
        "order": 1
    },
    {
        "cast_id": 307,
        "character": "Bruce Banner / The Hulk",
        "credit_id": "5e85e8083344c60015411cfa",
        "gender": "male",
        "id": 103,
        "name": "Mark Ruffalo",
        "order": 2
    }
]

Let’s break down the SEARCH_JSON function call in the SELECT:

SEARCH_JSON(
    $[name in [
        "Robert Downey Jr.",
        "Chris Evans",
        "Scarlett Johansson",
        "Mark Ruffalo",
        "Chris Hemsworth",
        "Jeremy Renner",
        "Clark Gregg",
        "Samuel L. Jackson",
        "Gwyneth Paltrow",
        "Don Cheadle"
    ]].{
        "actor": name,
        "character": character
    },
    c.`cast`
)

The first argument passed to SEARCH_JSON is the expression to execute against the second argument which is the cast attribute on the credits table. This expression will execute for every row. Looking into the expression it starts with “$[…]” this tells the expression to iterate all elements of the cast array.

Then the expression tells the function to only return entries where the name attribute matches any of the actors defined in the array:

name in ["Robert Downey Jr.", "Chris Evans", "Scarlett Johansson", "Mark Ruffalo", "Chris Hemsworth", "Jeremy Renner", "Clark Gregg", "Samuel L. Jackson", "Gwyneth Paltrow", "Don Cheadle"]

So far, we’ve iterated the array and filtered out rows, but we also want the results formatted in a specific way, so we’ve chained an expression on our filter with: {“actor”: name, “character”: character}. This tells the function to create a specific object for each matching entry.

Sample Result

[
    {
        "actor": "Robert Downey Jr.",
        "character": "Tony Stark / Iron Man"
    },
    {
        "actor": "Chris Evans",
        "character": "Steve Rogers / Captain America"
    },
    {
        "actor": "Mark Ruffalo",
        "character": "Bruce Banner / The Hulk"
    }
]

Just having the SEARCH_JSON function in our SELECT is powerful, but given our criteria it would still return every other movie that doesn’t have our matching actors, in order to filter out the movies we do not want we also use SEARCH_JSON in the WHERE clause.

This function call in the WHERE clause is similar, but we don’t need to perform the same transformation as occurred in the SELECT:

SEARCH_JSON(
    $count(
    $[name in [
            "Robert Downey Jr.",
            "Chris Evans",
            "Scarlett Johansson",
            "Mark Ruffalo",
            "Chris Hemsworth",
            "Jeremy Renner",
            "Clark Gregg",
            "Samuel L. Jackson",
            "Gwyneth Paltrow",
            "Don Cheadle"
        ]]
    ),
    c.`cast`
) >= 2

As seen above we execute the same name filter against the cast array, the primary difference is we are wrapping the filtered results in $count(…). As it looks this returns a count of the results back which we then use against our SQL comparator of >= 2.

To see further SEARCH_JSON examples in action view our Postman Collection that provides a sample schema & data with query examples: https://api.harperdb.io/

To learn more about how to build expressions check out the JSONata documentation: http://docs.jsonata.org/overview

geoLength

Takes a GeoJSON and measures its length in the specified units (default is kilometers).

Syntax

geoLength(geoJSON[, units])

Parameters

Parameter

Description

Example 1

Calculate the length, in kilometers, of a manually passed GeoJSON linestring.

SELECT geoLength('{
    "type": "Feature",
    "geometry": {
        "type": "LineString",
        "coordinates": [
            [-104.97963309288025,39.76163265441438],
            [-104.9823260307312,39.76365323407955],
            [-104.99193906784058,39.75616442110704]
        ]
    }
}')

Example 2

Find all data plus the calculated length in miles of the GeoJSON, restrict the response to only lengths less than 5 miles, and return the data in order of lengths smallest to largest.

SELECT *, geoLength(geo_data, 'miles') as length
FROM dev.locations
WHERE geoLength(geo_data, 'miles') < 5
ORDER BY length ASC

geoArea

The geoArea() function returns the area of one or more features in square meters.

Syntax

geoArea(geoJSON)

Parameters

Parameter

Description

Example 1

Calculate the area, in square meters, of a manually passed GeoJSON polygon.

SELECT geoArea('{
    "type":"Feature",
    "geometry":{
        "type":"Polygon",
        "coordinates":[[
            [0,0],
            [0.123456,0],
            [0.123456,0.123456],
            [0,0.123456]
        ]]
    }
}')

Example 2

Find all records that have an area less than 1 square mile (or 2589988 square meters).

SELECT * FROM dev.locations
WHERE geoArea(geo_data) < 2589988

geoNear

Determines if point1 and point2 are within a specified distance from each other, default units are kilometers. Returns a Boolean.

Syntax

geoNear(point1, point2, distance[, units])

Parameters

Parameter

Description

Example 1

Return all locations within 50 miles of a given point.

SELECT *
FROM dev.locations
WHERE geoNear('[-104.979127,39.761563]', geo_data, 50, 'miles')

Example 2

Return all locations within 2 degrees of the earth of a given point. (Each degree lat/long is about 69 miles [111 kilometers]). Return all data and the distance in miles, sorted by ascending distance.

SELECT *, geoDistance('[-104.979127,39.761563]', geo_data, 'miles') as distance
FROM dev.locations
WHERE geoNear('[-104.979127,39.761563]', geo_data, 2, 'degrees')
ORDER BY distance ASC

geoEqual

Determines if two GeoJSON features are the same type and have identical X,Y coordinate values. For more information see https://developers.arcgis.com/documentation/spatial-references/. Returns a Boolean.

Syntax

geoEqual(geo1, geo2)

Parameters

Parameter

Description

Example

Find HarperDB Headquarters within all locations within the database.

SELECT *
FROM dev.locations
WHERE geoEqual(geo_data, '{
    "type": "Feature",
    "properties": {
      "name": "HarperDB Headquarters"
    },
    "geometry": {
        "type": "Polygon",
        "coordinates": [[
            [-104.98060941696167,39.760704817357905],
            [-104.98053967952728,39.76065120861263],
            [-104.98055577278137,39.760642961109674],
            [-104.98037070035934,39.76049450588716],
            [-104.9802714586258,39.76056254790385],
            [-104.9805235862732,39.76076461167841],
            [-104.98060941696167,39.760704817357905]
        ]]
    }
}')

geoDistance

#geoDistance Calculates the distance between two points in units (default is kilometers).

Syntax

geoDistance(point1, point2[, units])

Parameters

Parameter

Description

Example 1

Calculate the distance, in miles, between HarperDB’s headquarters and the Washington Monument.

Example 2

Find all locations that are within 40 kilometers of a given point, return that distance in miles, and sort by distance in an ascending order.

geoDifference

Returns a new polygon with the difference of the second polygon clipped from the first polygon.

Syntax

geoDifference(polygon1, polygon2)

Parameters

Parameter

Description

Example

Return a GeoJSON Polygon that removes City Park (polygon2) from Colorado (polygon1).

geoConvert

Converts a series of coordinates into a GeoJSON of the specified type.

Syntax

geoConvert(coordinates, geo_type[, properties])

Parameters

Parameter

Description

Example

Convert a given coordinate into a GeoJSON point with specified properties.

geoContains

Determines if geo2 is completely contained by geo1. Returns a Boolean.

Syntax

geoContains(geo1, geo2)

Parameters

Parameter

Description

Example 1

Return all locations within the state of Colorado (passed as a GeoJSON string).

Example 2

Return all locations which contain HarperDB Headquarters.

SELECT geoDifference('{
    "type": "Feature",
    "properties": {
      "name":"Colorado"
    },
    "geometry": {
        "type": "Polygon",
        "coordinates": [[
            [-109.072265625,37.00255267215955],
            [-102.01904296874999,37.00255267215955],
            [-102.01904296874999,41.0130657870063],
            [-109.072265625,41.0130657870063],
            [-109.072265625,37.00255267215955]
        ]]
      }
    }',
    '{
        "type": "Feature",
        "properties": {
          "name":"City Park"
        },
        "geometry": {
            "type": "Polygon",
            "coordinates": [[
                [-104.95973110198975,39.7543828214657],
                [-104.95955944061278,39.744781185675386],
                [-104.95904445648193,39.74422022399989],
                [-104.95835781097412,39.74402223643582],
                [-104.94097709655762,39.74392324244047],
                [-104.9408483505249,39.75434982844515],
                [-104.95973110198975,39.7543828214657]
            ]]
        }
    }'
)

SELECT *
FROM dev.locations
WHERE geoContains('{
    "type": "Feature",
    "properties": {
      "name":"Colorado"
    },
    "geometry": {
        "type": "Polygon",
        "coordinates": [[
            [-109.072265625,37.00255267],
            [-102.01904296874999,37.00255267],
            [-102.01904296874999,41.01306579],
            [-109.072265625,41.01306579],
            [-109.072265625,37.00255267]
        ]]
    }
}', geo_data)

SELECT *
FROM dev.locations
WHERE geoContains(geo_data, '{
    "type": "Feature",
    "properties": {
      "name": "HarperDB Headquarters"
    },
    "geometry": {
        "type": "Polygon",
        "coordinates": [[
            [-104.98060941696167,39.760704817357905],
            [-104.98053967952728,39.76065120861263],
            [-104.98055577278137,39.760642961109674],
            [-104.98037070035934,39.76049450588716],
            [-104.9802714586258,39.76056254790385],
            [-104.9805235862732,39.76076461167841],
            [-104.98060941696167,39.760704817357905]
        ]]
    }
}')

SELECT *
FROM dev.locations
WHERE geoCrosses(
    geo_data,
    '{
        "type": "Feature",
        "properties": {
          "name": "Highway I-25"
        },
        "geometry": {
            "type": "LineString",
            "coordinates": [
                [-104.9139404296875,41.00477542222947],
                [-105.0238037109375,39.715638134796336],
                [-104.853515625,39.53370327008705],
                [-104.853515625,38.81403111409755],
                [-104.61181640625,38.39764411353178],
                [-104.8974609375,37.68382032669382],
                [-104.501953125,37.00255267215955]
            ]
        }
    }'
)

SQL Guide

Update

SQL Functions

Functions

Aggregate

Conversion

Date & Time

Logical

Mathematical

String

Operators

Logical Operators

Queries

General

Joins

Predicates

Statements

SQL Guide

Update

SQL Functions

Functions

Aggregate

Conversion

Date & Time

Logical

Mathematical

String

Operators

Logical Operators

Queries

General

Joins

Predicates

Statements

Insert

Delete

SQL Features Matrix

Select

Joins

SQL Date Functions

CURRENT_DATE()

CURRENT_TIME()

CURRENT_TIMESTAMP

DATE([date_string])

DATE_ADD(date, value, interval)

DATE_DIFF(date_1, date_2[, interval])

DATE_FORMAT(date, format)

DATE_SUB(date, value, interval)

EXTRACT(date, date_part)

GETDATE()

GET_SERVER_TIME()

OFFSET_UTC(date, offset)

NOW()

SQL Reserved Word

RESERVED WORD LIST

SQL Geospatial Functions

SQL JSON Search

Syntax

Example 1

Search a string array

Example 2

geoLength

Syntax

Parameters

Example 1

Example 2

geoArea

Syntax

Parameters

Example 1

Example 2

geoNear

Syntax

Parameters

Example 1

Example 2

geoEqual

Syntax

Parameters

Example