iQ Interactive: Cool Things for Developers on Couchbase Capella iQ

The landscape of software development is ever-evolving with the advent of new technologies. As we venture into 2023, natural language processing (NLP) is rapidly emerging as a pivotal aspect of programming. Unlike previous generations of tools that primarily aimed at enhancing coding productivity and code quality, the new generation of Artificial Intelligence (GenAI) tools, like iQ, is set to revolutionize every facet of a developer's workflow. This encompasses a wide range of activities:

• Reading, writing, and rewriting specifications
• Designing, prototyping, and coding
• Reviewing, refactoring, and verifying software
• Going through the iterative cycle of deploying, debugging, and improving the software
• Create a draft schema and sample data for any use case
• Natural language queries.
• Generate sample queries on a given dataset
• Fix the syntax error for a query
• Don't stop here. Let your imagination fly.

Although the insights garnered from iQ are preliminary and should be treated as drafts, the spectrum of tasks one can accomplish with iQ is rich and varied.

iQ is seamlessly integrated into the Developer Query workbench, specifically within an advisory panel located on the right-hand side. This panel includes features such as a history and an index advisor, providing a robust environment for developers to navigate their tasks. By clicking on iQ, you are presented with sample prompts that showcase its capabilities and potential applications, as illustrated in the figure below.  

In this article, we’ll explore ten intriguing iQ functionalities you can leverage with Couchbase, a powerful NoSQL database. I've used a widely recognized travel-sample dataset (in the samples schema) alongside sales data by Kaggle, housed in the collection titled 'mysales' within the same scope. As we delve into the practical applications, a series of sample Q&A segments will unfold the prowess of iQ, illustrating how it can be a game-changer in handling database operations effortlessly.

• Discover the fields in a collection in depth
• Learn a new feature or a concept in the product
• Get suggestions for a task
• Write interesting queries for charts
• Simple data analysis

Before we dive in, a note on prompts. The prompt is not an expanded search keyword or a single-sentence question. It's the description of the work we want LLMs to do. The current generation of Large Language Models (LLMs) prefer prompts written in an expository style. But what exactly is expository writing? Simply put, expository writing aims to explain, describe, or inform readers about a specific topic in a clear and straightforward manner. It lays out facts, analysis, or explanations without veering into personal opinions or fictional elements. Common examples of expository writing include textbooks, news articles, and instructional guides. And yes, you guessed it right — I enlisted the help of ChatGPT to provide this crisp explanation!

PROMPT : Explain the fields in the collection mysales

JSON can be a complex nested structure with objects and arrays. You can ask for details about a specific object or an array. Here’s an example from the hotel collection.

PROMPT: explain the field reviews in depth within the hotel collection.

PROMPT: Teach me window functions in Couchbase

Now you realize you really want to know more about NTILE() function. You can go deeper with examples.

PROMPT: Teach me NTILE() function with examples

Now, it writes a mini tutorial for you with full examples... but you really want queries that you can run. 

PROMPT: Write sample queries with NTILE() on mysales.

PROMPT: I need to present my sales data to the company board. What are the sales reports they expect? Write SQL++ queries for each of those reports.

PROMPT:  Write queries that produce good data for charts I need to present.

It generates the queries, and with a few clicks, you have charts!

PROMPT: 4 friends went for a golf outing and scored the following: Bob: 71, 68, Sam: 67, 73, Adam: 81, 72, Steve: 68, 73. Create a collection called gold, insert this daa and write queries to generate standard golf leaderboard. The golf leaderboard includes scores for each day, ranking, difference between each golfer and the leader

Here's the output. There are some issues with it... Couchbase script doesn't recognize — as a comment. In the SELECT query, we need to alias all of the objects and use it in all the references. Still, it's quite a good start, aka draft.

-- Create the 'golf' collection if it doesn't exist
CREATE COLLECTION IF NOT EXISTS `samples`.`golf`;

-- Insert the data into the 'golf' collection
INSERT INTO
  `samples`.`golf` (KEY, VALUE)
VALUES
  ("Bob", [71, 68]),
  ("Sam", [67, 73]),
  ("Adam", [81, 72]),
  ("Steve", [68, 73]);

-- Query to generate the standard golf leaderboard
SELECT
  META().id AS Player,
  scores AS Scores,
  RANK() OVER (
    ORDER BY
      AVG(score) DESC
  ) AS Ranking,
  AVG(score) - FIRST_VALUE(AVG(score)) OVER (
    ORDER BY
      AVG(score) DESC
  ) AS DifferenceFromLeader
FROM
  `samples`.`golf` AS g
UNNEST
  g.scores AS score
GROUP BY
  META(g).id,
  scores
ORDER BY
  Ranking ASC;

PROMPT: Create a collection for a game retail online store with a detailed user profile. Generate sample data and insert them into this collection.

Here's the output:

CREATE COLLECTION game_retail_online_store;

INSERT INTO
  game_retail_online_store (KEY, VALUE)
VALUES
  (
    "user1",
    {
      "username": "john_doe",
      "email": "[email protected]",
      "age": 25,
      "gender": "male",
      "address": {
        "street": "123 Main St",
        "city": "New York",
        "state": "NY",
        "country": "USA",
        "zipcode": "10001"
      },
      "preferences": {
        "favorite_genre": "action",
        "platform": "PlayStation",
        "language": "English"
      }
    }
  );

INSERT INTO
  game_retail_online_store (KEY, VALUE)
VALUES
  (
    "user2",
    {
      "username": "jane_smith",
      "email": "[email protected]",
      "age": 30,
      "gender": "female",
      "address": {
        "street": "456 Elm St",
        "city": "Los Angeles",
        "state": "CA",
        "country": "USA",
        "zipcode": "90001"
      },
      "preferences": {
        "favorite_genre": "adventure",
        "platform": "Xbox",
        "language": "Spanish"
      }
    }
  );

PROMPT:  Write the top 10 queries you can write on this collection.

Sure! Here are the top 10 queries you can write on the `game_retail_online_store` collection:

/* Retrieve all user profiles:*/
SELECT
  *
FROM
  game_retail_online_store;

/*  Retrieve a specific user profile by username: */
SELECT
  *
FROM
  game_retail_online_store
WHERE
  username = "john_doe";

/* Retrieve user profiles based on age range: */
SELECT
  *
FROM
  game_retail_online_store
WHERE
  age BETWEEN 20 AND 30;

/* Retrieve user profiles based on gender: */
SELECT
  *
FROM
  game_retail_online_store
WHERE
  gender = "female";

/* Retrieve user profiles based on favorite genre: */
SELECT
  *
FROM
  game_retail_online_store
WHERE
  preferences.favorite_genre = "action";
  
/* Retrieve user profiles based on platform preference: */
SELECT
  *
FROM
  game_retail_online_store
WHERE
  preferences.platform = "PlayStation";
  
/*  Retrieve user profiles based on language preference: */
SELECT
  *
FROM
  game_retail_online_store
WHERE
  preferences.language = "English";

/* Retrieve user profiles from a specific city: */
SELECT
  *
FROM
  game_retail_online_store
WHERE
  address.city = "New York";

/* Retrieve user profiles from a specific state: */
SELECT
  *
FROM
  game_retail_online_store
WHERE
  address.state = "NY";

/* Retrieve user profiles from a specific country: */
SELECT
  *
FROM
  game_retail_online_store
WHERE
  address.country = "USA";

/* These queries can be customized further based on your specific requirements.*/