IBM Support

How to insert JSON data with an apostrophe or a single quotation mark

How To


Summary

This article shows how to use the JSON2BSON function to insert JSON data that contains an apostrophe or single quotation mark. The example creates tables to store JSON data, insert data into the tables, and returns data from the tables for verification.

Environment

 Db2 for z/OS

Steps

Before you begin: 
If a table for storing JSON data exists, you can skip the step that creates the tables. However, make sure that you know where the tables are being stored.  
Steps:
  1. Create a table to store JSON data:
    The following CREATE TABLE statements are examples of tables that can store JSON data:
      
    CREATE TABLE  JSONPO( ID INTEGER NOT NULL,                     
                      DATA BLOB(16M) INLINE LENGTH 25000,      
                      PRIMARY KEY(ID)) CCSID UNICODE;   
                                 
CREATE TABLE  JSONCUSTOMER
                    ( ID INTEGER NOT NULL,                     
                      DATA BLOB(16M) INLINE LENGTH 25000,      
                      PRIMARY KEY(ID)) CCSID UNICODE;
  2. Insert JSON data into a table:
    To use an apostrophe or a single quotation mark (') in a value, add another single quotation mark after the first one.
    Notice that the value for name in JSONCUSTOMER table is     "O''HARA". The name value in JSONCUSTOMER table is saved as O'Hara.
     
    INSERT INTO JSONPO VALUES (
 101,
 SYSTOOLS.JSON2BSON(
   '{"PO":{"@id": 101,                                       
           "@orderDate": "2014-11-18",                       
           "customer": {"@cid": 999},                        
           "items": {                                        
              "item": [{"@partNum": "872-AA",                         
                        "productName": "Lawnmower",                   
                        "quantity": 1,                                
                        "USPrice": 149.99,                            
                        "shipDate": "2014-11-20"                      
                       },                                              
                       {"@partNum": "945-ZG",                         
                        "productName": "Sapphire Bracelet",           
                        "quantity": 2,                                
                        "USPrice": 178.99,                            
                        "comment": "Not shipped"                      
                       }                                                
                      ]                                               
                    }                                                  
          }
    }')
);

INSERT INTO  JSONCUSTOMER VALUES (
   101,
   SYSTOOLS.JSON2BSON(
     '{"Customer":{"@cid": 999,                                        
                   "name": "O''Hara",                       
                   "age": 31,                                                               
                   "telephone": "234-343-2343",                                             
                   "country": "USA"                  
                   }
      }'));
  3. Verify the inserted JSON data:
    SELECT the columns with the JSON data to verify that the data was inserted correctly.
     
    SELECT ID, SYSTOOLS.BSON2JSON(DATA)       
FROM  JSONCUSTOMER;

Document Location

Worldwide

[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSEPEK","label":"Db2 for z\/OS"},"ARM Category":[{"code":"a8m0z000000072kAAA","label":"Programming Interface->JSON"}],"ARM Case Number":"","Platform":[{"code":"PF035","label":"z\/OS"}],"Version":"All Version(s)","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
16 July 2020

UID

ibm16243278

Page Feedback