Page 1 of 1
Extract JSON to multiple structures
Posted: Thu Oct 12, 2023 12:32 am
by 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
Re: Extract JSON to multiple structures
Posted: Thu Oct 12, 2023 12:57 am
by 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
Re: Extract JSON to multiple structures
Posted: Thu Oct 12, 2023 4:01 am
by Jagermeister
Thanks! I was pondering on this line in the manual from ExtractJSONStructure:
The extraction is performed recursively if the structure contains further structures
Re: Extract JSON to multiple structures
Posted: Thu Oct 12, 2023 7:11 am
by 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.
