如何使用pljson解析具有多个记录的json

Question

我在解析json数据时遇到了问题，在"orders“中总共有2条记录，我需要基于

订单1和订单2，也包括"orders.order_items“中的每一项

任何帮助都会很好，这让我压力很大.

{
    "status": "success",
    "execution_time": "0.0304 seconds",
    "total_records": 2,
    "records_returned": 2,
    "offset": 0,
    "limit": 150,
    "orders": [{
        "id": "305954583",
        "email": "email@gmail.com",
        "date_added": "2022-03-16 20:42:44",
        "date_updated": "2022-03-16 20:43:12",
        "checkout_data": [],
        "order_metadata": [],
        "discount_list": [],
        "order_notes": [],
        "order_items": [{
            "id": "163220786",
            "name": "099922511015",
            "price": 5,
            "quantity": 3,
            "weight": 0,
            "code": "099922511015",
            "delivery_type": "ship",
            "category_code": "",
            "fulfillment_method": "",
            "variation_list": [],
            "metadata": []
        }],
        "order_shipments": []
    }, {
        "id": "170951391",
        "email": "email2@gmail.com",
        "date_added": "2021-04-27 22:50:11",
        "date_updated": "2022-03-17 02:38:43",
        "checkout_data": [],
        "order_metadata": [],
        "discount_list": [],
        "order_notes": [{
            "date_added": "2022-03-17 02:38:43",
            "username": "username",
            "content": "testing notes"
        }],
        "order_items": [{
            "id": "112184373",
            "name": "COUNTER",
            "price": 1,
            "quantity": 1,
            "weight": 0.25,
            "code": "COUNTER",
            "delivery_type": "ship",
            "category_code": "",
            "fulfillment_method": "",
            "variation_list": [],
            "metadata": []
        }],
        "order_shipments": []
    }]
}

目前，我就是这样得到它的

  for i in 1..2 loop  
  dbms_output.put_line('Order #: '||json_ext.get_string(l_json, 'orders['||i||'].id'));

  temp := json_list(l_json.get('orders['||i||'].order_items'));
  dbms_output.put_line(temp.get_string);
  end loop;

Answer 1

您可以使用：

DECLARE
  obj pljson := pljson(
    '{
    "status": "success",
    "execution_time": "0.0304 seconds",
    "total_records": 2,
    "records_returned": 2,
    "offset": 0,
    "limit": 150,
    "orders": [{
        "id": "305954583",
        "email": "email@gmail.com",
        "date_added": "2022-03-16 20:42:44",
        "date_updated": "2022-03-16 20:43:12",
        "checkout_data": [],
        "order_metadata": [],
        "discount_list": [],
        "order_notes": [],
        "order_items": [{
            "id": "163220786",
            "name": "099922511015",
            "price": 5,
            "quantity": 3,
            "weight": 0,
            "code": "099922511015",
            "delivery_type": "ship",
            "category_code": "",
            "fulfillment_method": "",
            "variation_list": [],
            "metadata": []
        }],
        "order_shipments": []
    }, {
        "id": "170951391",
        "email": "email2@gmail.com",
        "date_added": "2021-04-27 22:50:11",
        "date_updated": "2022-03-17 02:38:43",
        "checkout_data": [],
        "order_metadata": [],
        "discount_list": [],
        "order_notes": [{
            "date_added": "2022-03-17 02:38:43",
            "username": "username",
            "content": "testing notes"
        }],
        "order_items": [{
            "id": "112184373",
            "name": "COUNTER",
            "price": 1,
            "quantity": 1,
            "weight": 0.25,
            "code": "COUNTER",
            "delivery_type": "ship",
            "category_code": "",
            "fulfillment_method": "",
            "variation_list": [],
            "metadata": []
        }],
        "order_shipments": []
    }]
}'
  );
  v_order_list  pljson_list;
  v_order       pljson;
  v_order_items pljson_list;
  v_item        pljson;
BEGIN
  v_order_list := pljson_ext.get_json_list( obj, 'orders');
  FOR i IN 1 .. v_order_list.COUNT() LOOP
    v_order := TREAT(v_order_list.get(i).get_element() AS pljson);
    DBMS_OUTPUT.PUT_LINE('Order id: ' || v_order.get_string('id'));
    v_order_items := TREAT(v_order.get('order_items').get_element() AS pljson_list);
    FOR j IN 1 .. v_order_items.COUNT() LOOP
      v_item := TREAT(v_order_items.get(j).get_element() AS pljson);
      DBMS_OUTPUT.PUT_LINE('  Order item id: ' || v_item.get_string('id'));
    END LOOP;
  END LOOP;
END;
/

其中产出：

订单id: 305954583订单id: 163220786订单id: 170951391订单id: 112184373

db<>fiddle https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=323d6ef810b66b03b635ca1a4a6d5878