The cmdlet maps SQL queries into the corresponding Apache CouchDB queries. The cmdlet uses the Selector syntax to compute the desired results.

A detailed description of all the transformations is out of scope, but we will describe some of the common elements that are used.

SELECT Queries

The SELECT statement is mapped to the GET and POST methods. Below are example queries and the corresponding request payloads.

SELECT *
FROM sales

SELECT _id, value
FROM sales

{
  "fields": [
    "_id",
    "value"
  ],
  "skip": 0,
  "selector": {
    "_id": {
      "$exists": true
    }
  }
}

SELECT * 
FROM sales 
WHERE value=175033291697

{
  "selector": {
    "value": {
      "$eq": 175033291697
    }
  },
  "skip": 0
}

SELECT * 
FROM sales 
WHERE value = 175033291697 OR month='June'

{
  "selector": {
    "$or": [
      {
        "value": {
          "$eq": 175033291697
        }
      },
      {
        "month": {
          "$eq": "June"
        }
      }
    ]
  },
  "skip": 0
}

SELECT * 
FROM sales
WHERE name LIKE 'A%'

{
  "selector": {
    "name": {
      "$regex": "A%"
    }
  },
  "skip": 0
}

SELECT * FROM sales
WHERE month='June'
ORDER BY _id ASC

{
  "skip": 0,
  "sort": [
    {
      "_id": "asc"
    }
  ],
  "selector": {
    "month": {
      "$eq": "June"
    }
  }
}

SELECT *
FROM sales
WHERE month='June'
ORDER BY _id DESC

{
  "skip": 0,
  "sort": [
    {
      "_id": "desc"
    }
  ],
  "selector": {
    "month": {
      "$eq": "June"
    }
  }
}

INSERT Statements

The INSERT statement is mapped to the HTTP POST request, as shown in the following query and request payload.

SQL Query

INSERT INTO users (_id, age, status, [address.city], [address.postalcode]) 
VALUES ('bcd001', 45, 'A', 'Chapel Hill', 27517)

Apache CouchDB Query

{
  "address": {
    "city": "Chapel Hill",
    "postalcode": 27517
  },
  "_id": "bcd001",
  "age": 45,
  "status": "A"
}

UPDATE Statements

The UPDATE statement is mapped to the HTTP PUT method, as shown in the following query and request payload.

SQL Query

UPDATE users 
SET status = 'C', [address.postalcode] = 90210
WHERE _id = 'bcd001'

Apache CouchDB Query

{
  "_id": "bcd001",
  "_rev": "1-446f6c67e3a483feae8eaf112f18892c",
  "status": "C",
  "age": 45,
  "address": {
    "city": "Chapel Hill",
    "postalcode": 90210
  }
}

DELETE Statements

The DELETE statement is mapped to the DELETE method as shown below.

SQL Query

DELETE FROM users WHERE _id = 'bcd001'

Apache CouchDB Query

For this query, a DELETE request is sent to the users/{_id}?rev={_rev} endpoint.