Using JSON in SQL

This tutorial demonstrates the use of JSON within FairCom SQL. It details the creation of a table featuring a JSON field, followed by the insertion and updating of records to include each type of JSON value. The tutorial also illustrates how to use SQL and the JSON DB API on the same dataset.

In this tutorial, you can use iSQL or Data Explorer to run the SQL statements.

Create the testjson table

Use the JSON DB API to create the testjson table.

{
  "api": "db",
  "action": "createTable",
  "params": {
    "tableName": "testjson",
    "fields": [
      {
        "name": "myjson",
        "type": "json",
        "length": 65500
      },
      {
        "name": "notes",
        "type": "varchar",
        "length": 250
      }
    ]
  },
  "authToken": ""
}

Important

Until FairCom SQL natively supports the JSON type, you must create and alter tables using the JSON DB or CTDB APIs when you want a table to include JSON fields.

SQL semi-equivalent of the testjson table

The myjson field created in the previous example has a JSON data type, but SQL sees the column as VARCHAR because, in FairCom V13.1 and earlier, SQL does not support the JSON field type.

The testjson table created by the JSON DB API appears to SQL as if it were the following table.

Do not run the following code as part of this tutorial.

CREATE TABLE testjson
(
  id       BIGINT IDENTITY (1,1) PRIMARY KEY NOT NULL 
, changeid BIGINT DEFAULT autochangeid NOT NULL 
, myjson   VARCHAR(65500) 
, notes    VARCHAR(250) 
)  
STORAGE_ATTRIBUTES 'hotAlter; huge; norowid_fld; noRecbyt_idx;'
;
COMMIT;

ALTER INDEX sys_001 ON testjson RENAME TO id_pk;
COMMIT;

Tip

To maximize compatibility across all APIs, FairCom recommends creating tables using the JSON DB "createTable" action instead of SQL, CTDB, or ISAM. If you must use SQL to create tables, use the approach shown in the previous example.

Note

Even though FairCom SQL cannot see the difference between a JSON or VARCHAR column, the difference matters to the JSON DB and CTDB APIs.

The JSON DB API assigns and returns JSON from a JSON field and a string from a VARCHAR field.
For example, if you use the JSON DB API to assign the JSON object {"a": 1} to a JSON field, JSON DB returns the JSON object {"a": 1}. If you use SQL to assign the string '{"a": 1}' to a JSON field, the JSON DB API returns it as a JSON object: {"a": 1}.
For example, if you use the JSON DB API to assign the JSON object {"a": 1} to a VARCHAR field, it returns an error because the value is not a string. If you use SQL to assign the string '{"a": 1}' to a VARCHAR field, the JSON DB API returns it as a JSON string: "{\"a\": 1}".

Use SQL to insert records with JSON values

SQL

FairCom SQL inserts JSON values as strings.

insert into testjson values('{"a": 1}',        'JSON object');
insert into testjson values('[ "1", 2, [3] ]', 'JSON array');
insert into testjson values('"mike''s string"','JSON string');
insert into testjson values('1234',            'JSON integer');
insert into testjson values('-5678.009',       'JSON number');
insert into testjson values('true',            'JSON true');
insert into testjson values('false',           'JSON false');
insert into testjson values('null',            'JSON null');
commit;

The following screenshot uses the SQL Scripts tab in FairCom's Data Explorer application to insert these records.

Use SQL to view the inserted records

SELECT TOP 20 SKIP 0 id, myjson, notes FROM testjson ORDER BY id;

The following screenshot uses the SQL Queries tab in FairCom's Data Explorer application to display these records.

Use JSON DB API to insert records

The following code inserts the same set of records that the previous SQL example inserted. Notice how JSON DB API always uses native JSON values when working with data.

{
  "api": "db",
  "action": "insertRecords",
  "params": {
    "tableName": "testjson",
    "dataFormat": "objects",
    "sourceData": [
      { "myjson": {},          "notes": "JSON object" },
      { "myjson": [],          "notes": "JSON array" },
      { "myjson": "my string", "notes": "JSON string" },
      { "myjson": 1234,        "notes": "JSON integer" },
      { "myjson": -5678.009,   "notes": "JSON number" },
      { "myjson": true,        "notes": "JSON true" },
      { "myjson": false,       "notes": "JSON false" },
      { "myjson": null,        "notes": "JSON null" }
    ]
  },
  "authToken": ""
}

The following screenshot uses the SQL Queries tab in FairCom's Data Explorer application to display these records.

To make it easier to verify that the values are identical, you can order by the notes field.

SELECT id, myjson, notes FROM testjson ORDER BY notes;

Use JSON DB API to view inserted records

Because JSON DB created the myjson field as a JSON type, JSON DB returns JSON from the field regardless of whether SQL or JSON inserted those values.

JSON Request

{
  "api": "db",
  "action": "getRecordsByTable",
  "params": {
    "tableName": "testjson"
  },
  "authToken": "",
  "debug": "none"
}

JSON Response

