1. SQL Reference Guide
  2. Data Row Management
  3. Inserting Rows into JSON Collection Tables

Inserting Rows into JSON Collection Tables

JSON collection tables provide the flexibility to declare primary key fields during the table creation. The value of primary key fields along with the other fields in the document must be supplied during the insertion of data.

When you insert data, each row is inserted as a single document containing any number of JSON fields. To insert data, you must either supply the values for the fields inside a JSON object ({}) or state them explicitly.

Use one of the following methods to insert the data into a JSON collection table:

  • Using explicitly declared field names: You must explicitly supply the primary key field followed by the top-level JSON field names in the INSERT statement. You include the corresponding values using the values clause. The first value must be the value associated with the primary key field. This must be followed by the values associated with the corresponding field names supplied for the JSON document.

Example 7-1 Insert data into the JSON collection table created for a shopping application through explicit declaration of field names

INSERT into storeAcct(contactPhone, firstName, lastName, address, cart) values("1817113382", "Adam", "Smith", {"street" : "Tex Ave", "number" : 401, "city" : "Houston", "state" : "TX",   "zip" : 95085}, [{"item" : "handbag", "quantity" : 1, "priceperunit" : 350},{"item" : "Lego", "quantity" : 1, "priceperunit" : 5500}])

Explanation: In the above example, you insert the shopper's data into the shopping application table by supplying the contactPhone as the primary key followed by other details of the shopper as JSON fields. The shopper's details are stored internally as JSON data. Notice that in JSON collection tables, you do not supply a column name for the document itself, only provide the JSON fields in the document.

You can add another row to the same table with additional fields. 

INSERT into  storeAcct(contactPhone, firstName, lastName, gender, address, notify, cart, wishlist) values("1917113999", "Sharon", "Willard", "F", {"street" : "Maine", "number" : 501, "city" : "San Jose", "state" : "San Francisco",   "zip" : 95095},"yes", [{"item" : "wallet", "quantity" : 2, "priceperunit" : 950},{"item" : "wall art", "quantity" : 1, "priceperunit" : 9500}], [{"item" : "Tshirt", "priceperunit" : 500},{"item" : "Jenga", "priceperunit" : 850}])

In the above statement, you insert the shopper's data with additional fields such as gender, notify, and wishlist as compared with the first inserted row. The wishlist field is a JSON array that includes the details of items wishlisted by the shopper.

  • Using positional values: You must supply the primary key field values followed by document fields as name/value pairs encapsulated in a single JSON object. The fields in the document must adhere to JSON data type format. Any non-conformance will lead to exceptions and display error messages.

Example 7-2 Insert data into the JSON collection table created for a shopping application by supplying positional values

INSERT into storeAcct values("1817113382", {"firstName" : "Adam", "lastName" : "Smith", "address" : {"street" : "Tex Ave", "number" : 401, "city" : "Houston", "state" : "TX",   "zip" : 95085}, "cart" : [{"item" : "handbag", "quantity" : 1, "priceperunit" : 350},{"item" : "Lego", "quantity" : 1, "priceperunit" : 5500}]})

Explanation: In this insert statement, you insert the same data from Example 7-1 above using the positional value of the elements. You use the values clause followed by the value for the primary key field and then a JSON object {} containing the rest of the name/value pairs for the document.

Additional examples:

Example 7-3 Insert data into the JSON collection table for a shopping application with an MR_COUNTER

INSERT into  storeAcctMR(contactPhone, firstName, lastName, address, cart, mycounter) values("1817113382", "Adam", "Smith", {"street" : "Tex Ave", "number" : 401, "city" : "Houston", "state" : "TX",   "zip" : 95085}, [{"item" : "handbag", "quantity" : 1, "priceperunit" : 350},{"item" : "Lego", "quantity" : 1, "priceperunit" : 5500}], 0)
Explanation: While inserting data into the JSON collection table with an MR_COUNTER, you must supply a value of 0 for the MR_COUNTER.

Note:

  • If you supply a non-zero value, the system will ignore the supplied value and insert the value 0.
  • You can't supply the keyword DEFAULT in the INSERT clause for MR_COUNTER value.
  • The system returns an error if the MR_COUNTER value is skipped or DEFAULT is supplied in the INSERT statement. For more details, see Inserting rows into a JSON column having MR_COUNTER data type. 
SELECT contactPhone, firstName, mycounter FROM storeAcctMR where contactPhone = "1817113382"
Output:
{
  "contactPhone" : "1817113382",
  "firstName" : "Adam",
  "mycounter" : 0
}

Example 7-4 Insert shopper's data with more than one primary key field in the JSON collection table 

INSERT into storeAcctComposite(contactPhone, firstName, lastName, address, orders) values("1417114488", "Doris", "Martin", {"Dropbox" : "Presidency College", "city" : "Kansas City", "state" : "Alabama",   "zip" : 95065}) RETURNING *

Explanation: The storeAcctComposite table includes two primary key fields, the contactPhone field and the id field. For table creation, see JSON collection composite keys. As the order IDs for the purchased items are autogenerated in the shopping application, the id field is declared as an IDENTITY column while creating the table. While inserting data, you must supply the contactPhone primary key value along with document fields. If you do not supply the id field value, the system autogenerates the IDENTITY field value. For more details on the IDENTITY column, see Inserting Rows with an IDENTITY Column.

Output:
{
  "contactPhone" : "1417114488",
  "id" : 1,
  "address" : {
    "Dropbox" : "Presidency College",
    "city" : "Kansas City",
    "state" : "Alabama",
    "zip" : 95065
  },
  "firstName" : "Doris",
  "lastName" : "Martin"
}

Using APIs to Insert Data into JSON Collection Tables

You can create a JSON collection table and insert data into it using supported language drivers (SDKs). You can define the primary key fields using an allowed subset of NoSQL data types. For more information, see Table 5-1.

The language SDKs handle the creation of JSON collection tables or the insertion of data in the same way as fixed schema tables. For examples on the creation of JSON collection tables using APIs and inserting data into the tables, see Using APIs to create tables in Creating a table section of the Developer's Guide.