Extract JSON to multiple structures

[Jagermeister]

Hello,

I know I can mash the structures into one, but anyone know how to go about extracting JSON array data to designated structures?

Code: Select all

Enumeration
  #json
EndEnumeration

Structure struc_pers
  key.s
  name.s
EndStructure

Define pers.struc_pers

Structure struc_attrib
  key.s
  active.s
  date.s
EndStructure

Define attrib.struc_attrib

string$ = ~"{\"pers\":[{\"key\":\"101\",\"name\":\"James\"}],\"attrib\":[{\"key\":\"101\",\"active\":\"Y\",\"date\":\"20231010\"}]}"

Debug string$
If ParseJSON(#json, string$)
  Debug ComposeJSON(#json , #PB_JSON_PrettyPrint)
  
  ; ExtractJSONStructure? How to direct to multiple structures?
  
  Debug pers\key
  Debug pers\name
  Debug attrib\key
  Debug attrib\active
  Debug attrib\date
Else
  Debug JSONErrorMessage()
  Debug JSONErrorLine()
  Debug JSONErrorPosition()
EndIf 

[StarBootics]

Hello,

I can be wrong but you can't do this with ExtractJSONStructure() in that particular case. You have to do it the hard way using the other instructions form the JSON libraries. Like that :

Code: Select all

Enumeration
  #json
EndEnumeration

Structure struc_pers
  key.s
  name.s
EndStructure

Define pers.struc_pers

Structure struc_attrib
  key.s
  active.s
  date.s
EndStructure

Define attrib.struc_attrib

Procedure ExtractPerson(*pers.struc_pers, JSONValue.i)
  
  If ExamineJSONMembers(JSONValue)
    
    While NextJSONMember(JSONValue)
      
      Select JSONMemberKey(JSONValue)
          
        Case "key"
          *pers\key = GetJSONString(JSONMemberValue(JSONValue))
          
        Case "name"
          *pers\name = GetJSONString(JSONMemberValue(JSONValue))
          
      EndSelect
      
    Wend
    
  EndIf
  
EndProcedure

Procedure ExtractAttrib(*attrib.struc_attrib, JSONValue.i)
  
  If ExamineJSONMembers(JSONValue)
    
    While NextJSONMember(JSONValue)
      
      Select JSONMemberKey(JSONValue)
          
        Case "key"
          *attrib\key = GetJSONString(JSONMemberValue(JSONValue))
          
        Case "active"
          *attrib\active = GetJSONString(JSONMemberValue(JSONValue))
          
        Case "date"
          *attrib\date = GetJSONString(JSONMemberValue(JSONValue))
          
      EndSelect
      
    Wend
    
  EndIf
  
EndProcedure



string$ = ~"{\"pers\":[{\"key\":\"101\",\"name\":\"James\"}],\"attrib\":[{\"key\":\"101\",\"active\":\"Y\",\"date\":\"20231010\"}]}"

Debug string$

If ParseJSON(#json, string$)
  
  Debug ComposeJSON(#json , #PB_JSON_PrettyPrint)
  SaveJSON(#json, "Drop2.json", #PB_JSON_PrettyPrint)
  
  If IsJSON(#json)
    
    JSONValue.i = JSONValue(#json)
    
    If ExamineJSONMembers(JSONValue)
      
      While NextJSONMember(JSONValue)
        
        Select JSONMemberKey(JSONValue)
            
          Case "pers"
            perstable.i = GetJSONMember(JSONValue, "pers")
            ExtractPerson(pers.struc_pers, GetJSONElement(perstable, 0))
            
          Case "attrib"
            attribtable.i = GetJSONMember(JSONValue, "attrib")
            ExtractAttrib(attrib.struc_attrib, GetJSONElement(attribtable, 0))
            
        EndSelect
        
      Wend
      
    Else
      Debug "Can't examine JSON"
    EndIf
    
    FreeJSON(#json)
  Else
    Debug "ERROR !"
  EndIf
  
Else
  Debug "Parse JSON failed !"
  Debug JSONErrorMessage()
  Debug JSONErrorLine()
  Debug JSONErrorPosition()
EndIf 

Debug "properties"

Debug pers\key
Debug pers\name
Debug attrib\key
Debug attrib\active
Debug attrib\date

Best regards
StarBootics

[Jagermeister]

Thanks! I was pondering on this line in the manual from ExtractJSONStructure:

The extraction is performed recursively if the structure contains further structures

[Jagermeister]

Eureka! Found what I was looking for. Adapted from Paul's post here: https://forums.spiderbasic.com/viewtopi ... ture#p8378

Source:

Code: Select all

Enumeration
  #json
EndEnumeration

Structure struc_pers
  key.s
  name.s
EndStructure

Structure struc_attrib
  key.s
  active.s
  date.s
EndStructure

Structure struc_staff
  List pers.struc_pers()
  List attrib.struc_attrib()
EndStructure

Define company.struc_staff

string$ = ~"{\"pers\":[{\"key\":\"101\",\"name\":\"James\"}],\"attrib\":[{\"key\":\"101\",\"active\":\"Y\",\"date\":\"20231010\"}]}"

Debug string$
Debug "====================================================="
If ParseJSON(#json, string$)
  Debug ComposeJSON(#json , #PB_JSON_PrettyPrint)
  ExtractJSONStructure(JSONValue(#json) , @company.struc_staff , struc_staff)
  
  Debug "====================================================="
  Debug "--------------"
  Debug "Personnel"
  Debug "--------------"
  Debug "Key: " + company\pers()\key
  Debug "Name: " + company\pers()\name
  Debug "--------------"
  Debug "Attributes"
  Debug "--------------"
  Debug "Key: " + company\attrib()\key
  Debug "Active: " + company\attrib()\active
  Debug "Date: " + company\attrib()\date
Else
  Debug JSONErrorMessage()
  Debug JSONErrorLine()
  Debug JSONErrorPosition()
EndIf 

Output:

Code: Select all

{"pers":[{"key":"101","name":"James"}],"attrib":[{"key":"101","active":"Y","date":"20231010"}]}
=====================================================
{
	"pers": [
		{
			"key": "101",
			"name": "James"
		}
	],
	"attrib": [
		{
			"key": "101",
			"active": "Y",
			"date": "20231010"
		}
	]
}
=====================================================
--------------
Personnel
--------------
Key: 101
Name: James
--------------
Attributes
--------------
Key: 101
Active: Y
Date: 20231010

The goal is to receive SQL query results from several tables (not a JOIN query scenario) in a single JSON string and pour it into structures that reflect database table names and field/column names. Hope it helps in someone's next SpiderBasic project.