[crayon lang=”default” decode=”true”]SELECT u.name, COUNT(o.id) AS total_orders
\nFROM `commerce`.sales.users AS u
\nJOIN `commerce`.sales.orders AS o ON u.id = o.user_id
\nWHERE o.status = “completed”
\nAND DATE_DIFF_STR(NOW_STR(), o.order_date, “day”) <= 30
\nGROUP BY u.name
\nORDER BY total_orders DESC
\nLIMIT 5;
\n[\/crayon]<\/p>\n\n\n\n
The query above provides valuable insights from your data that’s stored in Couchbase about your top five users who generated the most completed orders within the past 30 days. But what if you\u2019re not an advanced SQL++ developer and need the answers by 11 p.m. for a report? You then need to wait for a developer to write a SQL++ query and get you the answers.<\/span><\/p>\n\n\n\n Alternatively, consider a case where you need to do some ad hoc debugging to address questions like:<\/span><\/p>\n\n\n\n In this case, you not only need to search the\u00a0 order_delivered field, but also look at order_cancelled or investigate comments to figure out if it was misplaced, etc. So the query to be written isn\u2019t simple or straightforward.\u00a0<\/span><\/p>\n\n\n [crayon lang=”default” decode=”true”]SELECT CASE In such cases, it would help if you had a reliable assistant available 24×7 to get all these answers. The UDF described in this blog is such an assistant. It accepts your questions in the most natural way and returns results in JSON. Behind the scenes, it connects to a model of your choice, along with your API key, to convert your thoughts into SQL++ and then executes it. And all you need to invoke this assistant is to use the UDF.<\/span><\/p>\n\n\n [crayon lang=”default” decode=”true”]SELECT NL2SQL( 1. Set up the library.<\/b> Library:<\/span><\/p>\n\n\n [crayon lang=”default” decode=”true”]\/* keyspaces: an array of strings, each string represents a keyspaces “bucket.scope.collection” with proper escaping using grave-accent quote wherever required output: function inferencer(k) { return res[0]; function nl2sql(keyspaces, prompt, apikey, modelapi, model) {<\/p>\n collectionSchema = {} collectionSchemaStr = JSON.stringify(collectionSchema)<\/p>\n promptContent = `Information:nCollection’s schema: ${collectionSchemaStr}nnPrompt: “${prompt}”nnThe query context is set.nnBased on the above Information, write valid SQL++ and return only the statement and no explanation. For retrieval, use aliases. Use UNNEST from the FROM clause when appropriate. nnIf you’re sure the Prompt can’t be used to generate a query, first say “#ERR:” and then explain why not.`<\/p>\n data = {“messages”:[{“role”:”system”,”content”:”You are a Couchbase Capella expert. Your task is to create valid queries to retrieve or create data based on the provided Information.nnApproach this task step-by-step and take your time.”},{“role”:”user”,”content”:promptContent}], var dataStr = JSON.stringify(data) var completionsurl = modelapi<\/p>\n var q= `SELECT CURL(“${completionsurl}”, { var completionsq = N1QL(q);<\/p>\n var res = [] try { stmt = content.trim().substring(7, content.length-4)<\/p>\n isSelect = (stmt.substring(0,6).toLowerCase())===”select” var runq = N1QL(stmt);<\/p>\n var rrun = [] return { } 2. Upload the library.<\/b> [crayon lang=”default” decode=”true”]curl -X POST https:\/\/localhost:9499\/evaluator\/v1\/libraries\/usingailib 3. Create the UDF.<\/b> [crayon lang=”default” decode=”true”]CREATE OR REPLACE FUNCTION NL2SQL(keyspaces, prompt, apikey, modelapi, model) NL2SQL() now acts as your multilingual translator between human language and Couchbase\u2019s query engine. You simply give it some context and a natural language request, and it returns a response.<\/span><\/p>\n\n\n\n Under the hood, it uses your preferred model when invoking the UDF to understand your intent and generate a query that Couchbase can execute.<\/span><\/p>\n\n\n\n The advantage of using the chat completions API means you could simply plug in a model from other providers that are compliant with the same API spec. You can use your own private LLM or known ones from Open AI, Gemini, Claude, etc.<\/span><\/p>\n\n\n\n The invoked UDF requires the following information from you:<\/span><\/p>\n\n\n\n There are also several available functions in the library:<\/span><\/p>\n\n\n\n inferencer()<\/strong><\/p>\n\n\n\n Before generating a query, the UDF first tries to understand your data. The inferencer() helper function calls Couchbase\u2019s INFER statement to retrieve a collection\u2019s schema:<\/p>\n\n\n [crayon lang=”default” decode=”true”]function inferencer(k) { This schema is used to help the AI understand what kind of data lives inside each collection.<\/p>\n\n\n\n The main function: nl2sql()<\/strong><\/p>\n\n\n\n The reason for not executing non-select statements is that you don\u2019t want this UDF to insert, update, or delete documents in a collection without you verifying it. So the SQL++ statement lets you execute it after it\u2019s been verified.<\/span><\/p>\n\n\n\n Example use case:<\/p>\n\n\n [crayon lang=”default” decode=”true”]SELECT default:NL2SQL( [crayon lang=”default” decode=”true”]Result: The next example uses Gemini\u2019s Open AI-compatible API. You simply change the model provider\u2019s URL from the previous Open AI API to Gemini\u2019s API. Also, be sure to change the model parameter to a model it recognizes. Of course, you need to also update the api-key from Open AI\u2019s key to Gemini\u2019s key.<\/p>\n\n\n [crayon lang=”default” decode=”true”]SELECT NL2SQL( The following illustrates the result:<\/p>\n\n\n\n [crayon lang=”default” decode=”true”]SELECT CASE This blog provides a glimpse into how you can leverage AI to interact with your data in Couchbase. With this UDF, natural language querying becomes a reality – no SQL++ expertise required. It is model-agnostic and safe for production queries.<\/p>\n\n\n\n And this is just the beginning. In the future, we hope to extend it to:<\/p>\n\n\n\n \u2026 all running inside Couchbase workflows.<\/p>\n\n\n\n References<\/strong> The query above provides valuable insights from your data that’s stored in Couchbase about your top five users who generated the most completed orders within the past 30 days. But what if you\u2019re not an advanced SQL++ developer and need the answers by 11 p.m. for a report? You then need to wait for a […]<\/p>\n","protected":false},"author":84423,"featured_media":5057,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"inline_featured_image":false,"footnotes":""},"categories":[54],"tags":[],"ppma_author":[683],"class_list":["post-5060","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-couchbase-server"],"yoast_head":"\n\n
\n o.orderId,
\n o.orderDate,
\n o.order_cancelled,
\n o.order_delivered,
\n o.comments,<\/p>\n
\n WHEN o.order_cancelled = TRUE THEN “Order was cancelled”
\n WHEN ANY c IN o.comments SATISFIES LOWER(c) LIKE “%misplac%”
\n OR LOWER(c) LIKE “%lost%” THEN “Order may have been misplaced”
\n WHEN ANY c IN o.comments SATISFIES LOWER(c) LIKE “%deliver%” THEN “Delivered but field not updated”
\n ELSE “Reason unknown \u2014 investigate”
\n END AS reason
\nFROM `commerce`.`sales`.`orders` AS o
\nWHERE o.order_delivered IS MISSING OR o.order_delivered IS NULL;
\n[\/crayon]<\/p>\n\n\n\n
\n [\u201c`commerce`.`sales`.`orders`\u201d],
\n “Are there any documents where the order_delivered date is missing?and if so why?”,
\n “”,
\n “https:\/\/api.openai.com\/v1\/chat\/completions”,
\n “gpt-4o-2024-05-13”
\n) ;
\n[\/crayon]<\/p>\n\n\n\nHow It Works<\/b><\/h3>\n\n\n\n
\nYou first create a JavaScript library used by the UDF.<\/span><\/p>\n\n\n\n
\ninput:<\/p>\n
\nprompt: users natural language request
\napikey: your openai api key
\nmodel: string representing the model’s name, see https:\/\/platform.openai.com\/docs\/api-reference\/completions\/create#completions-create-model for more details<\/p>\n
\nchat-completions api response with the generated sql statement
\n*\/<\/p>\n
\n var infq = N1QL(“SELECT t.properties FROM(INFER “+k+ “) as t”) ;
\n var res=[]
\n for(const doc of infq) {
\n res.push(doc)
\n }<\/p>\n
\n}<\/p>\n
\n for(const k in keyspaces) {
\n c = inferencer(keyspaces[k])
\n collectionSchema[keyspaces[k]] = c
\n }<\/p>\n
\n “model”: model,
\n “temperature”:0,
\n “max_tokens”:1024,
\n “stream”:false}<\/p>\n
\n .replace(\/\\\/g, “\\\\”) \/\/ escape backslashes
\n .replace(\/”\/g, ‘\\”‘); \/\/ escape quotes<\/p>\n
\n “request”: “POST”,
\n “header”: [“Authorization: Bearer ${apikey}”, “Content-type: application\/json”],
\n “data”: “${dataStr}”
\n }) AS result;`<\/p>\n
\n for(const doc of completionsq) {
\n res.push(doc);
\n }<\/p>\n
\n content = res[0].result.choices[0].message.content
\n } catch(e) {
\n return res;
\n } <\/p>\n
\n if(isSelect === false){
\n return {
\n “generated_statement”: stmt
\n }
\n }<\/p>\n
\n for(const doc of runq) {
\n rrun.push(doc)
\n }<\/p>\n
\n “generated_statement”: stmt,
\n “results”: rrun
\n }<\/p>\n
\n[\/crayon]<\/p>\n\n\n\n
\nRun the curl command after copying the provided library code into a file, i.e., usingailib.js. <\/span><\/p>\n\n\n
\n–data-binary @usingailib.js -u Administrator:password[\/crayon]<\/p>\n\n\n\n
\nUse the create function statement below to create the UDF once you have created the library:<\/span><\/p>\n\n\n
\nLANGUAGE JAVASCRIPT AS “nl2sql” AT “usingailib”;[\/crayon]<\/p>\n\n\n\nHow the UDF Thinks<\/h3>\n\n\n\n
\n
\nExample: “Show me all users who made a purchase in the last 24 hours.”<\/li>\n\n\n
\ne.g., “gpt-4o-2024-05-13”<\/li>\n\n<\/ol>\n\n\n\n
\n var infq = N1QL(“SELECT t.properties FROM (INFER ” + k + “) AS t”);
\n var res = [];
\n for (const doc of infq) {
\n res.push(doc);
\n }
\n return res[0];
\n}
\n[\/crayon]<\/p>\n\n\n\n\n
\n [“`travel-sample`.inventory.hotel”],
\n “Give me hotels in San Francisco that have free parking and free breakfast and a rating of more than 3”, “”,
\n “https:\/\/api.openai.com\/v1\/chat\/completions”,
\n “gpt-4o-2024-05-13”
\n);
\n[\/crayon]<\/p>\n\n\n
\n[{
\n “$1”: {
\n “generated_statement”: “SELECT h.name, h.address, h.city, h.state, h.country, h.free_parking, h.free_breakfast, r.ratings.OverallnFROM `travel-sample`.inventory.hotel AS hnUNNEST h.reviews AS rnWHERE h.city = “San Francisco”n AND h.free_parking = truen AND h.free_breakfast = truen AND r.ratings.Overall > 3;”,
\n “results”: [{
\n “Overall”: 4,
\n “address”: “520 Church St”,
\n “city”: “San Francisco”,
\n “country”: “United States”,
\n “free_breakfast”: true,
\n “free_parking”: true,
\n “name”: “Parker House”,
\n “state”: “California”
\n },
\n {
\n “Overall”: 4,
\n “address”: “520 Church St”,
\n “city”: “San Francisco”,
\n “country”: “United States”,
\n “free_breakfast”: true,
\n “free_parking”: true,
\n “name”: “Parker House”,
\n “state”: “California”
\n },
\n {
\n “Overall”: 5,
\n “address”: “520 Church St”,
\n “city”: “San Francisco”,
\n “country”: “United States”,
\n “free_breakfast”: true,
\n “free_parking”: true,
\n “name”: “Parker House”,
\n “state”: “California”
\n },
\n {
\n “Overall”: 4,
\n “address”: “520 Church St”,
\n “city”: “San Francisco”,
\n “country”: “United States”,
\n “free_breakfast”: true,
\n “free_parking”: true,
\n “name”: “Parker House”,
\n “state”: “California”
\n },
\n {
\n “Overall”: 5,
\n “address”: “465 Grant Ave”,
\n “city”: “San Francisco”,
\n “country”: “United States”,
\n “free_breakfast”: true,
\n “free_parking”: true,
\n “name”: “Grant Plaza Hotel”,
\n “state”: “California”
\n },
\n {
\n “Overall”: 5,
\n “address”: “465 Grant Ave”,
\n “city”: “San Francisco”,
\n “country”: “United States”,
\n “free_breakfast”: true,
\n “free_parking”: true,
\n “name”: “Grant Plaza Hotel”,
\n “state”: “California”
\n },
\n…
\n[\/crayon]<\/p>\n\n\n\nExperimenting with models from other providers<\/h3>\n\n\n\n
\n [“`travel-sample`.inventory.hotel”],
\n “Show me hotels in France”,
\n “”,
\n “https:\/\/generativelanguage.googleapis.com\/v1beta\/openai\/chat\/completions”,
\n “gemini-2.0-flash”
\n)as p;
\n[\/crayon]<\/p>\n\n\n\n
\n o.orderId,
\n o.orderDate,
\n o.order_cancelled,
\n o.order_delivered,
\n o.comments,<\/p>\n
\n WHEN o.order_cancelled = TRUE THEN “Order was cancelled”
\n WHEN ANY c IN o.comments SATISFIES LOWER(c) LIKE “%misplac%”
\n OR LOWER(c) LIKE “%lost%” THEN “Order may have been misplaced”
\n WHEN ANY c IN o.comments SATISFIES LOWER(c) LIKE “%deliver%” THEN “Delivered but field not updated”
\n ELSE “Reason unknown \u2014 investigate”
\n END AS reason
\nFROM `commerce`.`sales`.`orders` AS o
\nWHERE o.order_delivered IS MISSING OR o.order_delivered IS NULL;
\n[\/crayon]<\/p>\n0<\/p>\n\n\n\nConclusion<\/h3>\n\n\n\n
\n
\nCapella IQ: https:\/\/docs.couchbase.com\/cloud\/get-started\/capella-iq\/get-started-with-iq.html<\/a>
\nChat completions APIs:
\nhttps:\/\/platform.openai.com\/docs\/api-reference\/chat<\/a>
\nhttps:\/\/ai.google.dev\/gemini-api\/docs\/openai#rest<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"