RAD Server: Returning JSON with TJSONValue & More

by | May 9,2019

Table of Contents

[See the full index of articles for more information]

RAD Server provides support for handling JSON data that can be consumed by different programming languages and tools. Creating a JSON string,transmitting the string as a response, and having client application code process the return is okay for smaller amounts of data. Imagine how large a JSON array response would be for a database or complex data structure. RAD Studio provides two frameworks for working with JSON data. This chapter will cover a few of the many ways to return JSON to a calling application.

Two Frameworks for Handling JSON Data

RAD Studio provides two frameworks to handle JSON data:

  • JSON Objects Framework – creates temporary objects to read and write JSON data.

  • Readers and Writers JSON Framework – allows you to read and write JSON data directly.

The JSON objects framework requires the creation of a temporary object to parse or generate JSON data. To read or write JSON data, you have to create an intermediate memory object such as TJSONObject, TJSONArray, or TJSONString before reading and writing the JSON.

The Readers and Writers JSON Framework allows applications to read and write JSON data directly to a stream, without creating a temporary object. Not having to create a temporary object to read and write the JSON provides better performance and improved memory consumption.

Using JSONValue

Use the JSON Objects Framework to create JSON strings by assembling them in code. JSONValue is the ancestor class for all the JSON classes used for defining JSON string, object, array, number, Boolean, true, false, and null values. Included in the RAD Studio JSON implementation are the following classes and methods:

TJSONObject – implements a JSON object. Methods in TJSONObject Include:

  • Parse – method to parse a JSON data stream and store the encountered JSON pairs into a TJSONObject instance.

  • ParseJSONValue – method to parse a byte array and create the corresponding JSON value from the data.

  • AddPair method – Adds a new JSON pair to a JSON object.

  • GetPair method – Returns the key-value pair that has the specified I index in the list of pairs of a JSON object, or nil if the specified I index is out of bounds.

  • GetPairByName method – returns a key-value pair, from a JSON object, that has a key part matching the specified PairName string, or nil if there is no key matching PairName.

  • SetPairs – Defines the list of key-value pairs that this JSON object contains.

  • FindValue – Finds and returns a TJSONValue instance located at the specified Apath JSON path. Otherwise, returns nil.

  • Get Value – Returns the value part from a key-value pair specified by the Name key in a JSON object, or nil if there is no key that matches Name.

  • Pairs – Accesses the Key-value pair that is located at the specified Index in the list of pairs of the JSON object, or nil if the specified Index is out of bounds.

  • GetCount – Returns the number of key-value pairs of a JSON object.

TJSONArray – Implements a JSON array. JSONArray methods include:

  • Add – Adds a non-null value given through the Element parameter to the current element list.

  • Get – Returns the element at the given index in the JSON array.

  • Pop – Removes the first element from the JSON array.

  • Size – Returns the size of the JSON array.

  • ToBytes – Serializes the current JSON array content into an array of bytes.

  • ToString – Serializes the current JSON array into a string and returns the resulting string.

Additional JSON classes include:

  • TJSONString – Implements a JSON string.

  • TJSONNumber – Implements a JSON number.

  • TJSONBool – JSON Boolean value.

  • TJSONTrue – Implements a JSON true value.

  • TJSONFalse – Implements a JSON false value.

  • TJSONNull – Implements a JSON null value.

RAD Server App using JSONValue classes

The following RAD Server application implements a Get endpoint that uses several of the JSON classes to create, parse and display the results of JSONObject, JSONArray and JSONValue.

Delphi:

