Insert


In Clusterpoint database you can think of a Document as object or instance of a class you operate by your application or service.

Let’s say, your product is an object you are operating in your application and it has name, associated image, description and price. Thus within a collection called product we create a document describing its properties. 
​With Clusterpoint it is easily done with a JS/SQL INSERT statement. It is possible to insert JSON and XML documents.
You can also insert binary data type, but note, that most of database quering features will not be available for such type of data. When you will retrieve such data, the format will remain unchanged, but if you execute SELECT *, then response will return base64 encoded data. Please, refer to REST example of "inserting binary data".

In Clusterpoint every document has to have a unique key. There are several methods how to specify a key:
1)  If not provided it will be automatically generated as unique identifier.
2)  Key can be specified in the INSERT statement with a special syntax.

INSERT without specifying unique key

If ID key  is not provided in INSERT or documents you import from file, it will be automatically generated as unique identifier.

INSERT JSON VALUE example:

INSERT INTO product JSON VALUE {
  "name": "Schwinn S29 Full Suspension Mountain Bike",
  "category": "Bicycles",
  "image_url": "schwinn_s29.jpeg",
  "description": "...",
  "color": ["black","red"],
  "order_price": 211.16,
  "price": 259.16,
  "packaging": {
    "height": 23,
    "width": 25,
    "depth": 12,
    "weight": 54
  },
  "availability": "In Stock"
}

Unlike ANSI-SQL Clusterpoint allows operating JSON and XML documents natively, however we retain SQL syntax for compatibility (this limits us to a single level document structure):

INSERT INTO product (name, category, image_url, description, color, price, availability) 
VALUES ("Super Bicycle",
        "Bike",
        "super_bicycle_s08.jpeg",
        "...",
        "black",
        399.00,
        "Limited")

Since JS/SQL is based on JavaScript another alternative to insert the document is instead of its value provide JavaScript code that sets fields in the document as local variables of execution context, like:

INSERT INTO product SET
  name = "Schwinn Mountain Bike",
  category = "Bike",
  image_url = "schwinn_MB.jpeg",
  description = "...",
  color = ["white","red"],
  order_price = 180.93,
  price = 199.16,
  packaging = {
    height: 23,
    width: 25,
    depth: 12,
    weight: 54
  },
  availability = "In Stock"

INSERT XML VALUE example:

INSERT INTO product XML VALUE
<document>
  <name>Schwinn S29 Full Suspension Mountain Bike</name>
  <category>Bicycles</category>
  <image_url>schwinn_s29.jpeg</image_url>
  <description>...</description>
  <color>black</color>
  <order_price>211.16</order_price>
  <price>259.16</price>
  <packaging>
    <height>23</height>
    <width>25</width>
    <depth>12</depth>
    <weight>54</weight>
  </packaging>
  <availability>In Stock</availability>
</document>

Note, that XML document should have opening and closing "root" tag, as in example above - <document>.

INSERT with defining unique key

1) Unique ID key can be defined outside of document structure (in square brackets):

INSERT JSON VALUE example:

INSERT INTO product["34940855"] JSON VALUE {
  "name": "Schwinn S32 Full Suspension Mountain Bike",
  "category": "Bike",
  "image_url": "schwinn_s32.jpeg",
  "description": "...",
  "color": ["black","red"],
  "order_price": 374.26,
  "price": 459.16,
  "packaging" : {
    "height" : 23,
    "width" : 25,
    "depth" : 12,
    "weight" : 54
  },
  "availability": "In Stock"
}

INSERT XML VALUE example:

INSERT INTO product["34940866"] XML VALUE 
<document>
  <name>Schwinn S29 Full Suspension Mountain Bike</name>
  <category>Bicycles</category>
  <image_url>schwinn_s29.jpeg</image_url>
  <description>...</description>
  <color>black</color>
  <order_price>211.16</order_price>
  <price>259.16</price>
  <packaging>
    <height>23</height>
    <width>25</width>
    <depth>12</depth>
    <weight>54</weight>
  </packaging>
  <availability>In Stock</availability>
</document>


2)
The field within a document may be used as a primary key. This has to be specified in the Data Model when collection is empty (before documents are inserted). Or primary key could be defined in data import configuration by choosing ID column/field. For example, we have defined ID field named "id_field" in Data Model.

INSERT JSON VALUE example:

INSERT INTO product JSON VALUE {
  "id_field": "unique1",
"name": "Schwinn S32 Full Suspension Mountain Bike", "category": "Bike", "image_url": "schwinn_s32.jpeg", "description": "...", "color": ["black","red"], "order_price": 374.26, "price": 459.16, "packaging" : { "height" : 23, "width" : 25, "depth" : 12, "weight" : 54 }, "availability": "In Stock" }

INSERT XML VALUE example:

INSERT INTO product XML VALUE 
<document>
<id_field>unique2</id_field>
  <name>Schwinn S29 Full Suspension Mountain Bike</name>
  <category>Bicycles</category>
  <image_url>schwinn_s29.jpeg</image_url>
  <description>...</description>
  <color>black</color>
  <order_price>211.16</order_price>
  <price>259.16</price>
  <packaging>
    <height>23</height>
    <width>25</width>
    <depth>12</depth>
    <weight>54</weight>
  </packaging>
  <availability>In Stock</availability>
</document>


Collection[key] syntax is consistent with rest of JS/SQL where you can access a specific element of the collection by key elsewhere in the code just like collection was an associative container.

Note: allowed ID length is 249 bytes. If you try to INSERT document with ID that is greater, then it is automatically cut up to 249 bytes and document is successfully inserted. Response shows appropriate message:"The ID field's length must not exceed 249 bytes. It will be trimmed!".