JSON parsing

[kake26]

I dunno about the rest of you, but the JSON support in PureBasic. Well, is difficult to make any decent use of. This stopped me from working on a number of projects. I needed JSON parsing that can just be used easily and quickly. So I went to work to make something that fixed this. I ended up writing a small library that you can just include in a project and move on with. I figured I can't be the only one having a problem like this so I want to release my code for public usage. I've open sourced it and released the code just follow the link https://codeberg.org/kake26/pbjsonlib.

How it works

include file in project. Throw JSON at the parse function, this can be a string or a file. It will process the JSON into a SQLite database that will make working with the data a whole lot easier. This can deal with JSON that contains other objects and arrays.

Database layout

Below is the SQL that creates the required json_data table.

Code: Select all

CREATE TABLE "json_data" (
	"id"	INTEGER,
	"key"	TEXT,
	"parent_key"	INTEGER,
	"type"	TEXT,
	"value"	TEXT,
	PRIMARY KEY("id")
);

Please note parent_key is used for encountered objects or arrays. Key is the name of the json key, type will tell you if its a string,integer, etc. Value, well that's the value. Right now aside from the parsing you will needed to run the required querys on the data to do with as you please. I may add some additional functions soon to make that simpler as well. I'm also open to ideas and such if you have them.

[Fred]

You usually use ExtractJSONStructure() to parse complex JSON, to ease things like you do in other programming language. That said, it has some limitation about field naming, so it needs to be more flexible here (we plan to add a field mapping map)

[Quin]

You usually use ExtractJSONStructure() to parse complex JSON, to ease things like you do in other programming language. That said, it has some limitation about field naming, so it needs to be more flexible here (we plan to add a field mapping map)

And today in features I didn't know I needed but now want badly
Always coming up with amazing ideas, Fred. Look forward to this being added!

[Kiffi]

Look forward to this being added!

+1

[infratec]

@kake26

can you show an example what is not possible with build in PB JSON stuff?

[idle]

I posted the feature request ages ago and no one commented or took notice, I'm currently writing IMPBDB (in memory PB DB) which uses the feature though I'm waiting for the official solution as it's limited to X64 asm backend at the moment .
I will make a separate thread about it.

The take away point it that it's transparent to users you just work with structured pointers the memory is tracked by the DB and you don't even have to call the db to change the values if you have threads you can use atomic functions no need for murex's or calls to the DB and it you want to persist you just call a SetStructure and it's stored in the WAL. so you have the history of changes so you can do roll backs.

Code: Select all

IMPBDB_AllocateStructure(*mypt,POINT)
*mypt\x = 345                                        
*mypt\y = 456 
IMPBDB_SetStructure(*db,"mypt",*mypt,"POINT")     ;commit to disk WAL (write ahead log) 

IMPBDB_AllocateStructure(*mypt1,POINT)               ;make another point     
*mypt1\x = 345
*mypt1\y = 456 
IMPBDB_SetStructure(*db,"mypt1",*mypt1,"POINT")  ;commit to disk WAL    

*ls.IMPBDB_List = IMPBDB_Enum(*db,0,"myp")      ;prefix enumerations like select returns *mypt and *mypt1 
ForEach *ls\results() 
   Debug *ls\results()\type                                    ;has type info 
  *pt.point = *ls\results()\value                            ;cast to the type  
  Debug *pt\x 
  Debug *pt\y 
Next   
FreeStructure(*ls)                                                   ;free the list 

[kake26]

@kake26

can you show an example what is not possible with build in PB JSON stuff?

I'll attempt to explain this properly for you. I was saying the existing JSON handling capabilities where a difficult to use. I was trying to write a tool that would allow me to send and receive messages from a NTFY notification server. It uses bearer auth for restricted topics. It also responds in JSON. Some times depending on how much data is in the topic, it can be a number of json items in the form of one per line. Now to just extract the token required for post login communication was a pain to do with what purebasic offered out of the box. I admit maybe other languages have spoiled me a bit. in something like PHP this would have been simple. Let me show you. Here is a extract from what the post login response looks like form my dev setup.

Code: Select all

{"admin":{"email":"offal@pngpst.net","created":"2024-07-23 01:32:58.983Z","avatar":0,"updated":"2024-07-23 01:32:58.983Z","id":"xf9w05ts29nik6z"},"token":"eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJleHAiOjE3MzIzNDUwMjIsImlkIjoieGY5dzA1dHMyOW5pazZ6IiwidHlwZSI6ImFkbWluIn0.UM_QP4NVBdioMLU8mPJ9ZL0bq_ylLPQ6Jdn3AzNKjK0"}

