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

(1 votes, average: 5.00 out of 5)

Leave a Reply