Using SQL queries in JSON in KBMMW

How can I quickly find the values in JSON by referring to the KBMMW JSON object operation?

One way is through the method of traversal, in fact, in KBMMW there is a flexible way to query,

is to query the values in JSON by SQL. In other words, the Tkbmmwmemsql control is used to implement.

Tkbmmwmemsql can also be used to query XML and CSV files, just talk about JSON queries today.

We first put a tkbmmwmemsql control in the form,

Set its properties.

To query JSON directly using the Kbmmwmemsql control, you first need to save the JSON string as a text file.

Here is a simple JSON file with the following content:

{"Result": [{"id": 1, "name": "Xalion", "date": "2017-07-05t23:05:48.290+08:00"},{"id": 2, "name": "Xalion", "Date": " 2017-07-05t23:05:48.290+08:00 "},{" id ": 3," "Name": "Xalion", "date": "2017-07-05t23:05:48.290+08:00"},{"id": 4, "name" : "Xalion", "date": "2017-07-05t23:05:48.290+08:00"},{"ID": 5, "name": "Xalion", "date": "2017-07-05t23:05:48.290+ "},{" id ": 6," "Name": "Xalion", "date": "2017-07-05t23:05:48.290+08:00"},{"id": 7, "name": "Xalion", "Date": " 2017-07-05t23:05:48.290+08:00 "},{" id ": 8," "Name": "Xalion", "date": "2017-07-05t23:05:48.290+08:00"},{"id": 9, "name" : "Xalion", "date": "2017-07-05t23:05:48.290+08:00"},{"ID": Ten, "Name": "Xalion", "date": "2017-07-05t23:05:48.290+ 08:00 "}]}

The file name is Aaa.json and is stored in the current directory.

Then we execute the following code

 procedure   Tform1.button5click (Sender: TObject);  var   Sqls:  string   begin   Sqls:  ="   Select Id,name,date from DataFile FILE (JSON, '. \aaa.json ')    + '   subset '/result/.* '   '  + '  

The operation results are as follows

Join the conditions to continue the query.

procedureTform1.button5click (sender:tobject);varSqls:string;beginSqls:='SELECT id,name,date from DataFile FILE (JSON, ". \aaa.json")'+'subset '/result/.* ''+'where id=5'+'ORDER by ID'; Memo2.  Lines.clear; Memo2.  Lines.add (SQLS); Kbmmwmemsql1. Execsql (SQLS);End;

The results of the operation are as follows:

How is it convenient?

Some people may think to save a file, inconvenient, how to do?

All right, let's change our posture and come back.

We can directly add the JSON object directly using Tkbmmwmemsql and then query it like a normal table.

The code is as follows:

procedureTform1.button6click (sender:tobject);varJs:tkbmmwjsonstreamer;   Fjson:tkbmmwoncustomobject; SQLS:string;beginJS:=tkbmmwjsonstreamer.Create; Fjson:=JS. Loadfromutf16string (Memo1.     Text); Js.     Free; Kbmmwmemsql1. Tables.add ('Myjsont', Fjson); SQLS:='SELECT id,name,date from Myjson'+'subset '/result/.* ''+'ORDER by ID'; Memo2.  Lines.clear; Memo2.  Lines.add (SQLS); Kbmmwmemsql1. Execsql (SQLS);End;

Run results

You can also add query criteria

procedureTform1.button6click (sender:tobject);varJs:tkbmmwjsonstreamer;   Fjson:tkbmmwoncustomobject; SQLS:string;beginJS:=tkbmmwjsonstreamer.Create; Fjson:=JS. Loadfromutf16string (Memo1.     Text); Js.     Free; Kbmmwmemsql1. Tables.add ('Myjsont', Fjson); SQLS:='SELECT id,name,date from Myjson'+'subset '/result/.* ''+'where id=6'+'ORDER by ID'; Memo2.  Lines.clear; Memo2.  Lines.add (SQLS); Kbmmwmemsql1. Execsql (SQLS);End;

Run results

After the harvest.

Note that to run the above example, please refer to Kbmmwjson, kbmmwsqljsonapi,kbmmwobjectnotation

These several units.

Using SQL queries in JSON in KBMMW