the admin object, that can be ignored it won't do anything useful. Its the token I was after. That's what sparked this whole mess. Then I tried more complex JSON and then it turned into a even larger mess. That's why I made the choice to use SQLite. That way I can focus on the complexity of parsing it and later use simple SQL to grab data. Hmm, I'm not sure the above was from NTFY now that I think about it, maybe pocketbase. Regardless, I hope that goes further to explain why I cooked it up. Truth be told it really works for what I need to do and its great for JSON hijinx.

[kake26]

You usually use ExtractJSONStructure() to parse complex JSON, to ease things like you do in other programming language. That said, it has some limitation about field naming, so it needs to be more flexible here (we plan to add a field mapping map)

Thank you for mentioning that. I got so drawn into this JSON thing, that I ended up missing that. I think it might help simplify some of my code. +1 FACEPALM

[kake26]

I posted the feature request ages ago and no one commented or took notice, I'm currently writing IMPBDB (in memory PB DB) which uses the feature though I'm waiting for the official solution as it's limited to X64 asm backend at the moment .
I will make a separate thread about it.

The take away point it that it's transparent to users you just work with structured pointers the memory is tracked by the DB and you don't even have to call the db to change the values if you have threads you can use atomic functions no need for murex's or calls to the DB and it you want to persist you just call a SetStructure and it's stored in the WAL. so you have the history of changes so you can do roll backs.

Code: Select all

IMPBDB_AllocateStructure(*mypt,POINT)
*mypt\x = 345                                        
*mypt\y = 456 
IMPBDB_SetStructure(*db,"mypt",*mypt,"POINT")     ;commit to disk WAL (write ahead log) 

IMPBDB_AllocateStructure(*mypt1,POINT)               ;make another point     
*mypt1\x = 345
*mypt1\y = 456 
IMPBDB_SetStructure(*db,"mypt1",*mypt1,"POINT")  ;commit to disk WAL    

*ls.IMPBDB_List = IMPBDB_Enum(*db,0,"myp")      ;prefix enumerations like select returns *mypt and *mypt1 
ForEach *ls\results() 
   Debug *ls\results()\type                                    ;has type info 
  *pt.point = *ls\results()\value                            ;cast to the type  
  Debug *pt\x 
  Debug *pt\y 
Next   
FreeStructure(*ls)                                                   ;free the list 

Sounds like we sort of fell down the same rabbit hole. I was bashing my head against the wall and this was just what seemed like a good idea at the time. I'm using it in allot of other stuff and it works pretty well for me. I'll look for your post I'd be interested to see what you did.

[idle]

I posted the feature request ages ago and no one commented or took notice, I'm currently writing IMPBDB (in memory PB DB) which uses the feature though I'm waiting for the official solution as it's limited to X64 asm backend at the moment .
I will make a separate thread about it.

The take away point it that it's transparent to users you just work with structured pointers the memory is tracked by the DB and you don't even have to call the db to change the values if you have threads you can use atomic functions no need for murex's or calls to the DB and it you want to persist you just call a SetStructure and it's stored in the WAL. so you have the history of changes so you can do roll backs.

Code: Select all

IMPBDB_AllocateStructure(*mypt,POINT)
*mypt\x = 345                                        
*mypt\y = 456 
IMPBDB_SetStructure(*db,"mypt",*mypt,"POINT")     ;commit to disk WAL (write ahead log) 

IMPBDB_AllocateStructure(*mypt1,POINT)               ;make another point     
*mypt1\x = 345
*mypt1\y = 456 
IMPBDB_SetStructure(*db,"mypt1",*mypt1,"POINT")  ;commit to disk WAL    

*ls.IMPBDB_List = IMPBDB_Enum(*db,0,"myp")      ;prefix enumerations like select returns *mypt and *mypt1 
ForEach *ls\results() 
   Debug *ls\results()\type                                    ;has type info 
  *pt.point = *ls\results()\value                            ;cast to the type  
  Debug *pt\x 
  Debug *pt\y 
Next   
FreeStructure(*ls)                                                   ;free the list 

Sounds like we sort of fell down the same rabbit hole. I was bashing my head against the wall and this was just what seemed like a good idea at the time. I'm using it in allot of other stuff and it works pretty well for me. I'll look for your post I'd be interested to see what you did.

