Visual LANSA Forum

Community Forum for Visual LANSA
https://forum.developer.lansa.com/

Reading a large set of JSON data

https://forum.developer.lansa.com/viewtopic.php?t=2302

Page 1 of 1

Reading a large set of JSON data

Posted: Mon Mar 16, 2020 11:54 pm
by jan
Hi Everyone,

I asked a question previously on how to efficiently read JSON data which @Marcus Lancaster was able to provide a solution for.

Now, our team is working on a large set of data with about 500 entries returned and a kind of complicated structure (see a trimmed down version below).

Code: Select all

{
  "responseHeader":{
    "status":0,
    "QTime":9,
    "params":{
      "facet.ItemFamily":"",
      "q":"(ball) + ItemCatLevel1:Valves & Actuation",
      "defType":"edismax",
      "facet.field":["ItemSize",
        "ItemMaterial",
        "ItemEnd"],
      "qf":"ItemDesc^6 ItemCode^5 ItemCatLevel1^4 ItemCatLevel2^3 ItemCatLevel3^2 ItemCatLevel4^1",
      "start":"0",
      "facet.mincount":"1",
      "rows":"500",
      "facet":"on"}},
  "response":{"numFound":5324,"start":0,
    "docs":[
      {
        "StoreFrontID":9,
        "ItemCode":["1210005"],
        "ItemCatLevel1":["Ball Check"],
        "ItemCatLevel2":["Check Valves"],
        "ItemCatLevel3":["Manual Valves"],
        "ItemCatLevel4":["Valves & Actuations"],
        "ItemDesc":["1/2\" PVC EPDM Ball Check Socket and Threaded"],
        "ItemSize":4,
        "ItemSizeFilterSeq":["SIZ"],
        "ItemSizeId":["1/2\""],
        "ItemSizeSeq":290,
        "ItemSizeType":20,
        "ItemMaterial":57,
        "ItemMaterialFilterSeq":["MAT"],
        "ItemMaterialId":["PVC"],
        "ItemMaterialSeq":30,
        "ItemMaterialType":30,
        "ItemEnd":0,
        "ItemEndFilterSeq":[" "],
        "ItemEndId":[" "],
        "ItemEndType":0,
        "ItemFamily":0,
        "ItemFamilyFilterSeq":[" "],
        "ItemFamilyId":[" "],
        "ItemFamilySeq":0,
        "ItemFamilyType":0,
        "ItemElastic":0,
        "ItemElasticFilterSeq":[" "],
        "ItemElasticId":[" "],
        "ItemElasticSeq":0,
        "ItemElasticType":0,
        "ItemSdr":0,
        "ItemSdrFilterSeq":[" "],
        "ItemSdrId":[" "],
        "ItemSdrSeq":0,
        "ItemSdrType":0,
        "ItemImage":[" "],
        "id":"0dec9e6d-7dc7-4cf7-8c51-e2accc2ea95b",
        "_version_":1661042533061361706},
      {
        "StoreFrontID":9,
        "ItemCode":["1210007"],
        "ItemCatLevel1":["Ball Check"],
        "ItemCatLevel2":["Check Valves"],
        "ItemCatLevel3":["Manual Valves"],
        "ItemCatLevel4":["Valves & Actuations"],
        "ItemDesc":["3/4\" PVC EPDM Ball Check Socket and Threaded"],
        "ItemSize":4,
        "ItemSizeFilterSeq":["SIZ"],
        "ItemSizeId":["3/4\""],
        "ItemSizeSeq":370,
        "ItemSizeType":20,
        "ItemMaterial":57,
        "ItemMaterialFilterSeq":["MAT"],
        "ItemMaterialId":["PVC"],
        "ItemMaterialSeq":30,
        "ItemMaterialType":30,
        "ItemEnd":0,
        "ItemEndFilterSeq":[" "],
        "ItemEndId":[" "],
        "ItemEndType":0,
        "ItemFamily":0,
        "ItemFamilyFilterSeq":[" "],
        "ItemFamilyId":[" "],
        "ItemFamilySeq":0,
        "ItemFamilyType":0,
        "ItemElastic":0,
        "ItemElasticFilterSeq":[" "],
        "ItemElasticId":[" "],
        "ItemElasticSeq":0,
        "ItemElasticType":0,
        "ItemSdr":0,
        "ItemSdrFilterSeq":[" "],
        "ItemSdrId":[" "],
        "ItemSdrSeq":0,
        "ItemSdrType":0,
        "ItemImage":[" "],
        "id":"de315113-7e94-46db-82bc-5124f7f0b343",
        "_version_":1661042533061361707},
      {
        "StoreFrontID":9,
        "ItemCode":["1210010"],
        "ItemCatLevel1":["Ball Check"],
        "ItemCatLevel2":["Check Valves"],
        "ItemCatLevel3":["Manual Valves"],
        "ItemCatLevel4":["Valves & Actuations"],
        "ItemDesc":["1\" PVC EPDM Ball Check Socket and Threaded"],
        "ItemSize":4,
        "ItemSizeFilterSeq":["SIZ"],
        "ItemSizeId":["1\""],
        "ItemSizeSeq":650,
        "ItemSizeType":20,
        "ItemMaterial":57,
        "ItemMaterialFilterSeq":["MAT"],
        "ItemMaterialId":["PVC"],
        "ItemMaterialSeq":30,
        "ItemMaterialType":30,
        "ItemEnd":0,
        "ItemEndFilterSeq":[" "],
        "ItemEndId":[" "],
        "ItemEndType":0,
        "ItemFamily":0,
        "ItemFamilyFilterSeq":[" "],
        "ItemFamilyId":[" "],
        "ItemFamilySeq":0,
        "ItemFamilyType":0,
        "ItemElastic":0,
        "ItemElasticFilterSeq":[" "],
        "ItemElasticId":[" "],
        "ItemElasticSeq":0,
        "ItemElasticType":0,
        "ItemSdr":0,
        "ItemSdrFilterSeq":[" "],
        "ItemSdrId":[" "],
        "ItemSdrSeq":0,
        "ItemSdrType":0,
        "ItemImage":[" "],
        "id":"f1c8faad-d2d7-492f-9847-db2fe10e6d38",
        "_version_":1661042533061361708},
      {
        "StoreFrontID":9,
        "ItemCode":["1210012"],
        "ItemCatLevel1":["Ball Check"],
        "ItemCatLevel2":["Check Valves"],
        "ItemCatLevel3":["Manual Valves"],
        "ItemCatLevel4":["Valves & Actuations"],
        "ItemDesc":["1�1/4 PVC EPDM Ball Check Socket Only"],
        "ItemSize":4,
        "ItemSizeFilterSeq":["SIZ"],
        "ItemSizeId":["1�1/4"],
        "ItemSizeSeq":20,
        "ItemSizeType":20,
        "ItemMaterial":57,
        "ItemMaterialFilterSeq":["MAT"],
        "ItemMaterialId":["PVC"],
        "ItemMaterialSeq":30,
        "ItemMaterialType":30,
        "ItemEnd":0,
        "ItemEndFilterSeq":[" "],
        "ItemEndId":[" "],
        "ItemEndType":0,
        "ItemFamily":0,
        "ItemFamilyFilterSeq":[" "],
        "ItemFamilyId":[" "],
        "ItemFamilySeq":0,
        "ItemFamilyType":0,
        "ItemElastic":0,
        "ItemElasticFilterSeq":[" "],
        "ItemElasticId":[" "],
        "ItemElasticSeq":0,
        "ItemElasticType":0,
        "ItemSdr":0,
        "ItemSdrFilterSeq":[" "],
        "ItemSdrId":[" "],
        "ItemSdrSeq":0,
        "ItemSdrType":0,
        "ItemImage":[" "],
        "id":"c979cd3b-02d6-462e-a6ad-8976a1885f8f",
        "_version_":1661042533061361709},
      {
        "StoreFrontID":9,
        "ItemCode":["1210015"],
        "ItemCatLevel1":["Ball Check"],
        "ItemCatLevel2":["Check Valves"],
        "ItemCatLevel3":["Manual Valves"],
        "ItemCatLevel4":["Valves & Actuations"],
        "ItemDesc":["1�1/2 PVC EPDM Ball Check Socket and Threaded"],
        "ItemSize":4,
        "ItemSizeFilterSeq":["SIZ"],
        "ItemSizeId":["1�1/2"],
        "ItemSizeSeq":10,
        "ItemSizeType":20,
        "ItemMaterial":57,
        "ItemMaterialFilterSeq":["MAT"],
        "ItemMaterialId":["PVC"],
        "ItemMaterialSeq":30,
        "ItemMaterialType":30,
        "ItemEnd":0,
        "ItemEndFilterSeq":[" "],
        "ItemEndId":[" "],
        "ItemEndType":0,
        "ItemFamily":0,
        "ItemFamilyFilterSeq":[" "],
        "ItemFamilyId":[" "],
        "ItemFamilySeq":0,
        "ItemFamilyType":0,
        "ItemElastic":0,
        "ItemElasticFilterSeq":[" "],
        "ItemElasticId":[" "],
        "ItemElasticSeq":0,
        "ItemElasticType":0,
        "ItemSdr":0,
        "ItemSdrFilterSeq":[" "],
        "ItemSdrId":[" "],
        "ItemSdrSeq":0,
        "ItemSdrType":0,
        "ItemImage":[" "],
        "id":"c7ad6d03-d0ba-4e92-a7c6-d90af96d2940",
        "_version_":1661042533077090304},
      {
        "StoreFrontID":9,
        "ItemCode":["1210020"],
        "ItemCatLevel1":["Ball Check"],
        "ItemCatLevel2":["Check Valves"],
        "ItemCatLevel3":["Manual Valves"],
        "ItemCatLevel4":["Valves & Actuations"],
        "ItemDesc":["2\" PVC EPDM Ball Check Socket and Threaded"],
        "ItemSize":4,
        "ItemSizeFilterSeq":["SIZ"],
        "ItemSizeId":["2\""],
        "ItemSizeSeq":1340,
        "ItemSizeType":20,
        "ItemMaterial":57,
        "ItemMaterialFilterSeq":["MAT"],
        "ItemMaterialId":["PVC"],
        "ItemMaterialSeq":30,
        "ItemMaterialType":30,
        "ItemEnd":0,
        "ItemEndFilterSeq":[" "],
        "ItemEndId":[" "],
        "ItemEndType":0,
        "ItemFamily":0,
        "ItemFamilyFilterSeq":[" "],
        "ItemFamilyId":[" "],
        "ItemFamilySeq":0,
        "ItemFamilyType":0,
        "ItemElastic":0,
        "ItemElasticFilterSeq":[" "],
        "ItemElasticId":[" "],
        "ItemElasticSeq":0,
        "ItemElasticType":0,
        "ItemSdr":0,
        "ItemSdrFilterSeq":[" "],
        "ItemSdrId":[" "],
        "ItemSdrSeq":0,
        "ItemSdrType":0,
        "ItemImage":[" "],
        "id":"024ee48f-cb51-4e48-bacf-3eb5cbbe2fdc",
        "_version_":1661042533077090305},
      {
        "StoreFrontID":9,
        "ItemCode":["1210030"],
        "ItemCatLevel1":["Ball Check"],
        "ItemCatLevel2":["Check Valves"],
        "ItemCatLevel3":["Manual Valves"],
        "ItemCatLevel4":["Valves & Actuations"],
        "ItemDesc":["3\" PVC EPDM Ball Check Socket"],
        "ItemSize":4,
        "ItemSizeFilterSeq":["SIZ"],
        "ItemSizeId":["3\""],
        "ItemSizeSeq":1860,
        "ItemSizeType":20,
        "ItemMaterial":57,
        "ItemMaterialFilterSeq":["MAT"],
        "ItemMaterialId":["PVC"],
        "ItemMaterialSeq":30,
        "ItemMaterialType":30,
        "ItemEnd":0,
        "ItemEndFilterSeq":[" "],
        "ItemEndId":[" "],
        "ItemEndType":0,
        "ItemFamily":0,
        "ItemFamilyFilterSeq":[" "],
        "ItemFamilyId":[" "],
        "ItemFamilySeq":0,
        "ItemFamilyType":0,
        "ItemElastic":0,
        "ItemElasticFilterSeq":[" "],
        "ItemElasticId":[" "],
        "ItemElasticSeq":0,
        "ItemElasticType":0,
        "ItemSdr":0,
        "ItemSdrFilterSeq":[" "],
        "ItemSdrId":[" "],
        "ItemSdrSeq":0,
        "ItemSdrType":0,
        "ItemImage":[" "],
        "id":"6d786ac4-cc85-4409-8637-1c4b31ab92a1",
        "_version_":1661042533077090306},
      {
        "StoreFrontID":9,
        "ItemCode":["1210040"],
        "ItemCatLevel1":["Ball Check"],
        "ItemCatLevel2":["Check Valves"],
        "ItemCatLevel3":["Manual Valves"],
        "ItemCatLevel4":["Valves & Actuations"],
        "ItemDesc":["4\" PVC EPDM Ball Check Socket"],
        "ItemSize":4,
        "ItemSizeFilterSeq":["SIZ"],
        "ItemSizeId":["4\""],
        "ItemSizeSeq":2100,
        "ItemSizeType":20,
        "ItemMaterial":57,
        "ItemMaterialFilterSeq":["MAT"],
        "ItemMaterialId":["PVC"],
        "ItemMaterialSeq":30,
        "ItemMaterialType":30,
        "ItemEnd":0,
        "ItemEndFilterSeq":[" "],
        "ItemEndId":[" "],
        "ItemEndType":0,
        "ItemFamily":0,
        "ItemFamilyFilterSeq":[" "],
        "ItemFamilyId":[" "],
        "ItemFamilySeq":0,
        "ItemFamilyType":0,
        "ItemElastic":0,
        "ItemElasticFilterSeq":[" "],
        "ItemElasticId":[" "],
        "ItemElasticSeq":0,
        "ItemElasticType":0,
        "ItemSdr":0,
        "ItemSdrFilterSeq":[" "],
        "ItemSdrId":[" "],
        "ItemSdrSeq":0,
        "ItemSdrType":0,
        "ItemImage":[" "],
        "id":"e4d8856b-7f85-4688-8a53-5592d59f0847",
        "_version_":1661042533077090307},
      {
        "StoreFrontID":9,
        "ItemCode":["1211030"],
        "ItemCatLevel1":["Ball Check"],
        "ItemCatLevel2":["Check Valves"],
        "ItemCatLevel3":["Manual Valves"],
        "ItemCatLevel4":["Valves & Actuations"],
        "ItemDesc":["3\" PVC EPDM Ball Check Threaded"],
        "ItemSize":4,
        "ItemSizeFilterSeq":["SIZ"],
        "ItemSizeId":["3\""],
        "ItemSizeSeq":1860,
        "ItemSizeType":20,
        "ItemMaterial":57,
        "ItemMaterialFilterSeq":["MAT"],
        "ItemMaterialId":["PVC"],
        "ItemMaterialSeq":30,
        "ItemMaterialType":30,
        "ItemEnd":0,
        "ItemEndFilterSeq":[" "],
        "ItemEndId":[" "],
        "ItemEndType":0,
        "ItemFamily":0,
        "ItemFamilyFilterSeq":[" "],
        "ItemFamilyId":[" "],
        "ItemFamilySeq":0,
        "ItemFamilyType":0,
        "ItemElastic":0,
        "ItemElasticFilterSeq":[" "],
        "ItemElasticId":[" "],
        "ItemElasticSeq":0,
        "ItemElasticType":0,
        "ItemSdr":0,
        "ItemSdrFilterSeq":[" "],
        "ItemSdrId":[" "],
        "ItemSdrSeq":0,
        "ItemSdrType":0,
        "ItemImage":[" "],
        "id":"ef3b0cba-2427-4697-977f-f39269fe495a",
        "_version_":1661042533077090308},
      {
        "StoreFrontID":9,
        "ItemCode":["1211040"],
        "ItemCatLevel1":["Ball Check"],
        "ItemCatLevel2":["Check Valves"],
        "ItemCatLevel3":["Manual Valves"],
        "ItemCatLevel4":["Valves & Actuations"],
        "ItemDesc":["4\" PVC EPDM Ball Check Threaded"],
        "ItemSize":4,
        "ItemSizeFilterSeq":["SIZ"],
        "ItemSizeId":["4\""],
        "ItemSizeSeq":2100,
        "ItemSizeType":20,
        "ItemMaterial":57,
        "ItemMaterialFilterSeq":["MAT"],
        "ItemMaterialId":["PVC"],
        "ItemMaterialSeq":30,
        "ItemMaterialType":30,
        "ItemEnd":0,
        "ItemEndFilterSeq":[" "],
        "ItemEndId":[" "],
        "ItemEndType":0,
        "ItemFamily":0,
        "ItemFamilyFilterSeq":[" "],
        "ItemFamilyId":[" "],
        "ItemFamilySeq":0,
        "ItemFamilyType":0,
        "ItemElastic":0,
        "ItemElasticFilterSeq":[" "],
        "ItemElasticId":[" "],
        "ItemElasticSeq":0,
        "ItemElasticType":0,
        "ItemSdr":0,
        "ItemSdrFilterSeq":[" "],
        "ItemSdrId":[" "],
        "ItemSdrSeq":0,
        "ItemSdrType":0,
        "ItemImage":[" "],
        "id":"787fd730-07fe-40ac-9970-44bc0374d06b",
        "_version_":1661042533077090309}
  },
  "facet_counts":{
    "facet_queries":{},
    "facet_fields":{
      "ItemSize":[
        "4",5322,
        "0",2],
      "ItemMaterial":[
        "57",5322,
        "0",2],
      "ItemEnd":[
        "0",4968,
        "58",356]},
    "facet_ranges":{},
    "facet_intervals":{},
    "facet_heatmaps":{}}}
We have used PRIM_JSON to read this and PRIM_JSON.Member to read this.

For every array, we have to set it as one of the defined PRIM_JSON.Member and do a For_Each on it. I think the problem as to why it is slow is that most of the string values coming back from the JSON are set inside their own arrays and we have to do the same command for each one of the returned values.

Is there any way that we could improve the rate that we are reading this set of data? To give an example of the situation right now, we are reading all of the data coming back in the "docs" node and placing them on two separate working lists. This whole process takes about 6 seconds for 500 entries.

Re: Reading a large set of JSON data

Posted: Tue Mar 17, 2020 9:08 am
by soa
Use XPRIM_Jsonreader - this is a forward only json parser. It identifies the start and end of objects and arrays etcetera so you have to code to keep a track of where you are but its around 30 times faster than the other stuff. Search this forum for an example.

Re: Reading a large set of JSON data

Posted: Tue Mar 17, 2020 1:25 pm
by jan
Thanks for the response. Apparently, the team has agreed to just limit the searches to 100 for now so we will keep using the current implementation to move things forward.

On the other hand, I could not find any examples on how to use XPRIM_JSONReader. Would there be any examples of this in the documentation?

Re: Reading a large set of JSON data

Posted: Wed Mar 18, 2020 9:43 am
by soa
My Mistake.Try

Define_Com Class(#PRIM_JSON.Document) Name(#lDocument)
Define_Com Class(#PRIM_DC.UnicodeString) Name(#UniCodeString)

#UniCodeString := #Request.Response.AsString

#lDocument.LoadFromString( #UniCodeString.AsNativeString )

For Each(#Child) In(#lDocument.RootNode)

#CONRSPID := #Child<'id'>.AsInt32
#CONSSNID := #Child<'test_session'>.AsInt32
#CONITMID := #Child<'test_item'>.AsInt32
#iResponse := #Child<'response'>.AsString
#iResponseFull := #Child<'full_response'>.AsString

Endfor

Searching for #PRIM_JSON.Document WILL yield some content.
All times are UTC+11:00
Page 1 of 1

Powered by phpBB® Forum Software © phpBB Limited