Example SQL statements with JSON2BSON, BSON2JSON, and JSON_VAL functions
The following examples show how you can use SQL with the JSON2BSON, BSON2JSON, and JSON_VAL functions to work with JSON data.
Example: Creating tables to store JSON data
The following example creates tables to 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;
CREATE TABLE JSONPOTXT( DATA VARCHAR(5000)) CCSID UNICODE;Example: Creating an index on a table with JSON data
The following example creates an index on Customer.age column and Customer.name column in the JSONCUSTOMER table.CREATE INDEX IX2 ON JSONCUSTOMER (JSON_VAL(DATA, 'Customer.age', 'i:na'),
JSON_VAL(DATA, 'Customer.name', 's:20:na'));Example: Inserting JSON data into a table
The following example inserts purchase order JSON data into the JSONPO table, and customer information into the JSONCUSTOMER table.
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"
}
}'));The following example inserts a JSON document into a VARCHAR column. JSON2BSON is not needed for this operation.
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"
}
]
}
}
}');Examples: Displaying JSON data
The following example displays the entire JSON data as text from the JSONPO table.SELECT ID, SYSTOOLS.BSON2JSON(DATA)
FROM JSONPO; SELECT SYSTOOLS.BSON2JSON(SYSTOOLS.JSON2BSON((SELECT DATA FROM JSONPOTXT)))
FROM SYSIBM.SYSDUMMY1; SELECT SYSTOOLS.BSON2JSON(DATA)
FROM JSONPO
ORDER BY JSON_VAL(DATA, 'PO.customer.@cid', 'i') DESC;Examples: Retrieving JSON data
The following example retrieves the name of customers who are older than 30.SELECT JSON_VAL(DATA,'Customer.name', 's:20') "Name"
FROM JSONCUSTOMER
WHERE JSON_VAL(DATA,'Customer.age', 'i') > 30;SELECT SYSTOOLS.BSON2JSON(DATA)
FROM JSONPO
WHERE YEAR(JSON_VAL(DATA,'PO.@orderDate', 'd')) = 2014;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;Example: Updating JSON data in a table
The following example updates the Customer information.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; Example: Deleting JSON data in a table
The following example deleted JSON data in a table:DELETE FROM JSONCUSTOMER
WHERE JSON_VAL(DATA, 'Customer.@cid', 'i:na') = 777;