示例 SQL 语句，使用 JSON2BSON、 BSON2JSON 和 JSON_VAL 函数

示例：创建表来存储JSON数据

以下示例创建了用于存储JSON数据的表。

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;   
                                                                                           
CREATE TABLE  JSONPOTXT( DATA VARCHAR(5000)) CCSID UNICODE;

示例：使用JSON数据创建表索引

以下示例在JSONCUSTOMER表的 Customer.age 列和 Customer.name 列上创建索引。

CREATE INDEX IX2 ON  JSONCUSTOMER (JSON_VAL(DATA, 'Customer.age', 'i:na'),
                                   JSON_VAL(DATA, 'Customer.name', 's:20:na'));

示例：将JSON数据插入到表中

以下示例将采购订单JSON数据插入JSONPO表中，将客户信息插入JSONCUSTOMER表中。

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  JSONPO VALUES (
  102,
SYSTOOLS.JSON2BSON(
 '{"PO":{"@id": 102,                                       
           "@orderDate": "2014-12-20",                       
           "customer": {"@cid": 888},                        
           "items": {                                        
              "item": [{"@partNum": "872-AA",         
                        "productName": "Lawnmower",      
                        "quantity": 1,                     
                        "USPrice": 749.99,   
                        "shipDate": "2014-12-21"      
                       },                                       
                       {"@partNum": "837-CM",
                        "productName": "Digital Camera", 
                        "quantity": 2,
                        "USPrice": 199.99,
                        "comment": "2014-12-22"   
                       }                              
                      ]                                    
                   }                                                  
        }
  }'));             

INSERT INTO  JSONCUSTOMER VALUES (
   101,
   SYSTOOLS.JSON2BSON(
     '{"Customer":{"@cid": 999,                                        
                   "name": "Michael",                       
                   "age": 31,                                                               
                   "telephone": "234-343-2343",                                             
                   "country": "USA"                  
                   }
      }'));     
    
INSERT INTO  JSONCUSTOMER VALUES (
   102,
   SYSTOOLS.JSON2BSON(
      '{"Customer":{"@cid": 888,                                        
                    "name": "George",                       
                    "age": 29,                      
                    "telephone": "133-144-9999",       
                    "country": "USA"                  
                   }
      }'));

以下示例将JSON文档插入VARCHAR列。 JSON2BSON 此操作无需

INSERT INTO  JSONPOTXT VALUES (
   '{"PO":{"@id": 103,                                       
           "@orderDate": "2014-06-20",                       
           "customer": {"@cid": 888},                        
           "items": {                                        
             "item": [ { "@partNum": "872-AA",                         
                         "productName": "Lawnmower",                   
                         "quantity": 1,                                
                         "USPrice": 749.99,                            
                         "shipDate": "2014-06-21"                      
                       },                                              
                       { "@partNum": "837-CM",                         
                         "productName": "Digital Camera",           
                         "quantity": 2,                                
                         "USPrice": 199.99,                            
                         "comment": "2014-06-22"                     
                       }                                                
                     ]                                               
                   }                                                  
          }
    }');

示例：显示JSON数据

以下示例显示JSONPO表中整个JSON数据作为文本。

SELECT ID, SYSTOOLS.BSON2JSON(DATA)       
FROM  JSONPO;   

下面的示例将BSON格式转换为JSON格式，并将整个JSON数据显示为文本。

SELECT SYSTOOLS.BSON2JSON(SYSTOOLS.JSON2BSON((SELECT DATA FROM  JSONPOTXT)))
FROM SYSIBM.SYSDUMMY1;   

以下示例按降序显示客户ID JSON数据。

SELECT SYSTOOLS.BSON2JSON(DATA)
FROM  JSONPO 
ORDER BY JSON_VAL(DATA, 'PO.customer.@cid', 'i') DESC;

示例：检索JSON数据

以下示例检索年龄超过30岁的客户姓名。

SELECT JSON_VAL(DATA,'Customer.name', 's:20') "Name"
FROM  JSONCUSTOMER
WHERE JSON_VAL(DATA,'Customer.age', 'i') > 30;

以下示例从JSONPO表中检索2014年的采购订单。

SELECT SYSTOOLS.BSON2JSON(DATA)
FROM  JSONPO
WHERE YEAR(JSON_VAL(DATA,'PO.@orderDate', 'd')) = 2014;

以下示例检索了年龄在30岁以上的顾客购买的第一件商品。

SELECT JSON_VAL(T2.DATA,'PO.items.item.0.productName', 's:10') AS "Item"
FROM  JSONCUSTOMER T1,  JSONPO T2
WHERE JSON_VAL(T1.DATA, 'Customer.@cid', 'i') = JSON_VAL(T2.DATA, 'PO.customer.@cid', 'i') AND     
      JSON_VAL(T1.DATA,'Customer.age', 'i') > 30;

示例：更新表格中的JSON数据

以下示例更新了客户信息。

UPDATE  JSONCUSTOMER
SET DATA = SYSTOOLS.JSON2BSON('{"Customer":{"@cid": 777,
                                                  "name": "George",
                                                  "age": 29,
                                                  "telephone": "566-898-1111",
                                                  "country": "USA"
                                                   }
                                     }')
WHERE JSON_VAL(DATA, 'Customer.@cid', 'i:na') = 888; 

示例：删除表格中的 JSON 数据

以下示例删除表格中的JSON数据：

DELETE FROM JSONCUSTOMER
WHERE JSON_VAL(DATA, 'Customer.@cid', 'i:na') = 777;