JSON.Export

JSON.Export

Exports data to the USoft JSON format on the basis of a SELECT statement.

Exports binary data (of the BLOB data type) to base64 encoding.

Syntax

INVOKE JSON.Export WITH
SELECT      { parameter-assignment | column-expression } ...
FROM        table
ORDER BY    order-by-list

parameter-assignment  ::=  value  parameter

column-expression     ::=

  {    column        |
       OLD( column ) |
       *             |
       OLD( * )      }

The required SELECT clause must contain at least 1 item. Each item in this clause is either a parameter-assignment or a column-expression.

Each parameter-assignment is a value-name pair. The SECOND element is the name of a parameter. The FIRST element is the value assigned to that parameter. Please find values and parameters in the table below.

Each column-expression is either a literal column name or the SQL * (asterisk) wildcard. Column aliases are not supported. Other SQL functions than OLD() are NOT supported. The OLD() function is supported only if you call JSON.Export from constraint SQL. Even in constraint SQL, you cannot export both old value and new value for a column: you cannot use column and OLD( column ) for the same column in the same SELECT list.

It is customary, but not mandatory, to list value parameter pairs before column expressions. The order of the items in the SELECT clause determines the order of the attributes in the XML output. The optional ORDER BY clause determines the document order of the records in the JSON output.

The required FROM clause must contain the name of exactly 1 table or logical view.

Example

INVOKE    JSON.Export WITH
SELECT    'c:\travel\tours.json'  WriteOutputTo
,         *
FROM      tour

Parameters

This table is sorted by parameter and lists all value-parameter pairs supported in the SELECT output-lists passed to JSON.Export. The use of each parameter is optional. If you do not specify a given parameter and it has a default value corresponding to it, this is the value applied.

Both value and parameter are case-insensitive.

Encapsulation

This statement uses Array encapsulation:

INVOKE JSON.Export WITH SELECT
	'Array' Encapsulation,
	'Indent' PrettyPrint,
	* FROM tour_programme

and returns the following result:

[
	{
		"DESTINATION":"AUSTRALIA",
		"TOUR_TYPE":"ISLAND SUNTANNER",
		"NUM_DAYS":12,
		"MAX_AGE":65,
		"DESCRIPTION":"Exclusive sailing adventure to the Great Barrier Reef Two nights on Fraser Island - the world's largest sand island Hire of 4 wheel drive vehicles on Fraser Island A night at an outback cattle station Two nights on beautiful Great Keppel Island Tjapukai Aboriginal Dance Theatre"
	},
	{
		"DESTINATION":"AUSTRALIA",
		"TOUR_TYPE":"ROCK 'N' ALICE",
		"NUM_DAYS":12,
		"MAX_AGE":0,
		"DESCRIPTION":"Uluru rock sighting Outback town of Alice Springs Entrance fees to Uluru National Park King's Canyon and the Olgas Western MacDonnell Ranges"
	}
]

This statement uses Object encapsulation:

INVOKE JSON.Export WITH SELECT
	'Object' Encapsulation,
	'Indent' PrettyPrint,
	* FROM tour_programme

and returns the following result:

{
	"TOUR_PROGRAMME": [
		{
			"DESTINATION":"AUSTRALIA",
			"TOUR_TYPE":"ISLAND SUNTANNER",
			"NUM_DAYS":12,
			"MAX_AGE":65,
			"DESCRIPTION":"Exclusive sailing adventure to the Great Barrier Reef Two nights on Fraser Island - the world's largest sand island Hire of 4 wheel drive vehicles on Fraser Island A night at an outback cattle station Two nights on beautiful Great Keppel Island Tjapukai Aboriginal Dance Theatre"
		},
		{
			"DESTINATION":"AUSTRALIA",
			"TOUR_TYPE":"ROCK 'N' ALICE",
			"NUM_DAYS":12,
			"MAX_AGE":0,
			"DESCRIPTION":"Uluru rock sighting Outback town of Alice Springs Entrace fees to Uluru National Park King's Canyon and the Olgas Western MacDonnell Ranges"
		}
	]
}