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"
    },
    {
        "title": "Untitled Marvel Project (3)",
        "release_date": "2022-07-29"
    },
    {
        "title": "Guardians of the Galaxy Vol. 3",
        "release_date": "2023-02-16"
    }
]

Use search_json to show the movies with the largest casts

This example shows how we can use SEARCH_JSON to perform a simple calculation on JSON and order by the results. The cast attribute holds an object array of details around the cast of a movie. We use the expression '$count(id)' that counts each id and returns the value back which we alias in SQL as cast_size which in turn gets used to sort the rows.

Body