Advanced JSON SQL Examples
Create movies database
Create a new database called "movies" using the 'create_database' operation.
Note: Creating a database is optional, if one is not created HarperDB will default to using a database named data
Body
{
"operation": "create_database",
"database": "movies"
}Response: 200
{
"message": "database 'movies' successfully created"
}Create movie Table
Creates a new table called "movie" inside the database "movies" using the ‘create_table’ operation.
Body
{
"operation": "create_table",
"database": "movies",
"table": "movie",
"primary_key": "id"
}Response: 200
{
"message": "table 'movies.movie' successfully created."
}Create credits Table
Creates a new table called "credits" inside the database "movies" using the ‘create_table’ operation.
Body
{
"operation": "create_table",
"database": "movies",
"table": "credits",
"primary_key": "movie_id"
}Response: 200
{
"message": "table 'movies.credits' successfully created."
}Bulk Insert movie Via CSV
Inserts data from a hosted CSV file into the "movie" table using the 'csv_url_load' operation.
Body
{
"operation": "csv_url_load",
"database": "movies",
"table": "movie",
"csv_url": "https://search-json-sample-data.s3.us-east-2.amazonaws.com/movie.csv"
}Response: 200
{
"message": "Starting job with id 1889eee4-23c1-4945-9bb7-c805fc20726c"
}Bulk Insert credits Via CSV
Inserts data from a hosted CSV file into the "credits" table using the 'csv_url_load' operation.
Body
{
"operation": "csv_url_load",
"database": "movies",
"table": "credits",
"csv_url": "https://search-json-sample-data.s3.us-east-2.amazonaws.com/credits.csv"
}Response: 200
{
"message": "Starting job with id 3a14cd74-67f3-41e9-8ccd-45ffd0addc2c",
"job_id": "3a14cd74-67f3-41e9-8ccd-45ffd0addc2c"
}View raw data
In the following example we will be running expressions on the keywords & production_companies attributes, so for context we are displaying what the raw data looks like.
Body
{
"operation": "sql",
"sql": "SELECT title, rank, keywords, production_companies FROM movies.movie ORDER BY rank LIMIT 10"
}Response: 200
[
{
"title": "Ad Astra",
"rank": 1,
"keywords": [
{
"id": 305,
"name": "moon"
},
{
"id": 697,
"name": "loss of loved one"
},
{
"id": 839,
"name": "planet mars"
},
{
"id": 14626,
"name": "astronaut"
},
{
"id": 157265,
"name": "moon colony"
},
{
"id": 162429,
"name": "solar system"
},
{
"id": 240119,
"name": "father son relationship"
},
{
"id": 244256,
"name": "near future"
},
{
"id": 257878,
"name": "planet neptune"
},
{
"id": 260089,
"name": "space walk"
}
],
"production_companies": [
{
"id": 490,
"name": "New Regency Productions",
"origin_country": ""
},
{
"id": 79963,
"name": "Keep Your Head",
"origin_country": ""
},
{
"id": 73492,
"name": "MadRiver Pictures",
"origin_country": ""
},
{
"id": 81,
"name": "Plan B Entertainment",
"origin_country": "US"
},
{
"id": 30666,
"name": "RT Features",
"origin_country": "BR"
},
{
"id": 30148,
"name": "Bona Film Group",
"origin_country": "CN"
},
{
"id": 22213,
"name": "TSG Entertainment",
"origin_country": "US"
}
]
},
{
"title": "Extraction",
"rank": 2,
"keywords": [
{
"id": 3070,
"name": "mercenary"
},
{
"id": 4110,
"name": "mumbai (bombay), india"
},
{
"id": 9717,
"name": "based on comic"
},
{
"id": 9730,
"name": "crime boss"
},
{
"id": 11107,
"name": "rescue mission"
},
{
"id": 18712,
"name": "based on graphic novel"
},
{
"id": 265216,
"name": "dhaka (dacca), bangladesh"
}
],
"production_companies": [
{
"id": 106544,
"name": "AGBO",
"origin_country": "US"
},
{
"id": 109172,
"name": "Thematic Entertainment",
"origin_country": "US"
},
{
"id": 92029,
"name": "TGIM Films",
"origin_country": "US"
}
]
},
{
"title": "To the Beat! Back 2 School",
"rank": 3,
"keywords": [
{
"id": 10873,
"name": "school"
}
],
"production_companies": []
},
{
"title": "Bloodshot",
"rank": 4,
"keywords": [
{
"id": 2651,
"name": "nanotechnology"
},
{
"id": 9715,
"name": "superhero"
},
{
"id": 9717,
"name": "based on comic"
},
{
"id": 164218,
"name": "psychotronic"
},
{
"id": 255024,
"name": "shared universe"
},
{
"id": 258575,
"name": "valiant comics"
}
],
"production_companies": [
{
"id": 34,
"name": "Sony Pictures",
"origin_country": "US"
},
{
"id": 10246,
"name": "Cross Creek Pictures",
"origin_country": "US"
},
{
"id": 6573,
"name": "Mimran Schur Pictures",
"origin_country": "US"
},
{
"id": 333,
"name": "Original Film",
"origin_country": "US"
},
{
"id": 103673,
"name": "The Hideaway Entertainment",
"origin_country": "US"
},
{
"id": 124335,
"name": "Valiant Entertainment",
"origin_country": "US"
},
{
"id": 5,
"name": "Columbia Pictures",
"origin_country": "US"
},
{
"id": 1225,
"name": "One Race",
"origin_country": "US"
},
{
"id": 30148,
"name": "Bona Film Group",
"origin_country": "CN"
}
]
},
{
"title": "The Call of the Wild",
"rank": 5,
"keywords": [
{
"id": 818,
"name": "based on novel or book"
},
{
"id": 4542,
"name": "gold rush"
},
{
"id": 15162,
"name": "dog"
},
{
"id": 155821,
"name": "sled dogs"
},
{
"id": 189390,
"name": "yukon"
},
{
"id": 207928,
"name": "19th century"
},
{
"id": 259987,
"name": "cgi animation"
},
{
"id": 263806,
"name": "1890s"
}
],
"production_companies": [
{
"id": 787,
"name": "3 Arts Entertainment",
"origin_country": "US"
},
{
"id": 127928,
"name": "20th Century Studios",
"origin_country": "US"
},
{
"id": 22213,
"name": "TSG Entertainment",
"origin_country": "US"
}
]
},
{
"title": "Sonic the Hedgehog",
"rank": 6,
"keywords": [
{
"id": 282,
"name": "video game"
},
{
"id": 6054,
"name": "friendship"
},
{
"id": 10842,
"name": "good vs evil"
},
{
"id": 41645,
"name": "based on video game"
},
{
"id": 167043,
"name": "road movie"
},
{
"id": 172142,
"name": "farting"
},
{
"id": 188933,
"name": "bar fight"
},
{
"id": 226967,
"name": "amistad"
},
{
"id": 245230,
"name": "live action remake"
},
{
"id": 258111,
"name": "fantasy"
},
{
"id": 260223,
"name": "videojuego"
}
],
"production_companies": [
{
"id": 333,
"name": "Original Film",
"origin_country": "US"
},
{
"id": 10644,
"name": "Blur Studios",
"origin_country": "US"
},
{
"id": 77884,
"name": "Marza Animation Planet",
"origin_country": "JP"
},
{
"id": 4,
"name": "Paramount",
"origin_country": "US"
},
{
"id": 113750,
"name": "SEGA",
"origin_country": "JP"
},
{
"id": 100711,
"name": "DJ2 Entertainment",
"origin_country": ""
},
{
"id": 24955,
"name": "Paramount Animation",
"origin_country": "US"
}
]
},
{
"title": "Birds of Prey (and the Fantabulous Emancipation of One Harley Quinn)",
"rank": 7,
"keywords": [
{
"id": 849,
"name": "dc comics"
},
{
"id": 9717,
"name": "based on comic"
},
{
"id": 187056,
"name": "woman director"
},
{
"id": 229266,
"name": "dc extended universe"
}
],
"production_companies": [
{
"id": 9993,
"name": "DC Entertainment",
"origin_country": "US"
},
{
"id": 82968,
"name": "LuckyChap Entertainment",
"origin_country": "GB"
},
{
"id": 103462,
"name": "Kroll & Co Entertainment",
"origin_country": "US"
},
{
"id": 174,
"name": "Warner Bros. Pictures",
"origin_country": "US"
},
{
"id": 429,
"name": "DC Comics",
"origin_country": "US"
},
{
"id": 128064,
"name": "DC Films",
"origin_country": "US"
},
{
"id": 101831,
"name": "Clubhouse Pictures",
"origin_country": "US"
}
]
},
{
"title": "Justice League Dark: Apokolips War",
"rank": 8,
"keywords": [
{
"id": 849,
"name": "dc comics"
}
],
"production_companies": [
{
"id": 2785,
"name": "Warner Bros. Animation",
"origin_country": "US"
},
{
"id": 9993,
"name": "DC Entertainment",
"origin_country": "US"
},
{
"id": 429,
"name": "DC Comics",
"origin_country": "US"
}
]
},
{
"title": "Parasite",
"rank": 9,
"keywords": [
{
"id": 1353,
"name": "underground"
},
{
"id": 5318,
"name": "seoul"
},
{
"id": 5732,
"name": "birthday party"
},
{
"id": 5752,
"name": "private lessons"
},
{
"id": 9866,
"name": "basement"
},
{
"id": 10453,
"name": "con artist"
},
{
"id": 11935,
"name": "working class"
},
{
"id": 12565,
"name": "psychological thriller"
},
{
"id": 13126,
"name": "limousine driver"
},
{
"id": 14514,
"name": "class differences"
},
{
"id": 14864,
"name": "rich poor"
},
{
"id": 17997,
"name": "housekeeper"
},
{
"id": 18015,
"name": "tutor"
},
{
"id": 18035,
"name": "family"
},
{
"id": 33421,
"name": "crime family"
},
{
"id": 173272,
"name": "flood"
},
{
"id": 188861,
"name": "smell"
},
{
"id": 198673,
"name": "unemployed"
},
{
"id": 237462,
"name": "wealthy family"
}
],
"production_companies": [
{
"id": 7036,
"name": "CJ Entertainment",
"origin_country": "KR"
},
{
"id": 4399,
"name": "Barunson E&A",
"origin_country": "KR"
}
]
},
{
"title": "Star Wars: The Rise of Skywalker",
"rank": 10,
"keywords": [
{
"id": 161176,
"name": "space opera"
}
],
"production_companies": [
{
"id": 1,
"name": "Lucasfilm",
"origin_country": "US"
},
{
"id": 11461,
"name": "Bad Robot",
"origin_country": "US"
},
{
"id": 2,
"name": "Walt Disney Pictures",
"origin_country": "US"
},
{
"id": 120404,
"name": "British Film Commission",
"origin_country": ""
}
]
}
]Simple search_json call
This query uses search_json to convert the keywords object array to a simple string array. The expression '[name]' tells the function to extract all values for the name attribute and wrap them in an array.
Body
{
"operation": "sql",
"sql": "SELECT title, rank, search_json('[name]', keywords) as keywords FROM movies.movie ORDER BY rank LIMIT 10"
}Response: 200
[
{
"title": "Ad Astra",
"rank": 1,
"keywords": [
"moon",
"loss of loved one",
"planet mars",
"astronaut",
"moon colony",
"solar system",
"father son relationship",
"near future",
"planet neptune",
"space walk"
]
},
{
"title": "Extraction",
"rank": 2,
"keywords": [
"mercenary",
"mumbai (bombay), india",
"based on comic",
"crime boss",
"rescue mission",
"based on graphic novel",
"dhaka (dacca), bangladesh"
]
},
{
"title": "To the Beat! Back 2 School",
"rank": 3,
"keywords": [
"school"
]
},
{
"title": "Bloodshot",
"rank": 4,
"keywords": [
"nanotechnology",
"superhero",
"based on comic",
"psychotronic",
"shared universe",
"valiant comics"
]
},
{
"title": "The Call of the Wild",
"rank": 5,
"keywords": [
"based on novel or book",
"gold rush",
"dog",
"sled dogs",
"yukon",
"19th century",
"cgi animation",
"1890s"
]
},
{
"title": "Sonic the Hedgehog",
"rank": 6,
"keywords": [
"video game",
"friendship",
"good vs evil",
"based on video game",
"road movie",
"farting",
"bar fight",
"amistad",
"live action remake",
"fantasy",
"videojuego"
]
},
{
"title": "Birds of Prey (and the Fantabulous Emancipation of One Harley Quinn)",
"rank": 7,
"keywords": [
"dc comics",
"based on comic",
"woman director",
"dc extended universe"
]
},
{
"title": "Justice League Dark: Apokolips War",
"rank": 8,
"keywords": [
"dc comics"
]
},
{
"title": "Parasite",
"rank": 9,
"keywords": [
"underground",
"seoul",
"birthday party",
"private lessons",
"basement",
"con artist",
"working class",
"psychological thriller",
"limousine driver",
"class differences",
"rich poor",
"housekeeper",
"tutor",
"family",
"crime family",
"flood",
"smell",
"unemployed",
"wealthy family"
]
},
{
"title": "Star Wars: The Rise of Skywalker",
"rank": 10,
"keywords": [
"space opera"
]
}
]Use search_json in a where clause
This example shows how we can use SEARCH_JSON to filter out records in a WHERE clause. The production_companies attribute holds an object array of companies that produced each movie, we want to only see movies which were produced by Marvel Studios. Our expression is a filter '$[name="Marvel Studios"]' this tells the function to iterate the production_companies array and only return entries where the name is "Marvel Studios".
Body
{
"operation": "sql",
"sql": "SELECT title, release_date FROM movies.movie where search_json('$[name=\"Marvel Studios\"]', production_companies) IS NOT NULL ORDER BY release_date"
}Response: 200
[
{
"title": "Iron Man",
"release_date": "2008-04-30"
},
{
"title": "The Incredible Hulk",
"release_date": "2008-06-12"
},
{
"title": "Iron Man 2",
"release_date": "2010-04-28"
},
{
"title": "Thor",
"release_date": "2011-04-21"
},
{
"title": "Captain America: The First Avenger",
"release_date": "2011-07-22"
},
{
"title": "Marvel One-Shot: The Consultant",
"release_date": "2011-09-12"
},
{
"title": "Marvel One-Shot: A Funny Thing Happened on the Way to Thor's Hammer",
"release_date": "2011-10-25"
},
{
"title": "The Avengers",
"release_date": "2012-04-25"
},
{
"title": "Marvel One-Shot: Item 47",
"release_date": "2012-09-13"
},
{
"title": "Iron Man 3",
"release_date": "2013-04-18"
},
{
"title": "Marvel One-Shot: Agent Carter",
"release_date": "2013-09-08"
},
{
"title": "Thor: The Dark World",
"release_date": "2013-10-29"
},
{
"title": "Marvel One-Shot: All Hail the King",
"release_date": "2014-02-04"
},
{
"title": "Marvel Studios: Assembling a Universe",
"release_date": "2014-03-18"
},
{
"title": "Captain America: The Winter Soldier",
"release_date": "2014-03-20"
},
{
"title": "Guardians of the Galaxy",
"release_date": "2014-07-30"
},
{
"title": "Avengers: Age of Ultron",
"release_date": "2015-04-22"
},
{
"title": "Ant-Man",
"release_date": "2015-07-14"
},
{
"title": "Captain America: Civil War",
"release_date": "2016-04-27"
},
{
"title": "Team Thor",
"release_date": "2016-08-28"
},
{
"title": "Doctor Strange",
"release_date": "2016-10-25"
},
{
"title": "Guardians of the Galaxy Vol. 2",
"release_date": "2017-04-19"
},
{
"title": "Spider-Man: Homecoming",
"release_date": "2017-07-05"
},
{
"title": "Thor: Ragnarok",
"release_date": "2017-10-25"
},
{
"title": "Black Panther",
"release_date": "2018-02-13"
},
{
"title": "Avengers: Infinity War",
"release_date": "2018-04-25"
},
{
"title": "Ant-Man and the Wasp",
"release_date": "2018-07-04"
},
{
"title": "Captain Marvel",
"release_date": "2019-03-06"
},
{
"title": "Avengers: Endgame",
"release_date": "2019-04-24"
},
{
"title": "Spider-Man: Far from Home",
"release_date": "2019-06-28"
},
{
"title": "Black Widow",
"release_date": "2020-10-28"
},
{
"title": "Untitled Spider-Man 3",
"release_date": "2021-11-04"
},
{
"title": "Thor: Love and Thunder",
"release_date": "2022-02-10"
},
{
"title": "Doctor Strange in the Multiverse of Madness",
"release_date": "2022-03-23"
},