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.

Requirements:

 

Create the testjson table

Create the testjson table using JSON

  1. Navigate to the Code tab.
  2. Select API Console from the Code menu.
  3. Paste this code into the API Console:
    1. {
        "api": "db",
        "action": "createTable",
        "params": {
          "tableName": "testjson",
          "fields": [
            {
              "name": "myjson",
              "type": "json",
              "length": 65500
            },
            {
              "name": "notes",
              "type": "varchar",
              "length": 250
            }
          ]
        },
        "authToken": ""
      }
      
  4. Click the Run button. 
  5. Observe the output.

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.

 

Create a comparable testjson table using SQL statements

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}".

 

Insert records with JSON values

Use SQL to insert records with JSON values

FairCom SQL inserts JSON values as strings.

  1. Navigate to the Data tab.
  2. Select SQL Commands from the data menu.
  3. Paste this code into the SQL Script Request editor:
    1. INSERT INTO testjson VALUES
        ('{"a": 1}',        'JSON object'),
        ('[ "1", 2, [3] ]', 'JSON array'),
        ('"mike''s string"','JSON string'),
        ('1234',            'JSON integer'),
        ('-5678.009',       'JSON number'),
        ('true',            'JSON true'),
        ('false',           'JSON false'),
        ('null',            'JSON null');
  4. Click the Run button. 
  5. Observe the results in the Execution Log.

 

Use the 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.

  1. Navigate to the Code tab.
  2. Select API Console from the Code menu.
  3. Paste this code into the API Console:
    1. {
        "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": "replaceWithAuthTokenFromCreateSession"
      }
      
  4. Click the Run button. 
  5. Observe the output.

 

View the inserted records

Use SQL to view the inserted records

  1. Navigate to the Data tab
  2. Select SQL Commands from the Data menu.
  3. Paste this code into the SQL Script Request editor:
    1. SELECT TOP 20 SKIP 0 id, myjson, notes FROM testjson ORDER BY id;
  4. Click the Run button.
  5. Observe the results in the Output.

 

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.

  1. Navigate to the Code tab.
  2. Select API Console from the Code menu.
  3. Paste this code into the API Console:
    1. {
        "api": "db",
        "action": "getRecordsByTable",
        "params": {
          "tableName": "testjson"
        },
        "authToken": "replaceWithAuthTokenFromCreateSession",
        "debug": "none"
      }
  4. Click the Run button.
  5. Click the “JSON” button:

     

  6. Observe the output in JSON:
    1. {
       "authToken": "replaceWithAuthTokenFromCreateSession",
       "result": {
         "dataFormat": "objects",
         "binaryFormat": "hex",
         "variantFormat": "json",
         "stringFormat": "json",
         "fixedLengthCharFormat": "sql",
         "includeBookmarks": false,
         "fields": [
           {
             "name": "id",
             "type": "bigint",
             "length": null,
             "scale": null,
             "nullable": false,
             "primaryKey": 1,
             "autoValue": "incrementOnInsert",
             "defaultValue": null
           },
           {
             "name": "changeId",
             "type": "bigint",
             "length": null,
             "scale": null,
             "nullable": true,
             "primaryKey": 0,
             "autoValue": "changeId",
             "defaultValue": null
           },
           {
             "name": "myjson",
             "type": "json",
             "length": 65500,
             "scale": null,
             "nullable": true,
             "primaryKey": 0,
             "autoValue": "none",
             "defaultValue": null
           },
           {
             "name": "notes",
             "type": "varchar",
             "length": 250,
             "scale": null,
             "nullable": true,
             "primaryKey": 0,
             "autoValue": "none",
             "defaultValue": null
           }
         ],
         "data": [
           {
             "id": "15",
             "changeId": "181217",
             "myjson": false,
             "notes": "JSON false"
           },
           {
             "id": "11",
             "changeId": "181217",
             "myjson": "my string",
             "notes": "JSON string"
           },
           {
             "id": "16",
             "changeId": "181217",
             "myjson": null,
             "notes": "JSON null"
           },
           {
             "id": "12",
             "changeId": "181217",
             "myjson": 1234,
             "notes": "JSON integer"
           },
           {
             "id": "13",
             "changeId": "181217",
             "myjson": -5678.009,
             "notes": "JSON number"
           },
           {
             "id": "9",
             "changeId": "181217",
             "myjson": {},
             "notes": "JSON object"
           },
           {
             "id": "14",
             "changeId": "181217",
             "myjson": true,
             "notes": "JSON true"
           },
           {
             "id": "10",
             "changeId": "181217",
             "myjson": [],
             "notes": "JSON array"
           },
           {
             "id": "17",
             "changeId": "181293",
             "myjson": {
               "a": 1
             },
             "notes": "JSON object"
           },
           {
             "id": "18",
             "changeId": "181293",
             "myjson": [
               "1",
               2,
               [
                 3
               ]
             ],
             "notes": "JSON array"
           },
           {
             "id": "19",
             "changeId": "181293",
             "myjson": "mike's string",
             "notes": "JSON string"
           },
           {
             "id": "20",
             "changeId": "181293",
             "myjson": 1234,
             "notes": "JSON integer"
           },
           {
             "id": "21",
             "changeId": "181293",
             "myjson": -5678.009,
             "notes": "JSON number"
           },
           {
             "id": "22",
             "changeId": "181293",
             "myjson": true,
             "notes": "JSON true"
           },
           {
             "id": "23",
             "changeId": "181293",
             "myjson": false,
             "notes": "JSON false"
           },
           {
             "id": "24",
             "changeId": "181293",
             "myjson": null,
             "notes": "JSON null"
           }
         ],
         "primaryKeyFields": [
           "id"
         ],
         "changeIdField": "changeId",
         "moreRecords": false,
         "requestedRecordCount": 20,
         "returnedRecordCount": 16,
         "totalRecordCount": 16,
         "fieldValidation": []
       },
       "requestId": "idNumber",
       "errorCode": 0,
       "errorMessage": ""
      }

 

Update records with JSON values

Use SQL to update records with JSON values

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

  1. Navigate to the Data tab
  2. Select SQL Commands from the Data menu.
  3. Paste this code into the SQL Script Request editor:
    1. UPDATE testjson
      SET myjson = CASE id
          WHEN 1 THEN '{"a":1}'
          WHEN 2 THEN '["1",2,[3]]'
          WHEN 3 THEN '"mike''s string"'
          WHEN 4 THEN '1234'
          WHEN 5 THEN '-5678.009'
      END
      WHERE id IN (1, 2, 3, 4, 5);
  4. Click the Run button.
  5. Observe the results in the Execution Log.

 

Use JSON to update records with JSON values

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

  1. Navigate to the Code tab.
  2. Select API Console from the Code menu.
  3. Paste this code into the API Console:
    1. {
        "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": "replaceWithAuthTokenFromCreateSession"
      }
  4. Click the Run button. 
  5. Observe the output.
  6. Verify that the values are identical by pasting the following code into the SQL Command section of the Data tab:
    1. SELECT TOP 20 SKIP 0 id, myjson, notes FROM testjson ORDER BY notes;

Note

  • 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.