Yes similar and I had considered using sqlite as well but opted for a simple WAL

[infratec]

No problem with PB:

Code: Select all

Structure email_Structure
  email.s
  created.s
  avatar.i
  updated.s
  id.s
EndStructure

Structure Test_Structure
  admin.email_Structure
  token.s
EndStructure


Define Test.Test_Structure


JSON$ = ~"{\"admin\":{\"email\":\"offal@pngpst.net\",\"created\":\"2024-07-23 01:32:58.983Z\",\"avatar\":0,\"updated\":\"2024-07-23 01:32:58.983Z\",\"id\":\"xf9w05ts29nik6z\"},\"token\":\"eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJleHAiOjE3MzIzNDUwMjIsImlkIjoieGY5dzA1dHMyOW5pazZ6IiwidHlwZSI6ImFkbWluIn0.UM_QP4NVBdioMLU8mPJ9ZL0bq_ylLPQ6Jdn3AzNKjK0\"}"

JSON = ParseJSON(#PB_Any, JSON$)
If JSON  
  Debug ComposeJSON(JSON, #PB_JSON_PrettyPrint)
  
  ExtractJSONStructure(JSONValue(JSON), @Test, Test_Structure)
  
  Debug ""
  
  Debug Test\admin\email
  Debug Test\admin\created
  Debug Test\admin\updated
  Debug Test\admin\id
  Debug Test\admin\avatar
  
  Debug Test\token
  
  FreeJSON(JSON)
EndIf

[kenmo]

Now to just extract the token required for post login communication was a pain to do with what purebasic offered out of the box. I admit maybe other languages have spoiled me a bit. in something like PHP this would have been simple. Let me show you. Here is a extract from what the post login response looks like form my dev setup.

I rarely use the Insert/Extract JSON Structure approach. For my various JSON needs, my approach is lots and lots of helper functions
You may be interested in my JSON_Helper.pbi includefile: https://github.com/kenmo-pb/includes/bl ... Helper.pbi

For example, your "token" example could just become:

Code: Select all

XIncludeFile "Includes/JSON_Helper.pbi"

JSONText.s = ReplaceString("{`admin`:{`email`:`offal@pngpst.net`,`created`:`2024-07-23 01:32:58.983Z`,`avatar`:0,`updated`:`2024-07-23 01:32:58.983Z`,`id`:`xf9w05ts29nik6z`},`token`:`eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJleHAiOjE3MzIzNDUwMjIsImlkIjoieGY5dzA1dHMyOW5pazZ6IiwidHlwZSI6ImFkbWluIn0.UM_QP4NVBdioMLU8mPJ9ZL0bq_ylLPQ6Jdn3AzNKjK0`}", "`", #DQUOTE$)
ParseJSON(0, JSONText)

Token.s = JSONStringFromPath( MainJSONObject(0), "token" )
Debug Token

[kake26]

No problem with PB:

Code: Select all

Structure email_Structure
  email.s
  created.s
  avatar.i
  updated.s
  id.s
EndStructure

Structure Test_Structure
  admin.email_Structure
  token.s
EndStructure


Define Test.Test_Structure


JSON$ = ~"{\"admin\":{\"email\":\"offal@pngpst.net\",\"created\":\"2024-07-23 01:32:58.983Z\",\"avatar\":0,\"updated\":\"2024-07-23 01:32:58.983Z\",\"id\":\"xf9w05ts29nik6z\"},\"token\":\"eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJleHAiOjE3MzIzNDUwMjIsImlkIjoieGY5dzA1dHMyOW5pazZ6IiwidHlwZSI6ImFkbWluIn0.UM_QP4NVBdioMLU8mPJ9ZL0bq_ylLPQ6Jdn3AzNKjK0\"}"

JSON = ParseJSON(#PB_Any, JSON$)
If JSON  
  Debug ComposeJSON(JSON, #PB_JSON_PrettyPrint)
  
  ExtractJSONStructure(JSONValue(JSON), @Test, Test_Structure)
  
  Debug ""
  
  Debug Test\admin\email
  Debug Test\admin\created
  Debug Test\admin\updated
  Debug Test\admin\id
  Debug Test\admin\avatar
  
  Debug Test\token
  
  FreeJSON(JSON)
EndIf

Well, um now I feel a little silly. Its clear I missed ExtractJSONStructure and its use entirely. Thank you for showing me that. At the very least I can update the parser with that. I will still by choice keep SQLite as part of what my library does as its proven pretty useful for me at least.