procedure TJSONValueDemoResource1.Get(const AContext: TendpointContext; 	const ARequest: TEndpointRequest; const AResponse: TEndpointResponse); var JSONColor : TJSONObject; JSONArray : TJSONArray; JSONObject : TJSONObject; JSONValue : TJSONValue; begin // create some JSON objects JSONColor := TJSONObject.Create(); JSONColor.AddPair('red'</span>); JSONColor.AddPair('#ff0000'</span>); JSONColor.AddPair('blue'</span>); JSONColor.AddPair('#0000FF'</span>); JSONArray := TJSONArray.Create(); JSONArray.Add(JSONColor); JSONObject := TJSONObject.Create(); JSONObject.AddPair('colors', JSONArray); JSONValue := TJSONObject.Create(); JSONValue := TJSONObject.ParseJSONValue(JSONObject.ToJSON); AResponse.building.SetValue( TJSONString.Create( JSONColor.ToJSON + ',' + JSONObject.ToJSON + ',{"name":"' + JSONValue.GetValue<string>('colors[0].name') + '","hex":"'+ JSONValue.GetValue<string>('colors[0].hex') + '"}' ), True</span>); end</span>;

C++:

void TJSONValueDemoResource1::Get(TEndpointContext* AContext, TEndpointRequest* ARequest, TEndpointResponse* AResponse) { TJSONObject * JSONColor = new TJSONObject(); JSONColor->AddPair(new TJSONPair("name", "red"</span>)); JSONColor->AddPair(new TJSONPair("hex", "#ff0000"</span>)); JSONColor->AddPair(new TJSONPair("name", "blue"</span>)); JSONColor->AddPair(new TJSONPair("hex", "#0000FF"</span>)); TJSONArray * JSONArray = new TJSONArray(); JSONArray->Add(JSONColor); TJSONObject * JSONObject = new TJSONObject(); JSONObject->AddPair("colors", JSONArray); TJSONValue * JSONValue = new TJSONObject(); JSONValue = (TJSONObject*)TJSONObject::ParseJSONValue(JSONObject->ToJSON()); AResponse->building->SetValue( 	new TJSONString( 	 JSONColor->ToJSON() 	 + "," 	 + JSONObject->ToJSON() 	 + ",{\"name\":\""+ JSONValue->GetValue<String>("colors[0].name") 	 + "\",\"hex\":\""+ JSONValue->GetValue<String>("colors[0].hex") 	 + "\"}" 	),true</span>); }

Figure 1: Browser output using JSONObject, JSONArray and JSONValue

VCL Client App using JSONValue classes

The following VCL client application uses several of the JSONValue based classes to create, parse and display the results of TJSONArray and TJSONObject based operations.

Delphi

unit MainUnit;

interface

uses Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics, Vcl.Controls, Vcl.Forms, Vcl.Dialogs, Vcl.StdCtrls;

type TForm2 = class(TForm) Button1: TButton; Memo1: TMemo; procedure Button1Click(Sender: TObject); private { Private declarations } public { Public declarations } end</span>;

var Form2: TForm2;

implementation uses JSON; {$R *.dfm}

procedure TForm2.Button1Click(Sender: TObject); var JSONColor : TJSONObject; JSONArray : TJSONArray; JSONObject : TJSONObject; JSONValue : TJSONValue; mBoolean : boolean</span>; begin mBoolean := true</span>;

 JSONColor := TJSONObject.Create(); JSONColor.AddPair('name', 'red'</span>); JSONColor.AddPair('hex', '#f00'</span>); JSONColor.AddPair('mBoolean',TJSONBool.Create(mBoolean));

 JSONArray := TJSONArray.Create(); JSONArray.Add(JSONColor);

 JSONObject := TJSONObject.Create(); JSONObject.AddPair('colors', JSONArray); JSONObject.AddPair('mBoolean',TJSONBool.Create(mBoolean));

 Memo1.Lines.Clear(); Memo1.Lines.Add('JSONColor: '+JSONColor.ToJSON); Memo1.Lines.Add(''</span>); Memo1.Lines.Add('JSONObject: '+JSONObject.ToJSON); Memo1.Lines.Add(''</span>);

 JSONValue := TJSONObject.Create(); JSONValue := TJSONObject.ParseJSONValue(JSONObject.ToJSON);

 Memo1.Lines.Add('colors[0]'</span>); Memo1.Lines.Add('name: '+ JSONValue.GetValue<string>('colors[0].name'</span>)); Memo1.Lines.Add('hex: '+ JSONValue.GetValue<string>('colors[0].hex'</span>)); Memo1.Lines.Add('mBoolean: '+JSONValue.GetValue<string>('colors[0].mBoolean'</span>)); end</span>;

