Couchbase N1QL Samples.

0README.md

#Learn N1QL by sample

N1QL is the document query language used to query data in Couchbase Server (CB). N1QL will be an integrated part of CB 4, until then there is a preview version available.

##SQL for JSON Documents N1QL is the first language to combine the flexibility of JSON with power of SQL queries. With N1QL you can run SQL like queries against your CB data without the need to pre-build views. N1QL enables advanced ad-hoc document queries in a familiar query language.

##Learning With every new language there is a something new to learn, N1QL is no exception but the learning curve is modest as N1QL is highly inspired by SQL. The most notable difference is some added key words and the fact that N1QL is designed for working with JSON documents. This will get very visible when inserting data.

##Samples It’s different how we learn, but for me there is nothing that beats a good set of samples that showcase the options in the language and print out some meaningful data that I can reproduce on my local box.

The samples presented here are based on the “travel-sample” data that ships with Couchbase Server 4 Beta. Therefore if you install CB 4 Beta (or above), then please remember to install the “travel-sample” data bucket as well.

##Working progress I will continue to expand the samples when I find something missing or whenever someone points out a missing sample. Therefore I would like to invite you to suggest samples that you are missing or find incomplete.

##N1QL language reference The full N1QL language reference can be found here: http://docs.couchbase.com/4.0/n1ql/n1ql-language-reference/index.html

##Enjoy! I hope you find the samples use full and tweakable for use in your own project.

Happy learning.

N1QL_samples.sql

/*
* N1QL language reference
* The full N1QL language reference can be found here:
* ---------------------------------------------------------------------
* http://docs.couchbase.com/4.0/n1ql/n1ql-language-reference/index.html
* ---------------------------------------------------------------------
*/

/* create index on default bucket, required to enable N1QL queries on the bucket */
CREATE PRIMARY INDEX ON  `default`

/* delete index on default bucket */
DROP PRIMARY INDEX ON `default`

/* returns the total number of documents in the bucket */
SELECT COUNT(*) FROM `default`

SELECT COUNT(*) FROM `default` WHERE type='MyDoc'

SELECT COUNT(*) FROM `default` WHERE type LIKE '%My%'

SELECT COUNT(*) FROM `default` WHERE type LIKE '%My%c'

SELECT name, type AS DocumentType FROM `default` WHERE type LIKE '%MyDoc%'

SELECT name, type AS DocumentType FROM `default` WHERE type LIKE '%MyDoc%'AND name LIKE '%A%'

SELECT name, type AS DocumentType FROM `default` WHERE type LIKE '%MyDoc%'LIMIT 10

SELECT name, type AS DocumentType FROM `default` WHERE type LIKE '%MyDoc%'LIMIT 10 OFFSET 10

/*EXPLAIN -> prints the query plan for the query */
EXPLAIN SELECT name, type AS DocumentType FROM `default` WHERE type LIKE '%MyDoc%'LIMIT 10 OFFSET 10

/* TRAVEL SAMPLE DATA QUERIES */
/* 
   From Couchbase Server 4 BETA 1 you can create the travel-sample bucket from the settings page in the Admin Console. 
   The samples below are using the travel data sample and therefore you will need to create the travel sample data to 
   run the samples.
*/

SELECT COUNT(*) FROM `travel-sample`
-- result:
/*{
  "$1": 31620
}*/

