JSON Challenge

[John]

Nice set of libraries to build extension modules from. One of the best features of SB is its unlimited expansion ability in a seamless manor.

[John]

Maybe a good way to get people to try Script BASIC is to have a code challenge to build an extension module from a library of their choice.

[AIR]

MBC Version:

Code: Text

1. $MODULE json.inc
2.  
3. $EXECON
4.  
5. dim item as JSON_Object Ptr
6. dim json as JOBJECT
7.  
8. json.init("commits.json")
9.  
10. if json.jsontype = JSONArray then
11.     for integer i = 0 to json.count-1
12.         item = json.object(i)
13.         print string$(50,asc("-"))
14.         print "Name: ", $json.text(item,"commit.author.name")
15.         print "Commit Date: ", $json.text(item, "commit.author.date")
16.         print "SHA Hash: ", $json.text(item, "sha")
17.     next
18.     print string$(50,asc("-"))
19. end if
20.  

[John]

Sure looks like a BaCon contender without all the C underware exposed.

[John]

AIR,

The json extension module doesn't seem to have a way to load a JSON string other than from a file. I'm trying to use it with a web service.

[AIR]

Duplicate the 'load' function in the interface.c file, and in the duped function replace the json_parse_file call with json_parse_string

[John]

Thanks AIR!

Works great.

Code: C

1. /**
2. =section loadfile
3. =H json::loadfile(filename)
4.  
5. Loads filename, returns array of json objects
6. */
7. besFUNCTION(loadfile)
8.   pModuleObject p;
9.   char* filename;
10.  
11.   JSON_Array *items;
12.   JSON_Object *obj;
13.  
14.   p = (pModuleObject)besMODULEPOINTER;
15.  
16.   besARGUMENTS("z")
17.     &filename
18.   besARGEND
19.  
20.  jRoot = json_parse_file(filename);
21.  
22.   switch (json_value_get_type(jRoot)) {
23.     case JSONArray:
24.       items = json_value_get_array(jRoot);
25.       besRETURN_POINTER(items);
26.       break;
27.     case JSONObject:
28.       obj = json_value_get_object(jRoot);
29.       besRETURN_POINTER(obj);
30.       break;
31.   }
32.  
33.  
34. besEND
35.  
36.  
37. /**
38. =section loadstr
39. =H json::loadstr(json_str)
40.  
41. Loads json string, returns array of json objects
42. */
43. besFUNCTION(loadstr)
44.   pModuleObject p;
45.   char* json_str;
46.  
47.   JSON_Array *items;
48.   JSON_Object *obj;
49.  
50.   p = (pModuleObject)besMODULEPOINTER;
51.  
52.   besARGUMENTS("z")
53.     &json_str
54.   besARGEND
55.  
56.  jRoot = json_parse_string(json_str);
57.  
58.   switch (json_value_get_type(jRoot)) {
59.     case JSONArray:
60.       items = json_value_get_array(jRoot);
61.       besRETURN_POINTER(items);
62.       break;
63.     case JSONObject:
64.       obj = json_value_get_object(jRoot);
65.       besRETURN_POINTER(obj);
66.       break;
67.   }
68.  
69.  
70. besEND
71.  

Code: ScriptBasic

1. ' JSON - LoadStr
2.  
3. INCLUDE json.bas
4.  
5. json = """
6. {
7.    "client1": {
8.        "name": "Joe Blow",
9.        "age": 56,
10.        "address": {
11.            "city": "Tarrytown",
12.            "state": "NY",
13.            "zip": "10891"
14.        }
15.    },
16.    "client2": {
17.        "name": "John Smith",
18.        "age": 86,
19.        "address": {
20.            "city": "Cupertino",
21.            "state": "CA",
22.            "zip": "N\/A"
23.        }
24.    }
25. }
26. """
27.  
28. jObject = json::loadstr(json)
29. for i = 0 to json::count(jObject)-1
30.     obj = json::object(jObject,i)
31.     print string(40,"-"),"\n"
32.     print json::Get(obj,"name"),"\n"
33.     print json::Get(obj,"age"),"\n"
34.     print json::Get(obj,"address.city"),"\n"
35.     print json::Get(obj,"address.state"),"\n"
36.     print json::Get(obj,"address.zip"),"\n"
37. next
38. print string(40,"-"),"\n"
39.  

$ scriba json_loadstr.sb
----------------------------------------
Joe Blow
56
Tarrytown
NY
10891
----------------------------------------
John Smith
86
Cupertino
CA
N/A
----------------------------------------
$

