Json Element addressing

[Michael Greer]

I have this JSON:
{
  "totalSize":1,
  "done":true,
  "records":[
    {
      "attributes":{
        "type":"Order",
        "url":"/services/data/v54.0/sobjects/Order/8012D0000016xq5QAA"
      },
      "Id":"8012D0000016xq5QAA",
      "OrderNumber":"00000100",
      "CreatedDate":"2022-02-21T13:13:01.000+0000",
      "OrderItems":{
        "totalSize":1,
        "done":true,
        "records":[
          {
            "attributes":{
              "type":"OrderItem",
              "url":"/services/data/v54.0/sobjects/OrderItem/8022D000001GgV5QAK"
            },
            "Id":"8022D000001GgV5QAK",
            "Product2":{
              "attributes":{
                "type":"Product2",
                "url":"/services/data/v54.0/sobjects/Product2/01t2D0000050iKrQAI"
              },
              "StockKeepingUnit":"25"
            },
            "Quantity":5.0,
            "TotalPrice":1250.0
          }
        ]
      }
    }
  ]
}

I cannot figure out how to address, the OrderItems, or the "records" under that element.  The above is the result of a dim load test$=myjson$.

[Stéphane Devouard]

Michael

Is that what you are looking after ?

Code Select Expand


records=num(test$["totalSize"])
for records
items=num(test$["records."+str(records)+".OrderItems.totalSize"])
for items
itemID$=test$["records."+str(records)+".OrderItems.records."+str(items)+".Id"]
quantity=num(test$["records."+str(records)+".OrderItems.records."+str(items)+".Quantity"])
totalPrice=num(test$["records."+str(records)+".OrderItems.records."+str(items)+".totalPrice"])
next
next

Hope this helps

[Michael Greer]

Stephan,

Except for having to add a "." after the first record #, this is perfect. Thanks!  Thought I had tried that, but missed an index.

Michael

[Stéphane Devouard]

Michael
I think I found the missing dot between the moment you copied and tested the code and the moment you posted your answer ;-)
Glad I could help

[Loren Doornek]

I like Stephane's approach since it's simple and clean because the 'totalSize' tells you how many records are in each segment. 

I generally use WHILE/WEND, since I've never encountered any JSON that included a record count.  When using the WHILE/WEND, you just need to identify a field that will occur in each new segment to indicate a new record.  In this case, the "Id" field serves that purpose.  In case you run into a scenario where the record count ("totalSize") isn't included or isn't reliable, the bit of code below is an example of how to read through the JSON without relying on a record count.

Code Select Expand


0010 BEGIN
0020 LET json$=$7B22746F74616C53697A65223A312C22646F6E65223A747275652C227265636F726473223A5B7B2261747472696275746573223A7B2274797065223A224F72646572222C2275726C223A222F73657276696365732F646174612F7635342E302F736F626A656374732F4F726465722F383031324430303030303136787135514141227D2C224964223A22383031324430303030303136787135514141222C224F726465724E756D626572223A223030303030313030222C224372656174656444617465223A22323032322D30322D32315431333A31333A30312E3030302B30303030222C224F726465724974656D73223A7B22746F74616C53697A65223A312C22646F6E65223A747275652C227265636F726473223A5B7B2261747472696275746573223A7B2274797065223A224F726465724974656D222C2275726C223A222F73657276696365732F646174612F7635342E302F736F626A656374732F4F726465724974656D2F38303232443030303030314767563551414B227D2C224964223A2238303232443030303030314767563551414B222C2250726F6475637432223A7B2261747472696275746573223A7B2274797065223A2250726F6475637432222C2275726C223A222F73657276696365732F646174612F7635342E302F736F626A656374732F50726F64756374322F303174324430303030303530694B72514149227D2C2253746F636B4B656570696E67556E6974223A223235227D2C225175616E74697479223A352E302C22546F74616C5072696365223A313235302E307D5D7D7D5D7D$
0030 DIM LOAD test${ALL}=json$
0040 LET pf1=0
0050 WHILE 1
0060 LET pf1$="records."+STR(++pf1)+"."
0070 IF NOT(DIM(INDEX test$[pf1$+"Id"])) THEN BREAK
0080 PRINT test$[pf1$+"Id"]
0090 PRINT test$[pf1$+"attributes.type"]
0100 PRINT test$[pf1$+"attributes.url"]
0110 PRINT test$[pf1$+"OrderNumber"]
0120 PRINT test$[pf1$+"CreatedDate"]
0130 !
0140 LET pf2=0
0150 WHILE 1
0160 LET pf2$=pf1$+"OrderItems.records."+STR(++pf2)+"."
0170 IF NOT(DIM(INDEX test$[pf2$+"Id"])) THEN BREAK
0180 PRINT test$[pf2$+"Id"]
0190 PRINT test$[pf2$+"attributes.type"]
0200 PRINT test$[pf2$+"attributes.url"]
0210 PRINT test$[pf2$+"Product2.attributes.type"]
0220 PRINT test$[pf2$+"Product2.attributes.url"]
0230 PRINT test$[pf2$+"Product2.StockKeepingUnit"]
0240 PRINT test$[pf2$+"Quantity"]
0250 PRINT test$[pf2$+"TotalPrice"]
0260 WEND
0270 WEND


[Stéphane Devouard]

Loren


Your version is more generic.
Guess our lives would be simpler if PxPlus had a flavour of the dim(read num(array)) that would count the number of elements in a json array converted to a hash table. Maybe something like count=dim(read num(array[$],key="..."))


Regards