Reading a large set of JSON data

This Q&A forum allows users to post and respond to "How Do I Do ....." questions. Please do not use to report (suspected) errors - you must use your regional help desk for this. The information contained in this forum has not been validated by LANSA and, as such, LANSA cannot guarantee the accuracy of the information.
Post Reply
jan
Posts: 19
Joined: Thu Sep 06, 2018 12:36 pm

Reading a large set of JSON data

Post by jan » Mon Mar 16, 2020 11:54 pm

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.

soa
Posts: 329
Joined: Mon Dec 07, 2015 3:15 pm

Re: Reading a large set of JSON data

Post by soa » Tue Mar 17, 2020 9:08 am

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.

jan
Posts: 19
Joined: Thu Sep 06, 2018 12:36 pm

Re: Reading a large set of JSON data

Post by jan » Tue Mar 17, 2020 1:25 pm

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?

soa
Posts: 329
Joined: Mon Dec 07, 2015 3:15 pm

Re: Reading a large set of JSON data

Post by soa » Wed Mar 18, 2020 9:43 am

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.

Post Reply