SELECT * FROM `travel-sample` limit 1
-- result
/*
{
  "travel-sample": {
    "activity": "do",
    "address": "Shepherd's Bush Green, W12 8TT",
    "alt": null,
    "checkin": null,
    "checkout": null,
    "city": "London",
    "content": "Built in 1903 and at the centre of the entertainment scene ever
since, the Empire is one of London's premier live music venues, with an emphasis
 on contemporary rock and pop. | image=Shepherds Bush Empire, Shepherds Bush, W1
2 (4299318376).jpg",
    "country": "United Kingdom",
    "directions": "tube: Shepherd's Bush",
    "email": "[email protected]",
    "fax": null,
    "geo": {
      "lat": 51.5034,
      "lon": -0.2243
    },
    "hours": null,
    "id": 16144,
    "image": "https://en.wikivoyage.org/wiki/File:Shepherds Bush Empire, Shepher
ds Bush, W12 (4299318376).jpg",
    "image_direct_url": "https://upload.wikimedia.org/wikipedia/commons/a/ab/She
pherds_Bush_Empire%2C_Shepherds_Bush%2C_W12_%284299318376%29.jpg",
    "name": "02 Shepherd's Bush Empire",
    "phone": "+44 20 8354-3300",
    "price": null,
    "state": "England",
    "title": "London/Hammersmith and Fulham",
    "tollfree": null,
    "type": "landmark",
    "url": "http://www.shepherds-bush-empire.co.uk/"
  }
}
*/

SELECT COUNT(DISTINCT type) from `travel-sample`
--result:
/*
{
  "$1": 4
}
*/

SELECT DISTINCT type from `travel-sample`
--result:
/*
{
  "type": "airline"
}
{
  "type": "airport"
}
{
  "type": "route"
}
{
  "type": "landmark"
}
*/

SELECT COUNT(*) FROM `travel-sample` WHERE type='airline'
--result:
/*
{
  "$1": 187
}
*/

SELECT COUNT(*) FROM `travel-sample` WHERE type LIKE 'air%'
--result:
/*
{
  "$1": 2155
}
*/

SELECT COUNT(*) AS StartsWithAir FROM `travel-sample` WHERE type LIKE 'air%'
--result:
/*
{
  "StartsWithAir": 2155
}
*/
EXPLAIN SELECT COUNT(*) AS StartsWithAir FROM `travel-sample` WHERE type LIKE 'air%'
--result:
/*
{
  "#operator": "Sequence",
  "~children": [
    {
      "#operator": "PrimaryScan",
      "index": "#primary",
      "keyspace": "travel-sample",
      "namespace": "default",
      "using": "view"
    },
    {
      "#operator": "Parallel",
      "~child": {
        "#operator": "Sequence",
        "~children": [
          {
            "#operator": "Fetch",
            "keyspace": "travel-sample",
            "namespace": "default"
          },
          {
            "#operator": "Filter",
            "condition": "((`travel-sample`.`type`) like \"air%\")"
          },
          {
            "#operator": "InitialGroup",
            "aggregates": [
              "count(*)"
            ],
            "group_keys": []
          }
        ]
      }
    },
    {
      "#operator": "IntermediateGroup",
      "aggregates": [
        "count(*)"
      ],
      "group_keys": []
    },
    {
      "#operator": "FinalGroup",
      "aggregates": [
        "count(*)"
      ],
      "group_keys": []
    },
    {
      "#operator": "Parallel",
      "~child": {
        "#operator": "Sequence",
        "~children": [
          {
            "#operator": "InitialProject",
            "result_terms": [
              {
                "as": "StartsWithAir",
                "expr": "count(*)"
              }
            ]
          },
          {
            "#operator": "FinalProject"
          }
        ]
      }
    }
  ]
}
*/

SELECT * FROM `travel-sample` WHERE type LIKE 'airli%' limit 1
--reslut:
/*
{
  "travel-sample": {
    "callsign": "REDWOOD",
    "country": "United States",
    "iata": "VX",
    "icao": "VRD",
    "id": 5331,
    "name": "Virgin America",
    "type": "airline"
  }
}
*/

SELECT * FROM `travel-sample` WHERE type LIKE 'airport' limit 1
--result:
/*
{
  "travel-sample": {
    "airportname": "San Juan - Uganik Seaplane Base",
    "city": "San Juan",
    "country": "United States",
    "faa": "WSJ",
    "geo": {
      "alt": 0,
      "lat": 57.730278,
      "lon": -153.320556
    },
    "icao": null,
    "id": 7174,
    "type": "airport",
    "tz": "America/Anchorage"
  }
}
*/