[John]

AIR,

Can you please look at your PM's from me about the issues I'm having with the json extension module?

I'm starting to get the feeling it doesn't support arrays. 

[John]

PHP makes json simple with one function each to encode / decode JSON strings to/ from an associative array.

It looks like the parson based JSON extension module is going to need a lot more work to be usable.

[John]

I think I found another way around having to write my own json ENCODE/DECODE functions in Script BASIC. I discovered that MySQL has a set of functions to work with JSON data. I'm going to give this a try using the Script BASIC MySQL extension module.

Code: MySQL

1. mysql> SET @data = '{  
2.    '>     "Person": {    
3.     '>        "Name": "Homer",
4.    '>        "Age": 39,
5.     '>        "Hobbies": ["Eating", "Sleeping"]  
6.    '>     }
7.     '>  }';
8. Query OK, 0 rows affected (0.02 sec)
9.  
10. mysql> SELECT JSON_EXTRACT(@data, '$.Person.Name', '$.Person.Age', '$.Person.Hobbies[1]') AS 'Result';
11. +---------------------------+
12. | Result                    |
13. +---------------------------+
14. | ["Homer", 39, "Sleeping"] |
15. +---------------------------+
16. 1 row in set (0.07 sec)
17.  
18. mysql>
19.  

[John]

I was able to get it to work in Script BASIC using the MySQL extension module.

Code: ScriptBasic

1. IMPORT mysql.bas
2.  
3. json = """
4. {
5.    "Person": {
6.       "Name": "Homer",
7.       "Age": 39,
8.       "Hobbies": ["Eating", "Sleeping"]
9.    }
10. }
11. """
12.  
13. dbh =  mysql::RealConnect("localhost", "user", "password", "database")
14. mysql::query(dbh,"SET @data = '" & json & "'")
15. mysql::query(dbh,"SELECT JSON_EXTRACT(@data, '$.Person.Name', '$.Person.Age', '$.Person.Hobbies[1]') AS 'Result'")
16. ok = mysql::FetchHash(dbh, query_result)
17. mysql::Close(dbh)
18.  
19. PRINT query_result[0], " - ", query_result[1], "\n"
20.  

$ time scriba sqltest.sb
Result - ["Homer", 39, "Sleeping"]

real   0m0.021s
user   0m0.019s
sys   0m0.001s
$


Using wildcards can return all the data for an object. It looks like you can do a lot with json and MySQL.

Code: ScriptBasic

1. mysql::query(dbh,"SELECT JSON_EXTRACT(@data, '$.Person.*')")
2.  

$ scriba sqltest.sb
[39, "Homer", ["Eating", "Sleeping"]]
$

[John]

The MySQL json direction is going to work great for my project I'm doing for a client. The concept is to mirror the remote DB via  REST web service. It's too cool that I can have a standard table schema and update it with JSON based data. This eliminates the data conversion that would be needed using a JSON extension module.

BTW:  SQLite also supports a similar JSON interface. This direction my be more appropriate for Windows desktop applications.

[AIR]

Sorry, John, off doing the family thing for a few days...

[John]

Nothing to be sorry for. Please enjoy your holiday and time with your family.

I'm happy with the MySQL JSON direction and I'm working on a proof of concept CRUD example.

[John]

Here is an example of how PHP uses the decode JSON function to create an an object and an associative array.

Code: PHP

1. <?php
2.  $json = '
3. {
4.  "Person": {
5.   "Name": "Homer",
6.   "Age": 39,
7.   "Hobbies": ["Eating", "Sleeping"]}
8. }';
9.  
10.  var_dump(json_decode($json));
11.  var_dump(json_decode($json, true));
12. ?>
13.  
14.  

$ php testjson.php
object(stdClass)#2 (1) {
  ["Person"]=>
  object(stdClass)#1 (3) {
    ["Name"]=>
    string(5) "Homer"
    ["Age"]=>
    int(39)
    ["Hobbies"]=>
    array(2) {
      [0]=>
      string(6) "Eating"
      [1]=>
      string(8) "Sleeping"
    }
  }
}
array(1) {
  ["Person"]=>
  array(3) {
    ["Name"]=>
    string(5) "Homer"
    ["Age"]=>
    int(39)
    ["Hobbies"]=>
    array(2) {
      [0]=>
      string(6) "Eating"
      [1]=>
      string(8) "Sleeping"
    }
  }
}
$