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.

SQL

1

-- Create the 'golf' collection if it doesn't exist

2

CREATE COLLECTION IF NOT EXISTS `samples`.`golf`;

3

​

4

-- Insert the data into the 'golf' collection

5

INSERT INTO

6

  `samples`.`golf` (KEY, VALUE)

7

VALUES

8

  ("Bob", [71, 68]),

9

  ("Sam", [67, 73]),

10

  ("Adam", [81, 72]),

11

  ("Steve", [68, 73]);

12

​

13

-- Query to generate the standard golf leaderboard

14

SELECT

15

  META().id AS Player,

16

  scores AS Scores,

17

  RANK() OVER (

18

    ORDER BY

19

      AVG(score) DESC

20

  ) AS Ranking,

21

  AVG(score) - FIRST_VALUE(AVG(score)) OVER (

22

    ORDER BY

23

      AVG(score) DESC

24

  ) AS DifferenceFromLeader

25

FROM

26

  `samples`.`golf` AS g

27

UNNEST

28

  g.scores AS score

29

GROUP BY

30

  META(g).id,

31

  scores

32

ORDER BY

33

  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:

SQL

1

CREATE COLLECTION game_retail_online_store;

2

​

3

INSERT INTO

4

  game_retail_online_store (KEY, VALUE)

5

VALUES

6

  (

7

    "user1",

8

    {

9

      "username": "john_doe",

10

      "email": "john.doe@example.com",

11

      "age": 25,

12

      "gender": "male",

13

      "address": {

14

        "street": "123 Main St",

15

        "city": "New York",

16

        "state": "NY",

17

        "country": "USA",

18

        "zipcode": "10001"

19

      },

20

      "preferences": {

21

        "favorite_genre": "action",

22

        "platform": "PlayStation",

23

        "language": "English"

24

      }

25

    }

26

  );

27

​

28

INSERT INTO

29

  game_retail_online_store (KEY, VALUE)

30

VALUES

31

  (

32

    "user2",

33

    {

34

      "username": "jane_smith",

35

      "email": "jane.smith@example.com",

36

      "age": 30,

37

      "gender": "female",

38

      "address": {

39

        "street": "456 Elm St",

40

        "city": "Los Angeles",

41

        "state": "CA",

42

        "country": "USA",

43

        "zipcode": "90001"

44

      },

45

      "preferences": {

46

        "favorite_genre": "adventure",

47

        "platform": "Xbox",

48

        "language": "Spanish"

49

      }

50

    }

51

  );

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

SQL

1

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

2

​

3

/* Retrieve all user profiles:*/

4

SELECT

5

  *

6

FROM

7

  game_retail_online_store;

8

​

9

/*  Retrieve a specific user profile by username: */

10

SELECT

11

  *

12

FROM

13

  game_retail_online_store

14

WHERE

15

  username = "john_doe";

16

​

17

/* Retrieve user profiles based on age range: */

18

SELECT

19

  *

20

FROM

21

  game_retail_online_store

22

WHERE

23

  age BETWEEN 20 AND 30;

24

​

25

/* Retrieve user profiles based on gender: */

26

SELECT

27

  *

28

FROM

29

  game_retail_online_store

30

WHERE

31

  gender = "female";

32

​

33

/* Retrieve user profiles based on favorite genre: */

34

SELECT

35

  *

36

FROM

37

  game_retail_online_store

38

WHERE

39

  preferences.favorite_genre = "action";

40

41

/* Retrieve user profiles based on platform preference: */

42

SELECT

43

  *

44

FROM

45

  game_retail_online_store

46

WHERE

47

  preferences.platform = "PlayStation";

48

49

/*  Retrieve user profiles based on language preference: */

50

SELECT

51

  *

52

FROM

53

  game_retail_online_store

54

WHERE

55

  preferences.language = "English";

56

​

57

/* Retrieve user profiles from a specific city: */

58

SELECT

59

  *

60

FROM

61

  game_retail_online_store

62

WHERE

63

  address.city = "New York";

64

​

65

/* Retrieve user profiles from a specific state: */

66

SELECT

67

  *

68

FROM

69

  game_retail_online_store

70

WHERE

71

  address.state = "NY";

72

​

73

/* Retrieve user profiles from a specific country: */

74

SELECT

75

  *

76

FROM

77

  game_retail_online_store

78

WHERE

79

  address.country = "USA";

80

​

81

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