Supported JSON formats

Plain JSON

A plain JSON follows the industry standard JSON format (as specified in https://www.json.org) and has one of the following structures depending on the actual export/import scenario.

A plain JSON can only contain elements of the following 4 data types: integer, string, boolean, and null.

One-level structure

A one-level plain JSON string must have only one level (cannot be two or more) and must be an object (cannot be an array).

This format is supported by the following methods (For all formats supported by a particular method, see Applicable methods):

Object

Method

DataWindow ImportRowFromJSON, ExportRowAsJson
RESTClient RetrieveOne

Here is an example of a one-level plain JSON string:

{ "column1":1, "column2":"name", "column3":true, "column4":null... }

Two-level structure

A two-level plain JSON must have its top-level as an array and the second-level as object(s) which represent a row of data.

[ SIMPLE-ROW1, SIMPLE-ROW2, SIMPLE-ROW3, SIMPLE-ROW4, SIMPLE-ROW5... ]

This format is supported by the following methods (For all formats supported by a particular method, see Applicable methods):

Object

Method

DataWindow ImportJson, ImportJsonByKey, ExportJson
JSONPackage GetValueToDataWindow, SetValueByDataWindow
RESTClient Retrieve, RetrieveOne, Submit

Here is an example of a two-level plain JSON string:

[{"emp_id":1,"emp_fname":"Fran","emp_lname":"Whitney", &
     "street":"9 East Washington Street","city":"Cornwall"}, &
 {"emp_id":2,"emp_fname":"Matthew","emp_lname":"Cobb", &
     "street":"7 Pleasant Street","city":"Grimsby"}, &
 {"emp_id":3,"emp_fname":"Philip","emp_lname":"Chin", &
     "street":"539 Pond Street","city":"Oakville"}, &
 {"emp_id":4,"emp_fname":"Julie","emp_lname":"Jordan", &
     "street":"1244 Great Plain Avenue","city":"Woodbridge"}, &
 {"emp_id":5,"emp_fname":"Robert","emp_lname":"Breault", &
     "street":"358 Cherry Street","city":"Milton"}, &
 {"emp_id":6,"emp_fname":"Melissa","emp_lname":"Espinoza", &
     "street":"1121 Apple Tree Way","city":"Iroquois Falls"}, &
 {"emp_id":7,"emp_fname":"Jeannette","emp_lname":"Bertrand", &
     "street":"2090A Concord Street","city":"Waterloo"}, &
 {"emp_id":8,"emp_fname":"Marc","emp_lname":"Dill", &
     "street":"897 Hancock Street","city":"Milton"}, &
 {"emp_id":9,"emp_fname":"Jane","emp_lname":"Francis", &
     "street":"127 Hawthorne Drive","city":"Scarborough"}, &
 {"emp_id":10,"emp_fname":"Natasha","emp_lname":"Shishov", &
     "street":"151 Milk Street","city":"Grimsby"}, &
 {"emp_id":11,"emp_fname":"Kurt","emp_lname":"Driscoll", &
     "street":"1546 School Street","city":"Grimsby"}, &
 {"emp_id":12,"emp_fname":"Rodrigo","emp_lname":"Guevara", &
     "street":"72 East Main Street","city":"Fort Henry"}]

DataWindow JSON

A DataWindow JSON is an object that contains elements representing the various aspects of a DataWindow.

This format is supported by the following methods (For all formats supported by a particular method, see Applicable methods):

Object

Method

DataWindow ImportJson, ImportJsonByKey, ExportJson
JSONPackage GetValueToDataWindow, SetValueByDataWindow
RESTClient Submit

The structure of DataWindow JSON is as follows:

{
"identity": "70c86603-983b-4bd9-adbc-259436e43cbd",
"version":1.0,
"platform":"PowerBuilder",
"mapping-method": 0,
"dataobject":{"name":"d_example",
    "meta-columns": [COLUMN-META1, COLUMN-META2…],
    "primary-rows": [DW-STANDARD-ROW1, DW-STANDARD-ROW2…],
    "filter-rows": [DW-STANDARD-ROW1, DW-STANDARD-ROW2…],
    "delete-rows": [DW-STANDARD-ROW1, DW-STANDARD-ROW2…],
    "dwchilds":{"department_id": [SIMPLE-ROW1,SIMPLE-ROW2…],
                "category_id": [SIMPLE-ROW1,SIMPLE-ROW2…]
                     …
                    }
}
}

Elements

Description

identity

A string identifying the format. Should keep unchanged.

version

An integer specifying the format version. Currently it is 1.0.

platform

A string specifying the platform where JSON string is generated. Values are: PowerBuilder, C#.

mapping-method

An integer specifying the method for mapping columns. Values are:

  • 0 -- Use the index of JSON item to map with the DataWindow column.

  • 1 -- Use the index of meta-columns to map with the DataWindow column.

  • 2 -- Use the key of JSON item to map with the DataWindow column.

Note: ImportJson function supports only 0 and 1, and ImportJsonByKey function and GetValueToDataWindow function ignore this value.

dataobject

An object indicating it is a DataWindow.

name

A string specifying the name of the DataWindow object (DataObject).

meta-columns (optional)

An array specifying the information of the DataWindow columns (excluding the computed columns).

For elements about the column meta information, see COLUMN-META below.

primary-rows (optional)

An array specifying the data row in the DataWindow primary buffer.

For elements about the DataWindow row, see DW-STANDARD-ROW below.

filter-rows (optional)

An array specifying the data row in the DataWindow filter buffer.

For elements about the DataWindow row, see DW-STANDARD-ROW below.

delete-rows (optional)

An array specifying the data row in the DataWindow delete buffer.

For elements about the DataWindow row, see DW-STANDARD-ROW below.

dwchilds (optional)

An object specifying the data in the DataWindowChild. The column name of the DataWindowChild is the key of dwchilds. The data row is SIMPLE-ROW. see SIMPLE-ROW below for more information.


COLUMN-META

COLUMN-META is an object that contains elements representing the various aspects of the DataWindow column (but not the computed column).

{
"name": "department_id",
"index": 1,
"datatype": "long", 
"nullable": 0
}

name -- (required) a string specifying the name of the column.

index -- (required) an integer specifying the sequence order of the column. This value will be used to map with the DataWindow column when the mapping-method value is set to 1.

datatype -- (required) a string specifying the type of the column. This value is not used by import.

nullable -- (required) an integer specifying whether to allow a null value. 0 - a null value is not allowed, 1 - a null value is allowed.

DW-STANDARD-ROW

DW-STANDARD-ROW is an object that contains elements representing the detailed information of the DataWindow row.

{
 "row-status": 0,
 "columns":{"column1": [CURRENT-VALUE, COLUMN-STATUS, ORIGINAL-VALUE],
           "column2": [CURRENT-VALUE, COLUMN-STATUS, ORIGINAL-VALUE],
            ...
          }
}

row-status -- (required) an integer specifying the status of the DataWindow row. 0 - NotModified!, 1 - DataModified!, 2 - New!, 3 - NewModified!.

columns -- (required) an object specifying the DataWindow column information including the current value, the column status, and the original value.

  • CURRENT-VALUE: (required) The current value of the column, in the following data type: integer, string, boolean, or null.

  • COLUMN-STATUS: (optional) An integer specifying the column status. 0 - (default) NotModified!, 1 - DataModified!.

  • ORIGINAL-VALUE: (optional) The original value of the column, in the following data type: integer, string, boolean, or null. The default type is null.

SIMPLE-ROW

SIMPLE-ROW is an object that contains elements representing the simple information of the DataWindow row. The data must be in the following data type: integer, string, boolean, or null.

{ "column1":1, "column2":"name", "column3":true, "column4":null... }

Example

Here is an example of a DataWindow JSON string:

{
  "identity": "70c86603-983b-4bd9-adbc-259436e43cbd",
  "version": 1,
  "platform": "PowerBuilder",
  "mapping-method": 0,
  "dataobject": {
    "name": "d_employee",
    "meta-columns": [
      {
        "name": "emp_id",
        "index": 0,
        "datatype": "long",
        "nullable": 1
      },
      {
        "name": "manager_id",
        "index": 1,
        "datatype": "long",
        "nullable": 1
      },
      {
        "name": "emp_fname",
        "index": 2,
        "datatype": "string",
        "nullable": 1
      },
      {
        "name": "emp_lname",
        "index": 3,
        "datatype": "string",
        "nullable": 1
      },
      {
        "name": "dept_id",
        "index": 4,
        "datatype": "long",
        "nullable": 1
      },
      {
        "name": "street",
        "index": 5,
        "datatype": "string",
        "nullable": 1
      },
      {
        "name": "city",
        "index": 6,
        "datatype": "string",
        "nullable": 1
      },
      {
        "name": "state",
        "index": 7,
        "datatype": "string",
        "nullable": 1
      },
      {
        "name": "zip_code",
        "index": 8,
        "datatype": "string",
        "nullable": 1
      },
      {
        "name": "phone",
        "index": 9,
        "datatype": "string",
        "nullable": 1
      },
      {
        "name": "status",
        "index": 10,
        "datatype": "string",
        "nullable": 0
      },
      {
        "name": "ss_number",
        "index": 11,
        "datatype": "string",
        "nullable": 1
      },
      {
        "name": "salary",
        "index": 12,
        "datatype": "decimal",
        "nullable": 1
      },
      {
        "name": "start_date",
        "index": 13,
        "datatype": "date",
        "nullable": 1
      },
      {
        "name": "termination_date",
        "index": 14,
        "datatype": "date",
        "nullable": 1
      },
      {
        "name": "birth_date",
        "index": 15,
        "datatype": "date",
        "nullable": 1
      },
      {
        "name": "bene_health_ins",
        "index": 16,
        "datatype": "string",
        "nullable": 0
      },
      {
        "name": "bene_life_ins",
        "index": 17,
        "datatype": "string",
        "nullable": 0
      },
      {
        "name": "bene_day_care",
        "index": 18,
        "datatype": "string",
        "nullable": 0
      }
    ],
    "primary-rows": [
      {
        "row-status": 1,
        "columns": {
          "emp_id": [ 102 ],
          "manager_id": [ 501 ],
          "emp_fname": [ "Fran" ],
          "emp_lname": [ "Whitney" ],
          "dept_id": [ 400, 1, 100 ],
          "street": [ "49 East Washington Street" ],
          "city": [ "Needham" ],
          "state": [ "MA" ],
          "zip_code": [ "02192    " ],
          "phone": [ "6175554321", 1, "6175553985" ],
          "status": [ "A" ],
          "ss_number": [ "017349033" ],
          "salary": [ 50000, 1, 45700 ],
          "start_date": [ "1994-02-26" ],
          "termination_date": [ null ],
          "birth_date": [ "1966-06-05" ],
          "bene_health_ins": [ "Y" ],
          "bene_life_ins": [ "Y" ],
          "bene_day_care": [ "N" ]
        }
      },
      {
        "row-status": 0,
        "columns": {
          "emp_id": [ 129 ],
          "manager_id": [ 902 ],
          "emp_fname": [ "Philip" ],
          "emp_lname": [ "Chin" ],
          "dept_id": [ 200 ],
          "street": [ "59 Pond Street" ],
          "city": [ "Atlanta" ],
          "state": [ "GA" ],
          "zip_code": [ "30339    " ],
          "phone": [ "4045552341" ],
          "status": [ "A" ],
          "ss_number": [ "024608923" ],
          "salary": [ 38500 ],
          "start_date": [ "2005-08-04" ],
          "termination_date": [ null ],
          "birth_date": [ "1974-10-30" ],
          "bene_health_ins": [ "Y" ],
          "bene_life_ins": [ "Y" ],
          "bene_day_care": [ "N" ]
        }
      },
      {
        "row-status": 3,
        "columns": {
          "emp_id": [ 104, 1, null ],
          "manager_id": [ 902, 1, null ],
          "emp_fname": [ "Chris", 1, null ],
          "emp_lname": [ "Young", 1, null ],
          "dept_id": [ 200, 1, null ],
          "street": [ "57 Carver Street", 1, null ],
          "city": [ "Concord", 1, null ],
          "state": [ "MA", 1, null ],
          "zip_code": [ "12345    ", 1, null ],
          "phone": [ "6185551234", 1, null ],
          "status": [ "A", 1, null ],
          "ss_number": [ "010123456", 1, null ],
          "salary": [ 63000, 1, null ],
          "start_date": [ "2018-05-06", 1, null ],
          "termination_date": [ null ],
          "birth_date": [ "1984-10-12", 1, null ],
          "bene_health_ins": [ "Y", 1, null ],
          "bene_life_ins": [ "Y", 1, null ],
          "bene_day_care": [ null ]
        }
      }
    ],
    "filter-rows": [
      {
        "row-status": 0,
        "columns": {
          "emp_id": [ 148 ],
          "manager_id": [ 1293 ],
          "emp_fname": [ "Julie" ],
          "emp_lname": [ "Jordan" ],
          "dept_id": [ 300 ],
          "street": [ "144 Great Plain Avenue" ],
          "city": [ "Winchester" ],
          "state": [ "MA" ],
          "zip_code": [ "01890    " ],
          "phone": [ "6175557835" ],
          "status": [ "A" ],
          "ss_number": [ "501704733" ],
          "salary": [ 51432 ],
          "start_date": [ "2004-10-04" ],
          "termination_date": [ null ],
          "birth_date": [ "1959-12-13" ],
          "bene_health_ins": [ "Y" ],
          "bene_life_ins": [ "Y" ],
          "bene_day_care": [ "N" ]
        }
      }
    ],
    "delete-rows": [
      {
        "row-status": 0,
        "columns": {
          "emp_id": [ 105 ],
          "manager_id": [ 501 ],
          "emp_fname": [ "Matthew" ],
          "emp_lname": [ "Cobb" ],
          "dept_id": [ 100 ],
          "street": [ "77 Pleasant Street" ],
          "city": [ "Waltham" ],
          "state": [ "MA" ],
          "zip_code": [ "02154    " ],
          "phone": [ "6175553840" ],
          "status": [ "A" ],
          "ss_number": [ "052345739" ],
          "salary": [ 62000 ],
          "start_date": [ "1994-07-02" ],
          "termination_date": [ null ],
          "birth_date": [ "1968-12-04" ],
          "bene_health_ins": [ "Y" ],
          "bene_life_ins": [ "Y" ],
          "bene_day_care": [ "N" ]
        }
      }
    ],
    "dwchilds": {
      "dept_id": [
        {
          "dept_id": 100,
          "dept_name": "R & D"
        },
        {
          "dept_id": 200,
          "dept_name": "Sales"
        },
        {
          "dept_id": 300,
          "dept_name": "Finance"
        },
        {
          "dept_id": 400,
          "dept_name": "Marketing"
        },
        {
          "dept_id": 500,
          "dept_name": "Shipping"
        }
      ]
    }
  }
}

ModelStore JSON

A ModelStore JSON is an object that contains elements representing various aspects of a ModelStore provided in the PowerBuilder.Data package.

This format is supported by the following methods (For all formats supported by a particular method, see Applicable methods):

Object

Method

DataWindow ImportJson, ImportJsonByKey
JSONPackage GetValueToDataWindow

The structure of ModelStore JSON is as follows:

{
"identity": "6476d6ed-1e62-4441-a5bb-9a6ac62d0256",
"version":1.0,
"modelstore":{"name":"model class full name",
  "meta-columns":[COLUMN-META1, COLUMN-META2...],
  "rows": [MS-STANDARD-ROW1, MS-STANDARD-ROW2...],
}
}

Elements Description

identity

A string identifying the format. Should keep unchanged.

version

An integer specifying the format version. Currently it is 1.0.

modelstore

An object indicating it is a ModelStore.

name

A string specifying the name of the ModelStore.

meta-columns (optional)

An array specifying the Information of the ModelStore columns.

For elements about the column meta information, see COLUMN-META below.

rows

An array specifying the information of the ModelStore rows.

For elements about the ModelStore row, see MS-STANDARD-ROW below.


COLUMN-META

COLUMN-META is an object that contains elements representing the various aspects of the ModelStore column. For example,

{
"name": "department_id",
"index": 1,
"datatype": "long", 
"nullable": 0
}

name: (required) a string specifying the name of the column.

index: (required) an integer specifying the sequence order of the column. This value is not used by import.

datatype: (required) a string specifying the type of the column. This value is not used by import.

nullable: (required) an integer specifying whether to allow a null value. 0 - a null value is not allowed, 1 - a null value is allowed.

MS-STANDARD-ROW

MS-STANDARD-ROW is an object that contains elements representing the detailed information of the ModelStore row. For example,

{
 "row-status": 0,
 "columns":{"column1": [CURRENT-VALUE, COLUMN-STATUS, ORIGINAL-VALUE],
           "column2": [CURRENT-VALUE, COLUMN-STATUS, ORIGINAL-VALUE],
            ...
          }
}

row-status: (required) an integer specifying the status of the ModelStore row. -1 - NotTracked!, 0 - NotModified!, 1 - DataModified!, 2 - New!, 3 - NewModified!, 4 - Deleted!

columns: (required) an object specifying the ModelStore column information including the current value, the column status, and the original value.

  • CURRENT-VALUE: (required) The current value of the column, in the following data type: integer, string, boolean, or null.

  • COLUMN-STATUS: (optional) An integer specifying the column status. 0 - (default) NotModified!, 1 - DataModified!.

  • ORIGINAL-VALUE: (optional) The original value of the column, in the following data type: integer, string, boolean, or null. The default type is null.

Example

Here is an example of a ModelStore JSON string:

{
  "identity": "6476d6ed-1e62-4441-a5bb-9a6ac62d0256",
  "version": 1.0,
  "platform": "C#",
  "mapping-method": 0,
  "modelstore": {
    "name": "WebApplication1.Models.Department",
    "meta-columns": [
      {
        "name": "Dept_Id",
        "index": 0,
        "datatype": "System.Int32",
        "nullable": 0
      },
      {
        "name": "Dept_Name",
        "index": 1,
        "datatype": "System.String",
        "nullable": 0
      },
      {
        "name": "Dept_Head_Id",
        "index": 2,
        "datatype": "System.Int32",
        "nullable": 1
      }
    ],
    "rows": [
      {
        "row-status": -1,
        "columns": {
          "Dept_Id": [ 100 ],
          "Dept_Name": [ "R & D5" ],
          "Dept_Head_Id": [ 105 ]
        }
      },
      {
        "row-status": 1,
        "columns": {
          "Dept_Id": [ 200 ],
          "Dept_Name": [ "Sales" ],
          "Dept_Head_Id": [ 148, 1, 129 ]
        }
      },
      {
        "row-status": 0,
        "columns": {
          "Dept_Id": [ 300 ],
          "Dept_Name": [ "Finance" ],
          "Dept_Head_Id": [ 102 ]
        }
      },
      {
        "row-status": 3,
        "columns": {
          "Dept_Id": [ 600 ],
          "Dept_Name": [ "PB" ],
          "Dept_Head_Id": [ 102 ]
        }
      }
    ]
  }
}

Applicable methods

The following table summarizes the methods that support the different JSON formats.

Objects

Methods

Supported JSON Formats

DataWindow

ImportRowFromJSON, ExportRowAsJson

1. Plain JSON: One-level structure

ImportJson, ImportJsonByKey

1. Plain JSON: Two-level structure

2. DataWindow JSON

3. ModelStore JSON

ExportJson

1. Plain JSON: Two-level structure

2. DataWindow JSON

JSONPackage

LoadString, LoadFile

1. An object which contains a set of key/value pairs where key is the name of a JSONObjectItem-type object and the value for the key is a string, object, or array in the following formats: plain JSON, DataWindow JSON, or ModelStore JSON.

GetValueToDataWindow

1. Plain JSON: Two-level structure

2. DataWindow JSON

3. ModelStore JSON

4. An object which contains a set of key/value pairs where key is the name of a JSONObjectItem-type object and the value for the key is a string, object, or array in the following formats: plain JSON, DataWindow JSON, or ModelStore JSON.

SetValueByDataWindow

1. Plain JSON: Two-level structure

2. DataWindow JSON

3. An object which contains a set of key/value pairs where key is the name of a JSONObjectItem-type object and the value for the key is a string, object, or array in the following formats: plain JSON, DataWindow JSON, or ModelStore JSON.

RESTClient

Retrieve

1. Plain JSON: Two-level structure

RetrieveOne 1. Plain JSON: One-level structure & Two-level structure

Submit

1. Plain JSON: Two-level structure

2. DataWindow JSON

3. An object which contains a set of key/value pairs where key is the name of a JSONObjectItem-type object and the value for the key is a string, object, or array in the following formats: plain JSON, DataWindow JSON, or ModelStore JSON.


See also

Plain JSON: One-level or Two-level structure

DataWindow JSON

ModelStore JSON