Upscene JSON Library for Firebird
We’re proud to announce the Upscene JSON Library for Firebird, a server side plug in for Firebird 3 and up that provides full JSON support.
The two packages will allow you to query, update and create JSON documents. Click here to read more. Licenses can be purchased at our website.
The JSON package (or functions) allow for querying and modification of JSON values:
/* get individual JSON attributes as columns */
SELECT ID, NAME,
JSON.VALUE_STR(SETTINGS, '$.language') AS LANG,
JSON.VALUE_STR(SETTINGS, '$.contact.phone') AS PHONE,
JSON.VALUE_BOOL(SETTINGS, '$.newsletter') AS NEWSLETTER
FROM CUSTOMER
ORDER BY ID;
/* the customers who want the newsletter */
SELECT ID, NAME FROM CUSTOMER
WHERE JSON.VALUE_BOOL(SETTINGS, '$.newsletter');
UPDATE CUSTOMER SET SETTINGS = JSON.SET_VALUE(SETTINGS, '$.language', '"en"') WHERE ID = 3;
UPDATE CUSTOMER
SET SETTINGS = JSON.SET_VALUE(
JSON.INSERT_VALUE(SETTINGS, '$.contact', '{}'), -- add {} only where there is no contact
'$.contact.preferred', '"email"');
-- 1: unchanged ("preferred" was already "email")
-- 2: {"language": "de", ..., "contact": {"phone": "+49 221 987 654", "preferred": "email"}}
-- 3: {"language": "nl", "tags": [], "contact": {"preferred": "email"}}
The JSONDOC package give you the tools to build JSON documents from scratch:
CREATE PROCEDURE CUSTOMERS_JSON (ONLY_ACTIVE BOOLEAN DEFAULT FALSE)
RETURNS (DATA D_JSON)
AS
DECLARE list D_JSON_HANDLE;
DECLARE cust D_JSON_HANDLE;
DECLARE addr D_JSON_HANDLE;
DECLARE ID INTEGER;
DECLARE NAME VARCHAR(100);
DECLARE EMAIL VARCHAR(100);
DECLARE CITY VARCHAR(50);
DECLARE COUNTRY CHAR(2);
DECLARE ACTIVE BOOLEAN;
DECLARE CREDIT_LIMIT NUMERIC(12,2);
DECLARE SETTINGS D_JSON;
BEGIN
list = JSONDOC.NEW_ARRAY();
FOR SELECT ID, NAME, EMAIL, CITY, COUNTRY, ACTIVE, CREDIT_LIMIT, SETTINGS
FROM CUSTOMER
WHERE ACTIVE OR NOT :ONLY_ACTIVE
ORDER BY ID
INTO :ID, :NAME, :EMAIL, :CITY, :COUNTRY, :ACTIVE, :CREDIT_LIMIT, :SETTINGS
DO
BEGIN
/* the address as a small object of its own */
addr = JSONDOC.NEW_OBJECT();
addr = JSONDOC.ADD_STR(addr, 'city', CITY);
addr = JSONDOC.ADD_STR(addr, 'country', COUNTRY);
/* one object per customer; every call returns the handle, so the calls chain */
cust = JSONDOC.NEW_OBJECT();
cust = JSONDOC.ADD_INT(cust, 'id', ID);
cust = JSONDOC.ADD_STR(cust, 'name', NAME);
cust = JSONDOC.ADD_STR(cust, 'email', EMAIL); -- NULL gives "email": null
cust = JSONDOC.ADD_DOC(cust, 'address', addr); -- moves addr into cust
cust = JSONDOC.ADD_BOOL(cust, 'active', ACTIVE);
cust = JSONDOC.ADD_NUM(cust, 'creditLimit', CREDIT_LIMIT);
cust = JSONDOC.ADD_JSON(cust, 'settings', SETTINGS); -- the column is JSON already
list = JSONDOC.APPEND_DOC(list, cust); -- moves cust into list
END
DATA = JSONDOC.PRETTY(list); -- or JSONDOC.TEXT(list) for one line
JSONDOC.FREE(list);
SUSPEND;
END^
SET TERM ; ^
You can now SELECT from the procedure and it will return JSON data:
[
{
"id": 1,
"name": "De Vries Bouw",
"email": "info@devries.example",
"address": {
"city": "Delft",
"country": "NL"
},
"active": true,
"creditLimit": 2500,
"settings": {
"language": "nl",
"newsletter": true,
"tags": [
"vip",
"wholesale"
],
"contact": {
"phone": "+31 15 123 4567",
"preferred": "email"
}
}
},
{
"id": 2,
"name": "Müller GmbH",
"email": null,
"address": {
"city": "Köln",
"country": "DE"
},
"active": true,
"creditLimit": 10000,
"settings": {
"language": "de",
"newsletter": false,
"tags": [
"wholesale"
],
"contact": {
"phone": "+49 221 987 654",
"preferred": "phone"
}
}
},
{
"id": 3,
"name": "Leiden Fietsen",
"email": "shop@leidenfietsen.example",
"address": {
"city": "Leiden",
"country": "NL"
},
"active": false,
"creditLimit": null,
"settings": {
"language": "nl",
"tags": []
}
}
]
About Upscene Productions
Based in The Netherlands, Europe, this small but dedicated company has been providing database developers with useful tools for over 20 years. Slowly expanding the product portfolio and gaining recognition amongst InterBase and Firebird database developers, they now offer tools for a whole range of database systems, including Oracle and Microsoft SQL Server. Follow on Mastodon: https://mstdn.business/@upscene
