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"}}
Read more
