CREATE NICKNAME statement - Examples for the MongoDB data source

For each MongoDB server definition that you register, you must register a nickname for each collection that you want to access. Use these nicknames, instead of the names of the data source objects, when you query the MongoDB servers.

1. Find the JSON document schema. If schema is not available, then you can specify any document that belongs to the collection that you want to query. You can perform this step through any web browser. For example, if you specify a URL" http://hostname:28017/dbname/collection_name/?limit=1" as the input value, a document is returned, which you can consider as the schema.
2. Establish mapping between the column and JSON document field by using the nickname column option ‘JPATH’. The column 'JPATH' defines the field value path in JSON document. It is similar to XPATH in XML document. For example, in the following document that is returned by the URL "http://hostname:28017/test/restaurants/?limit=1",

   {
                     “address”:{
                        “building”:“8825”,
                        “coord”:“[-73.8803827, 40.7643124]”,
                        “street”:“Astoria Boulevard”,
                        “zipcode”:“11369” 
                     },
                        “borough”:“Queens”,
                        “cuisine”:“American”,
                        “grades”: [{
                           “date”:{ “$date”:“2014-11-15T00:00:00.000Z”},
                           “grade”:“Z”,
                           “score”:38
                         },
                       {
                           “date”:{“$date”:“2014-05-02T00:00:00.000Z”},
                           “grade”:“A”,
                           “score”:10
                         },
                        {
                           “date”:{“$date”:“2013-03-02T00:00:00.000Z”},
                           “grade”:“A”,
                           “score”:7
                         },
                        {
                           “date”:{“$date”:“2012-02-10T00:00:00.000Z”},
                           “grade”:“A”,
                           “score”:13
                      }],
                       “name”:“Brunos On The Boulevard”,
                       “restaurant_id”:“40356151”          
               } 

   to establish mapping between the federated server column "COL1" and field “borough” in JSON document, you can use JPATH with value ‘$.borough’.

   Following is an example of JSON path to federated server column:

   $.name  “Brunos On The Boulevard”
   $.borough “Queens”
   $.restaurant_id  “40356151”
   $.grades[0].grade “Z”
   $.grades[1].score  38
   $.grades[2].date 11/14/2014

3. Perform data type mapping between DB2® federated server and document store data source (MongoDB/CouchDB).

   Db2 data type or JSON data type	STRING	NUMBER	BOOLEAN	ARRAY	OBJECT
   CHAR	Y	Y	Y	Y	Y
   VARCHAR	Y	Y	Y	Y	Y
   SMALLINT	N	Y	N	N	N
   INTEGER	N	Y	N	N	N
   BIGINT	N	Y	N	N	N
   DECIMAL	Y	Y	N	N	N
   DOUBLE	Y	Y	N	N	N
   FLOAT	Y	Y	N	N	N
   DATE	Y	N	N	N	N
   TIME	Y	N	N	N	N
   TIMESTAMP	Y	N	N	N	N

   Note: A query with comparison conditions on ObjectId type in MongoDB can return a wrong result. You can troubleshoot this wrong result. For more information, see A query returns no results when including equal comparison on _id column in MongoDB wrapper.
4. Create mappings between BSON data type and federated server columns.
   Note: MongoDB adds extensions to the JSON format to preserve type information. There are two modes that MongoDB supports:

   • Strict mode- The representations of BSON types conform to the JSON RFC. Any JSON parser can parse these strict mode representations as key/value pairs; however, only the MongoDB internal JSON parser recognizes the type information that is conveyed by the format.
   • Mongo Shell mode- The MongoDB internal JSON parser and the mongo shell can parse this mode.

   Currently, Rest Service wrapper supports only a subset of the BSON data types that is listed in the following table:

   Db2 data type or JSON data type	$date	$numberLong	$numberDecimal
   CHAR	Y	Y	Y
   VARCHAR	Y	Y	Y
   SMALLINT	N	Y	Y
   INTEGER	N	Y	Y
   BIGINT	N	Y	Y
   DECIMAL	N	Y	Y
   DOUBLE	N	Y	Y
   FLOAT	N	Y	Y
   DATE	Y	N	N
   TIME	Y	N	N
   TIMESTAMP	Y	N	N

   To create mappings between BSON data type and federated server columns, it is not mandatory to set JPATH to bottom level. For example, in the above JSON document, if you want to create a mapping for field ‘date’, you can just set the JPATH to $.grades[0].date. $numberLong and $numberDecimal is same with $date.

   Note: If you create a mapping for data type $numberLong or $numberDecimal to DB2 number type, make sure that the DB2 column type is sufficient for number range of $numberLong and $numberDecimal.
5. Issue the CREATE NICKNAME statement from the command line.

   CREATE NICKNAME nickname
    FOR server_definition_name.remote_schema.remote_table;

   For example:

   
   CREATE NICKNAME restaurants (name VARCHAR(40) OPTIONS (JPATH '$.name'),
     id VARCHAR(10) OPTIONS (JPATH '$.restaurant_id'),
     borough VARCHAR(16) OPTIONS (JPATH '$.borough'),
     address VARCHAR(96) OPTIONS (JPATH '$.address'),
     cuisine VARCHAR(10) OPTIONS (JPATH '$.cuisine'),
     score_0 INT OPTIONS (JPATH '$.grades[0].score'),
     date_0 DATE OPTIONS (JPATH '$.grades[0].date'))
   FOR SERVER JSERV OPTIONS ( DOC 'restaurants');

   Nicknames can be up to 128 characters in length.

   Note: When you create the nickname, the column option JPATH is a mandatory option. If JPATH is missing, you receive an SQL error message.