C++:

void __fastcall TForm1::Button1Click(TObject *Sender) { 	boolean mBoolean = true</span>; 	TJSONObject * JSONColor = new TJSONObject(); 	JSONColor->AddPair(new TJSONPair("name", "red"</span>)); 	JSONColor->AddPair(new TJSONPair("hex", "#ff0000"</span>)); 	JSONColor->AddPair(new TJSONPair("name", "blue"</span>)); 	JSONColor->AddPair(new TJSONPair("hex", "#0000FF"</span>)); 	JSONColor->AddPair(new TJSONPair("mBoolean",new TJSONBool(mBoolean)));

	TJSONArray * JSONArray = new TJSONArray(); 	JSONArray->Add(JSONColor); 	TJSONObject * JSONObject = new TJSONObject(); 	JSONObject->AddPair("colors", JSONArray);

	Memo1->Lines->Clear(); 	Memo1->Lines->Add("JSONColor: "+JSONColor->ToJSON()); 	Memo1->Lines->Add(""</span>); 	Memo1->Lines->Add("JSONObject: "+JSONObject->ToJSON()); 	Memo1->Lines->Add(""</span>);

	TJSONValue * JSONValue = new TJSONObject(); 	JSONValue = (TJSONObject*)TJSONObject::ParseJSONValue(JSONObject->ToJSON());

	Memo1->Lines->Add("colors[0]"</span>); 	Memo1->Lines->Add("name: "+JSONValue->GetValue<String>("colors[0].name"</span>)); 	Memo1->Lines->Add("hex: "+JSONValue->GetValue<String>("colors[0].hex"</span>)); 	Memo1->Lines->'Add( 		"mBoolean: "+JSONValue->GetValue<String>("colors[0].mBoolean"</span>)); }

Figure 2: VCL Client App using JSON classes

Using JSONWriter

Using JSONWriter simplifies RAD Server application development to craft custom JSON that delivers data for programming language clients to consume. Use JSONWriter to start your JSON object, write a property name and a value, keep writing properties and values until you end the JSON object.

A Simple JSONWriter Example

Here is an example GET method that returns some data using JSONWriter’s WriteStartArray, WriteStartObject, WritePropertyName, WriteValue, WriteEndObject, WriteEndArray methods. Setting the JSONWriter.Formatting property value to Indented will produce a clean looking response in the Browser.

Delphi:

implementation

{%CLASSGROUP 'System.Classes.TPersistent'} uses System.JSON.Types;

{$R *.dfm}

procedure TEmployeeResource1.Get(const AContext: TendpointContext; 	const ARequest: TEndpointRequest; const AResponse: TEndpointResponse); begin // set the JSONWriter formatting to indented AResponse.building.JSONWriter.Formatting := TJSONFormatting.Indented;

 // start the JSON Array AResponse.building.JSONWriter.WriteStartArray;

 // start the JSON object

 AResponse.building.JSONWriter.WriteStartObject; AResponse.building.JSONWriter.WritePropertyName('Test'</span>); AResponse.building.JSONWriter.WriteValue('Foo bar'</span>);

 // add WritePropertyName and WriteValue statements as often as needed

 // end the JSON object AResponse.building.JSONWriter.WriteEndObject;

 // write as many additional JSON objects as you need

 // end the JSON array AResponse.building.JSONWriter.WriteEndArray; end</span>;

C++:

void TSimpleJSONWriterResource1::Get(TEndpointContext* Acontext, TEndpointRequest* ARequest, TEndpointResponse* AResponse) { // set the JSONWriter formatting to indented AResponse->building->JSONWriter->Formatting = TJsonFormatting::Indented;

 // start the JSON Array AResponse->building->JSONWriter->WriteStartArray();

 // start the JSON object

 AResponse->building->JSONWriter->WriteStartObject(); AResponse->building->JSONWriter->WritePropertyName("Test"</span>); AResponse->building->JSONWriter->WriteValue("Foo bar"</span>);

 // add WritePropertyName and WriteValue statements as often as needed

 // end the JSON object AResponse->building->JSONWriter->WriteEndObject();

 // write as many additional JSON objects as you need

 // end the JSON array AResponse->building->JSONWriter->WriteEndArray(); }

Figure 3: JSON output with Formatting property set to Indented

Using JSONWriter with a SQL Query

You can also use a SQL query (or table) rows and columns (fields) of database data and with JSONWriter build up your endpoint’s response to a Get and GetItem request. To return custom JSON for a database query here are the Get and GetItem method implementations.

Delphi:

procedure TFireDACResource1.Get(const AContext: TendpointContext; 	const ARequest: TEndpointRequest; const AResponse: TEndpointResponse); var RowCount,FieldIndex : integer</span>; begin EmployeeQuery.Close(); EmployeeQuery.SQL.Clear(); EmployeeQuery.SQL.Add('select * from employee'</span>); EmployeeQuery.Open(); // using JSONWriter to craft custom JSON return for all rows AResponse.building.JSONWriter.WriteStartArray; for RowCount := 0 to EmployeeQuery.RecordCount-1 do begin AResponse.building.JSONWriter.WriteStartObject; for FieldIndex := 0 to EmployeeQuery.FieldCount-1 do begin Aresponse.building.JSONWriter.WritePropertyName( EmployeeQuery.Fields[FieldIndex].FieldName); Aresponse.building.JSONWriter.WriteValue( EmployeeQuery.FieldByName( EmployeeQuery.Fields[FieldIndex].FieldName).AsString) end</span>; AResponse.building.JSONWriter.WriteEndObject; EmployeeQuery.Next end</span>; AResponse.building.JSONWriter.WriteEndArray; end</span>;

procedure TFireDACResource1.GetItem(const AContext: TendpointContext; 	const ARequest: TEndpointRequest; const AResponse: TEndpointResponse); var FieldIndex : integer</span>; LItem : string</span>; begin LItem := ARequest.Params.Values['item'</span>]; EmployeeQuery.Close(); EmployeeQuery.SQL.Clear(); EmployeeQuery.SQL.Add('select * from employee where EMP_NO = '+LItem); EmployeeQuery.Open(); // using JSONWriter to craft custom JSON return for the selected employee row AResponse.building.JSONWriter.WriteStartObject; for FieldIndex := 0 to EmployeeQuery.FieldCount-1 do begin Aresponse.building.JSONWriter.WritePropertyName( EmployeeQuery.Fields[FieldIndex].FieldName); Aresponse.building.JSONWriter.WriteValue( EmployeeQuery.FieldByName( EmployeeQuery.Fields[FieldIndex].FieldName).AsString) end</span>; end</span>;

C++:

void TFireDACResource1::Get(TEndpointContext* Acontext, 	TEndpointRequest* Arequest, TEndpointResponse* AResponse) { 	EmployeeQuery->Close(); 	EmployeeQuery->SQL->Clear(); 	EmployeeQuery->SQL->Add("select * from employee"</span>); 	EmployeeQuery->Open(); 	// using JSONWriter to craft custom JSON return for all rows 	AResponse->building->JSONWriter->WriteStartArray(); 	for (int RowCount = 0; RowCount < EmployeeQuery->RecordCount; RowCount++) { 		AResponse->building->JSONWriter->WriteStartObject(); 		for (int FieldIndex = 0; FieldIndex < EmployeeQuery�'FieldCount; 				 FieldIndex++) { 			AResponse->building->JSONWriter->WritePropertyName( 				EmployeeQuery->Fields->Fields[FieldIndex]->FieldName); 			AResponse->building->JSONWriter->WriteValue( 				EmployeeQuery->FieldByName( 					EmployeeQuery->Fields->Fields[FieldIndex] 						->FieldName)->AsString);	} 	AResponse->building->JSONWriter->WriteEndObject(); 	EmployeeQuery->Next(); } AResponse->building->JSONWriter->WriteEndArray(); }

void TFireDACResource1::GetItem(TEndpointContext* Acontext, 	TEndpointRequest* ARequest, TEndpointResponse* AResponse) { 	String item; 	item = ARequest->Params->Values["item"</span>]; 	EmployeeQuery->Close(); 	EmployeeQuery->SQL->Clear(); 	EmployeeQuery->SQL->Add("select * from employee where EMP_NO = "+item); 	EmployeeQuery->Open(); 	// using JSONWriter to craft custom JSON for the selected employee row 	for (int FieldIndex = 0; FieldIndex < EmployeeQuery->FieldCount; 			 FieldIndex++) { 	 AResponse->building->JSONWriter->WritePropertyName( 		EmployeeQuery->Fields->Fields[FieldIndex]->FieldName); 	 AResponse->building->JSONWriter->WriteValue( 		EmployeeQuery->FieldByName( 			EmployeeQuery->Fields->Fields[FieldIndex] 				->FieldName)->AsString); 	} }

The output of the Get and GetItem method calls are in the browser windows below.

Figure 4: JSON output for the Employee table’s rows and columns

Figure 5: JSON output for a specific Employee

Using FireDAC Batch Move and JSONWrite

If your application uses RAD Studio’s Delphi or C++ for building clients, then FireDAC components are available to produce and consume a stream containing database metadata and data encoded in JSON for a response from one of your RAD Server endpoints. Client applications that use other languages, like JavaScript would not know how to deal with all of the database information and data that would be included in the response. What is needed is a way to provide some clean JSON that JavaScript or other languages would expect to receive.

Returning JSON Database Data Using a Memory Stream

Given a simple RAD Server resource, we can use the FireDAC components to get access to a database table information and produce the result as a JSON string. Add a FDConnection component and connect it with the InterBase sample Employee.gdb database. Add a FDTable component and set its Table property to the Employee table. Add a FDStanStorageJSONLink component to facilitate creating of the JSON.

Figure 6: RAD Server project’s resource module

When you try to build the RAD Server Delphi based application, you may get a set of warnings and a dialog box will appear to allow you to make your application package compatible with other installed packages.

Figure 7: Delphi project build dialog box for required packages

Clicking the OK button will add the required package files to the requires section in your project. For C++Builder you can add the required packages manually (right mouse click on the Requires node in the project manager window and select Add Reference… from the pop-up menu).

Figure 8: Delphi RAD Server FireDAC Memory Stream JSON project and Figure 9: C++ RAD Server FireDAC Memory Stream JSON project

Here is the RAD Server Get method implementation that uses a memory stream to contain JSON from the employee table:

Delphi:

procedure TEmpfiredacResource1.Get(const AContext: TendpointContext; 	const ARequest: TEndpointRequest; const AResponse: TEndpointResponse); var mStream: TMemoryStream; begin mStream := TMemoryStream.Create; AResponse.building.SetStream(mStream,'application/json', True</span>); EmployeeTable.Open; EmployeeTable.SaveToStream(mStream, sfJSON); end</span>;

C++:

void TFireDACResource1::Get(TEndpointContext* Acontext, 	TEndpointRequest* ARequest, TEndpointResponse* AResponse) { 	TMemoryStream * mStream = new TMemoryStream; 	AResponse->building->SetStream(mStream,"application/json", True); 	EmployeeTable->Open(); 	EmployeeTable->SaveToStream(mStream, sfJSON); }

Use a browser and the URL http://localhost:8087/FireDAC to get the response containing the JSON data for the database’s employee table. The JSON contains much more information than just the data. Also included in the response is information about the table, columns, types, etc.

Figure 10: Browser window containing the JSON response

This is a definitely not the simple column and value JSON that other languages could consume without parsing the response using code.

Using FireDACs BatchMove,BatchMoveDataSetReader and BatchMoveJSONWriter

Taking advantage of FireDAC’s FDBatchMove, FDBatchMoveDataSetReader and FDBatchMoveJSONWriter components will simplify creating the JSON response.

Use the RAD Server Package Wizard to create a new package project with Data Module Resource named BatchMove with a Get EndPoint. Add FireDAC FDConnection, FDQuery, FDBatchMove, FDBatcMoveDataSetReader and FDBatchMoveJSONWriter components to the resource module. Rename the FDConnection as DatabaseConnection. Rename the FDQuery as CustomerQuery. Save the project.

Figure 11: Resource module with FireDAC Query, BatchMove, DataSetReader and JSONWriter

Connect the DataBaseConnection component to the InterBase Employee.gdb sample database. Set the CustomerQuery SQL string to select * from customer. Set the FDBatchMoveDataSetReader’s DataSet property to CustomerQuery.

The coding for the Get method can be simplified as follows:

Delphi:

procedure TEmployeeResource1.Get(const AContext: TendpointContext; 	const ARequest: TEndpointRequest; const AResponse: TEndpointResponse); begin FDBatchMoveJSONWriter1.JsonWriter := AResponse.building.JSONWriter; FDBatchMove1.Execute; end</span>;

C++:

void TEmployeeResource1::Get(TEndpointContext* Acontext, 	TEndpointRequest* ARequest, TEndpointResponse* AResponse) { FDBatchMoveJSONWriter1->JsonWriter = AResponse->building->JSONWriter; FDBatchMove1->Execute(); }

Before you compile and run the application server, you’ll need to add a few FireDAC libraries to the Requires section of your project. You’ll find these files in the C:\Program Files (x86)\Embarcadero\Studio\20.0\lib for each of your target platforms.

Figure 12: Delphi BatchMove project and Figure 13: C++ BatchMove project

Calling the GET method using the URL http://localhost:8087/BatchMove returns the JSON data result:

Figure 14: Browser with JSON result using BatchMove

FDBatchMoveJSONWriter provides multiple options for formatting the JSON result. DataDef’s Formatting sub-property for TBatchMoveJSONWriter, by default, is set to None.

Figure 15: BatchMoveJSONWriter DataDef sub-properties in the ObjectInspector

Set the Formatting sub-property to Indented. Setting this sub-property will result in JSON responses that are laid out in a more readable form.

Figure 16: DataDef’s Formatting sub-property set to Indented

Re-run the RAD Server application. Use a browser to call the Get method (http://localhost:8087/BatchMove) and see the indent formatted JSON output.

Figure 17: Browser JSON result with Formatting set to Indented

Additional Information

Table of Contents

Free Trial

Explore all the products and find the right solution for your business.

Start your free trial

Latest Content

Perpetual vs Subscription: SQL Server Tools Pricing Explained

Understanding and Managing High Memory Usage in SQL Server

Essentials of Database Indexing for SQL Server

A User-Centric Tuning Philosophy

Macros – Add custom properties to specific objects

See More

Recommended Articles

Top 10 Concerns Keeping SQL Server DBAs Awake at Night

SQL Server Database Administrators (DBAs) have a tough job. They are responsible for ensuring database performance, security, and ....
Read More

Achieving PCI Compliance in Your SQL Server Environment

Payment Card Industry Data Security Standard (PCI DSS) is a globally recognized security standard designed to ensure that businesses handling credit card .....
Read More

SQL Server Monitoring Reimagined: Predictive Analytics and AI Trends for 2025

The world of SQL Server monitoring has undergone a major transformation. In the past, database administrators (DBAs) relied on reactive methods to address ......
Read More