SELECT COUNT(*) FROM `travel-sample` WHERE type LIKE 'airport' AND country='United States'
--result:
/*
{
  "$1": 1560
}
*/

SELECT DISTINCT(country) FROM `travel-sample` WHERE type LIKE 'airport'
--result:
/*
{
  "country": "United Kingdom"
}
{
  "country": "United States"
}
{
  "country": "France"
}
*/

SELECT DISTINCT(country) AS AirportCountry FROM `travel-sample` WHERE type LIKE 'airport'
--result:
/*
{
  "AirportCountry": "France"
}
{
  "AirportCountry": "United Kingdom"
}
{
  "AirportCountry": "United States"
}
*/

SELECT DISTINCT(name) AS Airline, id FROM `travel-sample` WHERE type = 'airline' AND country LIKE '%Fran%'
--result:
/*
{
  "Airline": "Tom\\'s & co airliners",
  "id": 13947
}
{
  "Airline": "Hex'Air",
  "id": 2757
}
{
  "Airline": "Air Austral",
  "id": 1191
}
{
  "Airline": "Air France",
  "id": 137
}
{
  "Airline": "Air Caledonie International",
  "id": 139
}
{
  "Airline": "Air Mediterranee",
  "id": 547
}
{
  "Airline": "XL Airways France",
  "id": 5479
}
{
  "Airline": "Aigle Azur",
  "id": 21
}
{
  "Airline": "Corse-Mediterranee",
  "id": 1909
}
{
  "Airline": "Brit Air",
  "id": 1523
}
{
  "Airline": "Air Tahiti Nui",
  "id": 225
}
{
  "Airline": "Air Saint Pierre",
  "id": 477
}
{
  "Airline": "Air Cara�¯bes",
  "id": 567
}
{
  "Airline": "Transavia France",
  "id": 8745
}
{
  "Airline": "Heli France",
  "id": 2704
}
{
  "Airline": "R�©gional",
  "id": 4299
}
{
  "Airline": "Twin Jet",
  "id": 4965
}
{
  "Airline": "VickJet",
  "id": 16837
}
{
  "Airline": "Corsairfly",
  "id": 1908
}
{
  "Airline": "Airlinair",
  "id": 1203
}
{
  "Airline": "Air Moorea",
  "id": 551
}
*/

SELECT DISTINCT(name) AS Airline, id FROM `travel-sample` WHERE type = 'airline' AND country LIKE '%Fran%' LIMIT 5 OFFSET 5
--result:
/*
{
  "Airline": "Air France",
  "id": 137
}
{
  "Airline": "Hex'Air",
  "id": 2757
}
{
  "Airline": "Air Cara�¯bes",
  "id": 567
}
{
  "Airline": "Air Moorea",
  "id": 551
}
{
  "Airline": "Twin Jet",
  "id": 4965
}
*/

SELECT COUNT(*) FROM `travel-sample` WHERE airline IS MISSING
--result:
/*
{
  "$1": 7544
}
*/

SELECT COUNT(*) FROM `travel-sample` WHERE icao IS NULL
--result:
/*
{
  "$1": 281
}
*/

SELECT sourceairport FROM `travel-sample` WHERE sourceairport IS NOT NULL LIMIT 2
--result:
/*
{
  "sourceairport": "ATL"
}
{
  "sourceairport": "GDL"
}
*/

SELECT s FROM `travel-sample` as r UNNEST r.schedule s limit 3
--result:
/*
{
  "s": {
    "day": 0,
    "flight": "AH099",
    "utc": "03:26:00"
  }
}
{
  "s": {
    "day": 1,
    "flight": "AH772",
    "utc": "23:02:00"
  }
}
{
  "s": {
    "day": 1,
    "flight": "AH165",
    "utc": "13:33:00"
  }
}
*/

Hi , Is it possible to have '$' in N1QL queries as parameter like

SELECT * from db WHERE $Type = 'Customer'

/*
{
"$Type" : "Customer" {
"Name":"XXX"
}
}
*/