search_json as a condition, in a select with a table join

POST {{HDB_ENDPOINT}}

This example shows how we can use SEARCH_JSON to find movies where at least of 2 our favorite actors from Marvel films have acted together then list the movie, its overview, release date, and the actors names and their characters. The WHERE clause performs a count on credits.cast attribute that have the matching actors. The SELECT performs the same filter on the cast attribute and performs a transform on each object to just return the actor's name and their character.

Request Body

{"operation"=>"sql", "sql"=>"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"}

HEADERS

RESPONSES

status: OK

[{"title":"Out of Sight","overview":"Meet Jack Foley, a smooth criminal who bends the law and is determined to make one last heist. Karen Sisco is a federal marshal who chooses all the right moves … and all the wrong guys. Now they're willing to risk it all to find out if there's more between them than just the law.","release_date":"1998-06-26","characters":[{"actor":"Don Cheadle","character":"Maurice Miller"},{"actor":"Samuel L. Jackson","character":"Hejira Henry (uncredited)"}]},{"title":"Iron Man","overview":"After being held captive in an Afghan cave, billionaire engineer Tony Stark creates a unique weaponized suit of armor to fight evil.","release_date":"2008-04-30","characters":[{"actor":"Robert Downey Jr.","character":"Tony Stark / Iron Man"},{"actor":"Gwyneth Paltrow","character":"Virginia \"Pepper\" Potts"},{"actor":"Clark Gregg","character":"Phil Coulson"},{"actor":"Samuel L. Jackson","character":"Nick Fury (uncredited)"},{"actor":"Samuel L. Jackson","character":"Nick Fury"}]},{"title":"Captain America: The First Avenger","overview":"During World War II, Steve Rogers is a sickly man from Brooklyn who's transformed into super-soldier Captain America to aid in the war effort. Rogers must stop the Red Skull – Adolf Hitler's ruthless head of weaponry, and the leader of an organization that intends to use a mysterious device of untold powers for world domination.","release_date":"2011-07-22","characters":[{"actor":"Chris Evans","character":"Steve Rogers / Captain America"},{"actor":"Samuel L. Jackson","character":"Nick Fury"}]},{"title":"In Good Company","overview":"Dan Foreman is a seasoned advertisement sales executive at a high-ranking publication when a corporate takeover results in him being placed under naive supervisor Carter Duryea, who is half his age. Matters are made worse when Dan's new supervisor becomes romantically involved with his daughter an 18 year-old college student Alex.","release_date":"2004-12-29","characters":[{"actor":"Scarlett Johansson","character":"Alex Foreman"},{"actor":"Clark Gregg","character":"Mark Steckle"}]},{"title":"Zodiac","overview":"The true story of the investigation of the \"Zodiac Killer\", a serial killer who terrified the San Francisco Bay Area, taunting police with his ciphers and letters. The case becomes an obsession for three men as their lives and careers are built and destroyed by the endless trail of clues.","release_date":"2007-03-02","characters":[{"actor":"Mark Ruffalo","character":"Dave Toschi"},{"actor":"Robert Downey Jr.","character":"Paul Avery"}]},{"title":"Hard Eight","overview":"A stranger mentors a young Reno gambler who weds a hooker and befriends a vulgar casino regular.","release_date":"1996-02-28","characters":[{"actor":"Gwyneth Paltrow","character":"Clementine"},{"actor":"Samuel L. Jackson","character":"Jimmy"}]},{"title":"The Spirit","overview":"Down these mean streets a man must come.  A hero born, murdered, and born again.  A Rookie cop named Denny Colt returns from the beyond as The Spirit, a hero whose mission is to fight against the bad forces from the shadows of Central City.  The Octopus, who kills anyone unfortunate enough to see his face, has other plans; he is going to wipe out the entire city.","release_date":"2008-12-25","characters":[{"actor":"Scarlett Johansson","character":"Silken Floss"},{"actor":"Samuel L. Jackson","character":"Octopuss"}]},{"title":"S.W.A.T.","overview":"Hondo Harrelson recruits Jim Street to join an elite unit of the Los Angeles Police Department. Together they seek out more members, including tough Deke Kay and single mom Chris Sanchez. The team's first big assignment is to escort crime boss Alex Montel to prison. It seems routine, but when Montel offers a huge reward to anyone who can break him free, criminals of various stripes step up for the prize.","release_date":"2003-08-08","characters":[{"actor":"Samuel L. Jackson","character":"Sgt. Dan 'Hondo' Harrelson"},{"actor":"Jeremy Renner","character":"Brian Gamble"}]},{"title":"Iron Man 2","overview":"With the world now aware of his dual life as the armored superhero Iron Man, billionaire inventor Tony Stark faces pressure from the government, the press and the public to share his technology with the military. Unwilling to let go of his invention, Stark, with Pepper Potts and James 'Rhodey' Rhodes at his side, must forge new alliances – and confront powerful enemies.","release_date":"2010-04-28","characters":[{"actor":"Robert Downey Jr.","character":"Tony Stark / Iron Man"},{"actor":"Gwyneth Paltrow","character":"Virginia \"Pepper\" Potts"},{"actor":"Don Cheadle","character":"James \"Rhodey\" Rhodes / War Machine"},{"actor":"Scarlett Johansson","character":"Natalie Rushman / Natasha Romanoff / Black Widow"},{"actor":"Samuel L. Jackson","character":"Nick Fury"},{"actor":"Clark Gregg","character":"Phil Coulson"}]},{"title":"Thor","overview":"Against his father Odin's will, The Mighty Thor - a powerful but arrogant warrior god - recklessly reignites an ancient war. Thor is cast down to Earth and forced to live among humans as punishment. Once here, Thor learns what it takes to be a true hero when the most dangerous villain of his world sends the darkest forces of Asgard to invade Earth.","release_date":"2011-04-21","characters":[{"actor":"Chris Hemsworth","character":"Thor Odinson"},{"actor":"Clark Gregg","character":"Phil Coulson"},{"actor":"Jeremy Renner","character":"Clint Barton / Hawkeye (uncredited)"},{"actor":"Samuel L. Jackson","character":"Nick Fury (uncredited)"}]},{"title":"View from the Top","overview":"A small-town woman tries to achieve her goal of becoming a flight attendant.","release_date":"2003-03-21","characters":[{"actor":"Gwyneth Paltrow","character":"Donna"},{"actor":"Mark Ruffalo","character":"Ted Stewart"}]},{"title":"The Nanny Diaries","overview":"A college graduate goes to work as a nanny for a rich New York family. Ensconced in their home, she has to juggle their dysfunction, a new romance, and the spoiled brat in her charge.","release_date":"2007-08-24","characters":[{"actor":"Scarlett Johansson","character":"Annie Braddock"},{"actor":"Chris Evans","character":"Hayden \"Harvard Hottie\""}]},{"title":"The Perfect Score","overview":"Six high school seniors decide to break into the Princeton Testing Center so they can steal the answers to their upcoming SAT tests and all get perfect scores.","release_date":"2004-01-30","characters":[{"actor":"Chris Evans","character":"Kyle"},{"actor":"Scarlett Johansson","character":"Francesca Curtis"}]},{"title":"The Avengers","overview":"When an unexpected enemy emerges and threatens global safety and security, Nick Fury, director of the international peacekeeping agency known as S.H.I.E.L.D., finds himself in need of a team to pull the world back from the brink of disaster. Spanning the globe, a daring recruitment effort begins!","release_date":"2012-04-25","characters":[{"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"},{"actor":"Chris Hemsworth","character":"Thor Odinson"},{"actor":"Scarlett Johansson","character":"Natasha Romanoff / Black Widow"},{"actor":"Jeremy Renner","character":"Clint Barton / Hawkeye"},{"actor":"Samuel L. Jackson","character":"Nick Fury"},{"actor":"Clark Gregg","character":"Phil Coulson"},{"actor":"Gwyneth Paltrow","character":"Virginia \"Pepper\" Potts"}]},{"title":"Iron Man 3","overview":"When Tony Stark's world is torn apart by a formidable terrorist called the Mandarin, he starts an odyssey of rebuilding and retribution.","release_date":"2013-04-18","characters":[{"actor":"Robert Downey Jr.","character":"Tony Stark / Iron Man"},{"actor":"Gwyneth Paltrow","character":"Virginia \"Pepper\" Potts"},{"actor":"Don Cheadle","character":"James \"Rhodey\" Rhodes / Iron Patriot"},{"actor":"Mark Ruffalo","character":"Bruce Banner (uncredited)"}]},{"title":"Marvel One-Shot: The Consultant","overview":"Agent Coulson informs Agent Sitwell that the World Security Council wishes Emil Blonsky to be released from prison to join the Avengers Initiative. As Nick Fury doesn't want to release Blonsky, the two agents decide to send a patsy to sabotage the meeting...","release_date":"2011-09-12","characters":[{"actor":"Clark Gregg","character":"Phil Coulson"},{"actor":"Robert Downey Jr.","character":"Tony Stark (archive footage)"}]},{"title":"Thor: The Dark World","overview":"Thor fights to restore order across the cosmos… but an ancient race led by the vengeful Malekith returns to plunge the universe back into darkness. Faced with an enemy that even Odin and Asgard cannot withstand, Thor must embark on his most perilous and personal journey yet, one that will reunite him with Jane Foster and force him to sacrifice everything to save us all.","release_date":"2013-10-29","characters":[{"actor":"Chris Hemsworth","character":"Thor Odinson"},{"actor":"Chris Evans","character":"Loki as Captain America (uncredited)"}]},{"title":"Avengers: Age of Ultron","overview":"When Tony Stark tries to jumpstart a dormant peacekeeping program, things go awry and Earth’s Mightiest Heroes are put to the ultimate test as the fate of the planet hangs in the balance. As the villainous Ultron emerges, it is up to The Avengers to stop him from enacting his terrible plans, and soon uneasy alliances and unexpected action pave the way for an epic and unique global adventure.","release_date":"2015-04-22","characters":[{"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"},{"actor":"Chris Hemsworth","character":"Thor Odinson"},{"actor":"Scarlett Johansson","character":"Natasha Romanoff / Black Widow"},{"actor":"Jeremy Renner","character":"Clint Barton / Hawkeye"},{"actor":"Samuel L. Jackson","character":"Nick Fury"},{"actor":"Don Cheadle","character":"James \"Rhodey\" Rhodes / War Machine"}]},{"title":"Captain America: The Winter Soldier","overview":"After the cataclysmic events in New York with The Avengers, Steve Rogers, aka Captain America is living quietly in Washington, D.C. and trying to adjust to the modern world. But when a S.H.I.E.L.D. colleague comes under attack, Steve becomes embroiled in a web of intrigue that threatens to put the world at risk. Joining forces with the Black Widow, Captain America struggles to expose the ever-widening conspiracy while fighting off professional assassins sent to silence him at every turn. When the full scope of the villainous plot is revealed, Captain America and the Black Widow enlist the help of a new ally, the Falcon. However, they soon find themselves up against an unexpected and formidable enemy—the Winter Soldier.","release_date":"2014-03-20","characters":[{"actor":"Chris Evans","character":"Steve Rogers / Captain America"},{"actor":"Samuel L. Jackson","character":"Nick Fury"},{"actor":"Scarlett Johansson","character":"Natasha Romanoff / Black Widow"}]},{"title":"Thanks for Sharing","overview":"A romantic comedy that brings together three disparate characters who are learning to face a challenging and often confusing world as they struggle together against a common demon—sex addiction.","release_date":"2013-09-19","characters":[{"actor":"Mark Ruffalo","character":"Adam"},{"actor":"Gwyneth Paltrow","character":"Phoebe"}]},{"title":"Chef","overview":"When Chef Carl Casper suddenly quits his job at a prominent Los Angeles restaurant after refusing to compromise his creative integrity for its controlling owner, he is left to figure out what's next. Finding himself in Miami, he teams up with his ex-wife, his friend and his son to launch a food truck. Taking to the road, Chef Carl goes back to his roots to reignite his passion for the kitchen -- and zest for life and love.","release_date":"2014-05-08","characters":[{"actor":"Scarlett Johansson","character":"Molly"},{"actor":"Robert Downey Jr.","character":"Marvin"}]},{"title":"Marvel Studios: Assembling a Universe","overview":"A look at the story behind Marvel Studios and the Marvel Cinematic Universe, featuring interviews and behind-the-scenes footage from all of the Marvel films, the Marvel One-Shots and \"Marvel's Agents of S.H.I.E.L.D.\"","release_date":"2014-03-18","characters":[{"actor":"Robert Downey Jr.","character":"Himself / Tony Stark / Iron Man"},{"actor":"Chris Hemsworth","character":"Himself / Thor"},{"actor":"Chris Evans","character":"Himself / Steve Rogers / Captain America"},{"actor":"Mark Ruffalo","character":"Himself / Bruce Banner / Hulk"},{"actor":"Gwyneth Paltrow","character":"Herself"},{"actor":"Clark Gregg","character":"Himself"},{"actor":"Samuel L. Jackson","character":"Himself"},{"actor":"Scarlett Johansson","character":"Herself"},{"actor":"Jeremy Renner","character":"Himself"}]},{"title":"Captain America: Civil War","overview":"Following the events of Age of Ultron, the collective governments of the world pass an act designed to regulate all superhuman activity. This polarizes opinion amongst the Avengers, causing two factions to side with Iron Man or Captain America, which causes an epic battle between former allies.","release_date":"2016-04-27","characters":[{"actor":"Chris Evans","character":"Steve Rogers / Captain America"},{"actor":"Robert Downey Jr.","character":"Tony Stark / Iron Man"},{"actor":"Scarlett Johansson","character":"Natasha Romanoff / Black Widow"},{"actor":"Don Cheadle","character":"James \"Rhodey\" Rhodes / War Machine"},{"actor":"Jeremy Renner","character":"Clint Barton / Hawkeye"}]},{"title":"Thor: Ragnarok","overview":"Thor is imprisoned on the other side of the universe and finds himself in a race against time to get back to Asgard to stop Ragnarok, the destruction of his home-world and the end of Asgardian civilization, at the hands of an all-powerful new threat, the ruthless Hela.","release_date":"2017-10-25","characters":[{"actor":"Chris Hemsworth","character":"Thor Odinson"},{"actor":"Mark Ruffalo","character":"Bruce Banner / Hulk"},{"actor":"Scarlett Johansson","character":"Natasha Romanoff / Black Widow (archive footage / uncredited)"}]},{"title":"Avengers: Endgame","overview":"After the devastating events of Avengers: Infinity War, the universe is in ruins due to the efforts of the Mad Titan, Thanos. With the help of remaining allies, the Avengers must assemble once more in order to undo Thanos' actions and restore order to the universe once and for all, no matter what consequences may be in store.","release_date":"2019-04-24","characters":[{"actor":"Robert Downey Jr.","character":"Tony Stark / Iron Man"},{"actor":"Chris Evans","character":"Steve Rogers / Captain America"},{"actor":"Mark Ruffalo","character":"Bruce Banner / Hulk"},{"actor":"Chris Hemsworth","character":"Thor Odinson"},{"actor":"Scarlett Johansson","character":"Natasha Romanoff / Black Widow"},{"actor":"Jeremy Renner","character":"Clint Barton / Hawkeye"},{"actor":"Don Cheadle","character":"James Rhodes / War Machine"},{"actor":"Gwyneth Paltrow","character":"Pepper Potts"},{"actor":"Samuel L. Jackson","character":"Nick Fury"}]},{"title":"Avengers: Infinity War","overview":"As the Avengers and their allies have continued to protect the world from threats too large for any one hero to handle, a new danger has emerged from the cosmic shadows: Thanos. A despot of intergalactic infamy, his goal is to collect all six Infinity Stones, artifacts of unimaginable power, and use them to inflict his twisted will on all of reality. Everything the Avengers have fought for has led up to this moment - the fate of Earth and existence itself has never been more uncertain.","release_date":"2018-04-25","characters":[{"actor":"Robert Downey Jr.","character":"Tony Stark / Iron Man"},{"actor":"Chris Hemsworth","character":"Thor Odinson"},{"actor":"Chris Evans","character":"Steve Rogers / Captain America"},{"actor":"Scarlett Johansson","character":"Natasha Romanoff / Black Widow"},{"actor":"Don Cheadle","character":"James \"Rhodey\" Rhodes / War Machine"},{"actor":"Gwyneth Paltrow","character":"Virginia \"Pepper\" Potts"},{"actor":"Samuel L. Jackson","character":"Nick Fury (uncredited)"},{"actor":"Mark Ruffalo","character":"Bruce Banner / The Hulk"}]},{"title":"Captain Marvel","overview":"The story follows Carol Danvers as she becomes one of the universe’s most powerful heroes when Earth is caught in the middle of a galactic war between two alien races. Set in the 1990s, Captain Marvel is an all-new adventure from a previously unseen period in the history of the Marvel Cinematic Universe.","release_date":"2019-03-06","characters":[{"actor":"Samuel L. Jackson","character":"Nick Fury"},{"actor":"Clark Gregg","character":"Agent Phil Coulson"},{"actor":"Chris Evans","character":"Steve Rogers / Captain America (uncredited)"},{"actor":"Scarlett Johansson","character":"Natasha Romanoff / Black Widow (uncredited)"},{"actor":"Don Cheadle","character":"James 'Rhodey' Rhodes / War Machine (uncredited)"},{"actor":"Mark Ruffalo","character":"Bruce Banner / The Hulk (uncredited)"}]},{"title":"Spider-Man: Homecoming","overview":"Following the events of Captain America: Civil War, Peter Parker, with the help of his mentor Tony Stark, tries to balance his life as an ordinary high school student in Queens, New York City, with fighting crime as his superhero alter ego Spider-Man as a new threat, the Vulture, emerges.","release_date":"2017-07-05","characters":[{"actor":"Robert Downey Jr.","character":"Tony Stark / Iron Man"},{"actor":"Gwyneth Paltrow","character":"Virginia \"Pepper\" Potts"},{"actor":"Chris Evans","character":"Steve Rogers / Captain America"}]},{"title":"Team Thor","overview":"Discover what Thor was up to during the events of Captain America: Civil War.","release_date":"2016-08-28","characters":[{"actor":"Chris Hemsworth","character":"Thor Odinson"},{"actor":"Mark Ruffalo","character":"Bruce Banner"}]},{"title":"Black Widow","overview":"Natasha Romanoff, also known as Black Widow, confronts the darker parts of her ledger when a dangerous conspiracy with ties to her past arises. Pursued by a force that will stop at nothing to bring her down, Natasha must deal with her history as a spy and the broken relationships left in her wake long before she became an Avenger.","release_date":"2020-10-28","characters":[{"actor":"Scarlett Johansson","character":"Natasha Romanoff / Black Widow"},{"actor":"Robert Downey Jr.","character":"Tony Stark / Iron Man"}]}]