PxPlus User Forum

Twitter Twitter Twitter

Author Topic: Reading/parsing JSON data  (Read 1461 times)

Mike Hatfield

  • Gold Member
  • ****
  • Posts: 70
    • View Profile
Reading/parsing JSON data
« on: May 15, 2019, 02:12:35 AM »
This is the  JSON Data file supplied

 "Order ID": "000000034",
  "Grand Total": "6",
  "Sub Total": "5.45",
  "Shipping Method": "flatrate_flatrate",
  "Shipping Description": "Order below FIS value - freight to be advised",
  "Payment Method": "Bank Transfer Payment",
  "Billing Details": {
    "City": "Springfield Lakes",
    "Street": "Brisbane",
    "Post Code": "4064",
    "Telephone": "123456789",
    "RegionCode": "Queensland"
  },
  "Shipping Details": {
    "Shipping City": "Springfield Lakes",
    "Shipping Street": "Brisbane",
    "Shipping Post Code": "4064",
    "Shipping Telephone": "123456789",
    "Shipping RegionCode": "Queensland"
  },
  "Customer Details": {
    "First Name": "Test",
    "Last Name": "Maybray",
    "Email": "test234@maybray.com",
    "IP": "144.130.97.53",
    "ID": "412"
  },
  "Products": [
    {
      "ID": "",
      "Name": "9\" x 7\" CLEAR BOOK COVER WITH COLOURED RETURNS - PACK OF 5 - BC97",
      "Type": "simple",
      "Qty": "1",
      "Price": "1.95"
    },
    {
      "ID": "",
      "Name": "SCRAPBOOK COVER - CLEAR - PACK OF 5 - SBC4934",
      "Type": "simple",
      "Qty": "1",
      "Price": "3.5"
    }
  ]
}

My Code snippet below with json as read from file.
What am I doing wrong? How do I access the fields?
My task is to parse the file and create the order in the PxPlus application order file.

  OPEN (HFN,ISZ=10240)"/hit/temp/000000034.json"
  READ RECORD (LFO,ERR=*NEXT)TEMPJSONORDER$;
  LET JSONORDER$+=TEMPJSONORDER$;
  GOTO *SAME
 !
  ESCAPE !
  DIM LOAD MORDER${ALL}=JSONORDER$
 !
  PRINT MORDER$["Order ID"]
 ! This  returns nothing at all



1}? jsonorder$
{"Order ID":"000000034","Grand Total":"6","Sub Total":"5.45","Shipping Method":"
flatrate_flatrate","Shipping Description":"Order below FIS value - freight to be
 advised","Payment Method":"Bank Transfer Payment","Billing Details":{"City":"Sp
ringfield Lakes","Street":"Brisbane","Post Code":"4064","Telephone":"123456789",
"RegionCode":"Queensland"},"Shipping Details":{"Shipping City":"Springfield Lake
s","Shipping Street":"Brisbane","Shipping Post Code":"4064","Shipping Telephone"
:"123456789","Shipping RegionCode":"Queensland"},"Customer Details":{"First Name
":"Test","Last Name":"Maybray","Email":"test234@maybray.com","IP":"144.130.97.53
","ID":"412"},"Products":[{"ID":"","Name":"9\" x 7\" CLEAR BOOK COVER WITH COLOU
RED RETURNS - PACK OF 5 - BC97","Type":"simple","Qty":"1","Price":"1.95"},{"ID":
"","Name":"SCRAPBOOK COVER - CLEAR - PACK OF 5 - SBC4934","Type":"simple","Qty":
"1","Price":"3.5"}]}
1}

1}? morder${all}
00000003465.45flatrate_flatrateOrder below FIS value - freight to be advisedBank
 Transfer PaymentSpringfield LakesBrisbane4064123456789QueenslandSpringfield Lak
esBrisbane4064123456789QueenslandTestMaybraytest234@maybray.com144.130.97.534129
" x 7" CLEAR BOOK COVER WITH COLOURED RETURNS - PACK OF 5 - BC97simple11.95SCRAP
BOOK COVER - CLEAR - PACK OF 5 - SBC4934simple13.5
1}
Mike H

Mike Hatfield

  • Gold Member
  • ****
  • Posts: 70
    • View Profile
Re: Reading/parsing JSON data
« Reply #1 on: May 15, 2019, 02:52:49 AM »
I just worked out most of this.....I make a mistake
I defined the JSON in the program as MORDER$["Order ID"]=""  etc....
then B$=DIM(LIST EDIT MORDER${all})

DIM LOAD MORDER${all}... should have been DIM LOAD B${all}...
Now
PRINT b$["Order ID"] returns 000000034

My remaining question is how to loop through the Products
There is no field containing the number of product lines EG ["Product lines"]

What would be the correct syntax to loop through the products given that any indeterminate loop will generate an error?
Mike H

Thomas Bock

  • Diamond Member
  • *****
  • Posts: 177
    • View Profile
Re: Reading/parsing JSON data
« Reply #2 on: May 15, 2019, 03:21:57 AM »
The products element is an array, because of "[...]".
So you should be able to address the elements with b$["Products.1.Price"] and b$["Products.2.Price"]
Of course a preceding information of how many item lines are contained would be nice. Without that information best thing you can do is to stop on b$["Products."+str(i)+".Qty"]=$$.

Mike Hatfield

  • Gold Member
  • ****
  • Posts: 70
    • View Profile
Re: Reading/parsing JSON data
« Reply #3 on: May 15, 2019, 03:51:17 AM »
Hi Thomas,
I wrote a loop as you described and it will loop for as long as the upper range you give it without error.
You comment is what I thought.
I guess if the field is null as opposed to blank or 0 then its at the end.
Thanks
Mike H