{
  "authToken": "",
  "result": {
    "dataFormat": "objects",
    "binaryFormat": "hex",
    "variantFormat": "json",
    "fields": [
      {
        "name": "id", "type": "bigint",
        "length": null, "scale": null, "defaultValue": null, "nullable": false,
        "primaryKey": 1, "autoValue": "incrementOnInsert"
      },
      {
        "name": "changeId", "type": "bigint",
        "length": null, "scale": null, "defaultValue": null, "nullable": true,
        "primaryKey": 0, "autoValue": "changeId"
      },
      {
        "name": "myjson", "type": "json",
        "length": 65500, "scale": null, "defaultValue": null, "nullable": true,
        "primaryKey": 0, "autoValue": "none"
      },
      {
        "name": "notes", "type": "varchar",
        "length": 250, "scale": null, "defaultValue": null, "nullable": true,
        "primaryKey": 0, "autoValue": "none"
      }
    ],
    "data": [
      { "id": 1,  "myjson": {},          "notes": "JSON object" },
      { "id": 2,  "myjson": [],          "notes": "JSON array" },
      { "id": 3,  "myjson": "my string", "notes": "JSON string" },
      { "id": 4,  "myjson": 1234,        "notes": "JSON integer" },
      { "id": 5,  "myjson": -5678.009,   "notes": "JSON number" },
      { "id": 6,  "myjson": true,        "notes": "JSON true" },
      { "id": 7,  "myjson": false,       "notes": "JSON false" },
      { "id": 8,  "myjson": null,        "notes": "JSON null" },
      { "id": 9,  "myjson": {},          "notes": "JSON object" },
      { "id": 10, "myjson": [],          "notes": "JSON array" },
      { "id": 11, "myjson": "my string", "notes": "JSON string" },
      { "id": 12, "myjson": 1234,        "notes": "JSON integer" },
      { "id": 13, "myjson": -5678.009,   "notes": "JSON number" },
      { "id": 14, "myjson": true,        "notes": "JSON true" },
      { "id": 15, "myjson": false,       "notes": "JSON false" },
      { "id": 16, "myjson": null,        "notes": "JSON null" }
    ],
    "primaryKeyFields": [
      "id"
    ],
    "changeIdField": "changeId",
    "moreRecords": false,
    "requestedRecordCount": 20,
    "returnedRecordCount": 16,
    "totalRecordCount": 16
  },
  "errorCode": 0,
  "errorMessage": ""
}

Note

The "changeId" field is omitted from the results to keep the focus on the myjson field.

Use SQL to update records with JSON values

SQL

The following SQL code updates the records 1 through 5 with new JSON values.

UPDATE testjson SET myjson = '{"a":1}'          WHERE id = 1;
UPDATE testjson SET myjson = '["1",2,[3]]'      WHERE id = 2;
UPDATE testjson SET myjson = '"mike''s string"' WHERE id = 3;
UPDATE testjson SET myjson = '1234'             WHERE id = 4;
UPDATE testjson SET myjson = '-5678.009'        WHERE id = 5;
COMMIT;

JSON

The following JSON DB code updates records 9 through 13 with new JSON values.

{
  "api": "db",
  "action": "updateRecords",
  "params": {
    "tableName": "testjson",
    "dataFormat": "objects",
    "ignoreChangeIdProtection": true,
    "sourceData": [
      { "id": 9,  "myjson": {"a": 1}         },
      { "id": 10, "myjson": ["1", 2, [3] ]   },
      { "id": 11, "myjson": "mike's string"  },
      { "id": 12, "myjson": 1234             },
      { "id": 13, "myjson": -5678.009        }
    ]
  },
  "authToken": ""
}

The following screenshot uses the SQL Queries tab in FairCom's Data Explorer application to display these records. You can verify that the values are identical.

Note

Notes:

A JSON string is always enclosed in double quotes, such as "my string". To put a JSON string in a SQL CHAR or VARCHAR field, you must include the double quotes as part of the string, as shown in the following SQL INSERT statement:
INSERT INTO testjson VALUES('"my string"', 'JSON string');
If you do not include the double quotes, the text in the character field is invalid JSON and SQL returns error -21186 The value is not a valid JSON document.
If you need a SQL string to contain a single quote ' character, repeat it. For example, to create the JSON string "mike's string" in SQL, use '"mike''s string"'. The following SQL INSERT statement inserts "mike's string".
UPDATE testjson SET myjson = '"mike''s string"' WHERE id = 3;
JSON does not enclose the symbols true and false in double quotes, thus, they must be inserted or updated in SQL without double quotes, as shown in the following SQL INSERT statement:
INSERT INTO testjson VALUES('true', 'JSON true');
JSON numbers are not enclosed in double quotes, thus, they must be inserted or updated in SQL without double quotes, as shown in the following SQL INSERT statement:
INSERT INTO testjson VALUES('-5678.009', 'JSON number');
FairCom SQL automatically converts a number to a string; thus, the following SQL INSERT statements work the same:
INSERT INTO testjson VALUES( -5678.009, 'JSON number');
INSERT INTO testjson VALUES('-5678.009', 'JSON number');
In SQL and JSON DB you must use the null symbol to insert a null value into a field. If you use "null" in JSON or 'null' in SQL, you will insert a string containing the letters n u l l.