How to extract JSONObjects from a JSON Array?

Just starting out? Need help? Post your questions and find answers here.
swhite
Enthusiast
Enthusiast
Posts: 726
Joined: Thu May 21, 2009 6:56 pm

How to extract JSONObjects from a JSON Array?

Post by swhite »

Hi

When I check the JSONType() after parsing the data below with ParseJson() I find that the JSON type is array which makes sense. However, I cannot find a method to extract the JSON objects from this array. ExportJSONArray() does not work because you cannot define an array of type JsonObject and neither string or integer works.

I can use StringField() to get each string and then use ParseJSON() on each string to get a JSONObject(). However is there a method to extract this data without using Stringfield()? I would assume that it should all be able to be done with JSON functions.

Code: Select all

   lcJSON=~"[{\"date\":\"20201103\",\"exchtous\":0.73456,\"price\":1.111,\"pricecd\":1.112,\"pricefs\":1.115,\"product\":\"clrdsl\",\"time\":\"00:00\"},"+
   ~"{\"date\":\"20201103\",\"exchtous\":0.73456,\"price\":2.111,\"pricecd\":2.112,\"pricefs\":2.115,\"product\":\"reggas\",\"time\":\"00:00\"},"+
   ~"{\"date\":\"20201103\",\"exchtous\":0.73456,\"price\":3.111,\"pricecd\":3.112,\"pricefs\":3.115,\"product\":\"dyedsl\",\"time\":\"00:00\"}]"
   
   lcJSON = RemoveString(RemoveString(lcJSON,"["),"]")
   lnCnt = CountString(lcJSON,"}")
   For ln = 1 To lnCnt
      lcTxt.s = StringField(lcJSON,ln,"}")+"}"
      If Left(lcTxt,1)=","
         lcTxt = Mid(lcTxt,2)
      EndIf
      Debug lcTxt
      loJSON = ParseJSON(#PB_Any,lcTxt)
      Debug JSONErrorMessage()
      lnJVal =JSONValue(loJSON)
      If ExamineJSONMembers(lnJVal)
         While NextJSONMember(lnJVal)
            lnVal.i = JSONMemberValue(lnJVal)
            Select JSONType(lnVal)
               Case #PB_JSON_Null:    Debug JSONMemberKey(lnJVal)+" null"
               Case #PB_JSON_String:  Debug JSONMemberKey(lnJVal)+" "+GetJSONString(lnVal)
               Case #PB_JSON_Number:  Debug JSONMemberKey(lnJVal)+" "+StrD(GetJSONDouble(lnVal))    
               Case #PB_JSON_Boolean: Debug JSONMemberKey(lnJVal)+" "+Str(GetJSONBoolean(lnVal))
               Case #PB_JSON_Array:   Debug JSONMemberKey(lnJVal)+" array"
               Case #PB_JSON_Object:  Debug JSONMemberKey(lnJVal)+" object"
            EndSelect
         Wend
      EndIf
      
   Next
Simon White
dCipher Computing
infratec
Always Here
Always Here
Posts: 6817
Joined: Sun Sep 07, 2008 12:45 pm
Location: Germany

Re: How to extract JSONObjects from a JSON Array?

Post by infratec »

Let PB do this stuff for you:

Code: Select all

EnableExplicit

Structure lcJSONStructure
  date.s
  exchtous.f
  price.f
  pricecd.f
  pricefs.f
  product.s
  time.s
EndStructure

Structure lcJSONListStructure
  List lcJSONList.lcJSONStructure()
EndStructure

Define lcJSON$
Define loJSON.i
Define lcJSON.lcJSONListStructure

lcJSON$ = ~"[{\"date\":\"20201103\",\"exchtous\":0.73456,\"price\":1.111,\"pricecd\":1.112,\"pricefs\":1.115,\"product\":\"clrdsl\",\"time\":\"00:00\"},"+
   ~"{\"date\":\"20201103\",\"exchtous\":0.73456,\"price\":2.111,\"pricecd\":2.112,\"pricefs\":2.115,\"product\":\"reggas\",\"time\":\"00:00\"},"+
   ~"{\"date\":\"20201103\",\"exchtous\":0.73456,\"price\":3.111,\"pricecd\":3.112,\"pricefs\":3.115,\"product\":\"dyedsl\",\"time\":\"00:00\"}]"
   
lcJSON$ = ~"{\"lcJSONList\":" + lcJSON$ + "}"

Debug lcJSON$

loJSON = ParseJSON(#PB_Any, lcJSON$)
If loJSON
  ExtractJSONStructure(JSONValue(loJSON), @lcJSON, lcJSONListStructure)
  FreeJSON(loJSON)
  
  ForEach lcJSON\lcJSONList()
    Debug lcJSON\lcJSONList()\product + " " + StrF(lcJSON\lcJSONList()\price, 3)
  Next
EndIf
swhite
Enthusiast
Enthusiast
Posts: 726
Joined: Thu May 21, 2009 6:56 pm

Re: How to extract JSONObjects from a JSON Array?

Post by swhite »

Hi

Thank-you for your suggestion but I cannot really use the solution you suggested because the data is coming from a database and the structure can change so I need a general purpose method to handle JSON data when I do not know the structure. To that end I use a Map of structures where key is the field name. The data is either double, integer or string. The record set map uses the unique ID field as the map key.

Code: Select all

; Database Field Structure
Structure dBValue
   StructureUnion
      ValueD.d
      ValueI.i
   EndStructureUnion 
   ValueS.s
   Type.s[1]
   Chngd.a
EndStructure

; Record Set Structure
Structure RecordSet
   Map Fld.dBValue()  
EndStructure
Simon White
dCipher Computing
User avatar
skywalk
Addict
Addict
Posts: 3972
Joined: Wed Dec 23, 2009 10:14 pm
Location: Boston, MA

Re: How to extract JSONObjects from a JSON Array?

Post by skywalk »

If the data is coming from a database, maybe you know the schema?
It is no biggie to have several Structures defined for all cases.
There really can't be that many.
The JSON<->PB direct method is clean and worth the effort.
The nice thing about standards is there are so many to choose from. ~ Andrew Tanenbaum
User avatar
kenmo
Addict
Addict
Posts: 1967
Joined: Tue Dec 23, 2003 3:54 am

Re: How to extract JSONObjects from a JSON Array?

Post by kenmo »

Hi swhite,

I traverse JSON all the time, and usually without the extract-structure techniques popular on the forum.

I don't understand your question though. If you have a known *Array, isn't it as simple as...

Code: Select all

*Object = GetJSONElement(*Array, Index)
If not, maybe check out my JSON Helper functions which offer many simplified ways to traverse JSON and extract data:
https://raw.githubusercontent.com/kenmo ... Helper.pbi
GPI
PureBasic Expert
PureBasic Expert
Posts: 1394
Joined: Fri Apr 25, 2003 6:41 pm

Re: How to extract JSONObjects from a JSON Array?

Post by GPI »

Code: Select all

Procedure DebugJson(injVal,deep=0)
  Select JSONType(injVal)
    Case #PB_JSON_Null:    Debug Space(deep*2)+Hex(injVal)+" null"
    Case #PB_JSON_String:  Debug Space(deep*2)+Hex(injVal)+" string "+GetJSONString(injVal)
    Case #PB_JSON_Number:  Debug Space(deep*2)+Hex(injVal)+" number "+StrD(GetJSONDouble(injVal))    
    Case #PB_JSON_Boolean: Debug Space(deep*2)+Hex(injVal)+" boolean "+Str(GetJSONBoolean(injVal))
    Case #PB_JSON_Array:   Debug Space(deep*2)+Hex(injVal)+" array"
      size = JSONArraySize(injVal)
      For i=0 To size-1
        DebugJson(GetJSONElement(injVal,i),deep+1)
      Next                 
      
    Case #PB_JSON_Object:  Debug Space(deep*2)+Hex(injVal)+" object"
      If ExamineJSONMembers(injVal)
        While NextJSONMember(injVal)
          Debug Space((deep+1)*2)+JSONMemberKey(injVal) 
          DebugJson(JSONMemberValue(injVal),deep+2)
        Wend
      EndIf
      
      
      
    Default: Debug "UNKNWON"
      
  EndSelect
EndProcedure


lcJSON.s=~"[{\"date\":\"20201103\",\"exchtous\":0.73456,\"price\":1.111,\"pricecd\":1.112,\"pricefs\":1.115,\"product\":\"clrdsl\",\"time\":\"00:00\"},"+
         ~"{\"date\":\"20201103\",\"exchtous\":0.73456,\"price\":2.111,\"pricecd\":2.112,\"pricefs\":2.115,\"product\":\"reggas\",\"time\":\"00:00\"},"+
         ~"{\"date\":\"20201103\",\"exchtous\":0.73456,\"price\":3.111,\"pricecd\":3.112,\"pricefs\":3.115,\"product\":\"dyedsl\",\"time\":\"00:00\"}]"


json = ParseJSON(#PB_Any,lcJSON)

JSONValue = JSONValue(Json)

Debug DebugJson(JSONValue)

FreeJSON( json )
swhite
Enthusiast
Enthusiast
Posts: 726
Joined: Thu May 21, 2009 6:56 pm

Re: How to extract JSONObjects from a JSON Array?

Post by swhite »

Hi

I believe I tried using the GETJSONElement() but clearly I did not do it correctly so thank-you for pointing this out. It always helps to have another pair of eyes look at what you are doing.

Simon
kenmo wrote: Tue Jul 20, 2021 12:14 am Hi swhite,

I traverse JSON all the time, and usually without the extract-structure techniques popular on the forum.

I don't understand your question though. If you have a known *Array, isn't it as simple as...

Code: Select all

*Object = GetJSONElement(*Array, Index)
If not, maybe check out my JSON Helper functions which offer many simplified ways to traverse JSON and extract data:
https://raw.githubusercontent.com/kenmo ... Helper.pbi
Simon White
dCipher Computing
Post Reply