Hi there. I'm working on returning multple query results based on an inner array.
For instance, I have an object that looks like this:
{
"customer_order_number": "T391704031545",
"aggregation_date_time": "2023-09-17T09:03:17.000Z",
"sales_order_creation_date_time": "2023-09-17T09:03:17.000Z",
"sales_order_modification_date_time": "2023-09-17T09:03:17.000Z",
"max_order_status": "2160",
"min_order_status": "2160",
"order_status_history": [
{
"status_name": "DOES_NOT_MATTER",
"status_date_time": "2023-09-17T09:03:17.000Z"
}
],
"order_lines": [
{
"order_line_number": "1",
"min_line_status": "2160",
"max_line_status": "2160",
"order_line_status_history": [
{
"status_name": "LATE",
"status_date_time": "2023-09-17T09:03:17.000Z",
"status_quantity": "5.0"
}
],
"order_line_type": "TEST",
"destination_zip_code": "30188",
"destination_state_code": "GA",
"destination_time_zone": "-7",
"sku": "",
"parent_line_relationship": [
{
"parent_line_number": "1",
"parent_line_relationship_type": "CHILD"
}
],
"original_ordered_quantity": "5",
"current_quantity": "5",
"cancelled_quantity": "0",
"returned_quantity": "0",
"fulfillment_type": "OPEN",
"promised_delivery_date": "2023-10-19",
"purchase_orders": [
{
"purchase_order_number": "12345",
"purchase_order_creation_date_time": "2023-09-17T09:03:17.000Z",
"purchase_order_modification_date_time": "2023-09-17T09:03:17.000Z",
"delivery_method": "VIA_PARCEL",
"ship_by_date": "2023-10-18",
"completely_shipped_date_time": null,
"shipments": [
{
"monitor_log": [],
"tracking_number": "TID234321",
"carrier_scac": "fdeg",
"carrier_name": "FedEx",
"leg_type": "LastMile",
"origin_location_id": "3",
"destination_location_id": "43",
"shipment_status": "SHIP_ME_HARDER",
"estimated_delivery_date": "2023-10-17",
"delivery_window": {
"start": "2023-09-17T09:03:17.000Z",
"end": "2023-09-17T09:03:17.000Z"
},
"tracking_events": [
{
"event_date_time": "2023-09-17T09:03:17.000Z",
"local_date_time": "2023-09-18T03:27:00",
"event_status": "NOT_DELIVERED",
"event_description": "On FexEx for Delievery",
"ingestion_date_time": "2023-09-17T09:03:17.000Z",
"event_location_city": "SANTA BARBARA",
"event_location_state_code": "CA",
"event_type": "tracking",
"estimated_delivery_date": "2023-09-17",
"revised_estimated_delivery_date": "2023-09-17"
}
]
},
{
"monitor_log": [],
"tracking_number": "TID234323",
"carrier_scac": "fdeg",
"carrier_name": "FedEx",
"leg_type": "LastMile",
"origin_location_id": "3",
"destination_location_id": "43",
"shipment_status": "SHIP_ME_HARDER",
"estimated_delivery_date": "2023-10-17",
"delivery_window": {
"start": "2023-09-17T09:03:17.000Z",
"end": "2023-09-17T09:03:17.000Z"
},
"tracking_events": [
{
"event_date_time": "2023-09-17T09:03:17.000Z",
"local_date_time": "2023-09-18T03:27:00",
"event_status": "DELIVERED",
"event_description": "On FexEx for Delievery",
"ingestion_date_time": "2023-09-17T09:03:17.000Z",
"event_location_city": "SANTA BARBARA",
"event_location_state_code": "CA",
"event_type": "tracking",
"estimated_delivery_date": "2023-09-17",
"revised_estimated_delivery_date": "2023-09-17"
}
]
}
]
}
]
}
]
}
As you see, I have a single cusomer_order_number, but multiple shipments that are late (not delievered).
I have a query that seems to pull the shipments that don't have statuses I'm testing against, and it pulls back the full record - but I need two results. One for each late shipment. It would look like this:
{
"customer_order_number": "T12345678",
"order_status_history": [
{
"status_name": "DOES_NOT_MATTER",
"status_date_time": "2023-09-17T09:03:17.000Z"
}
],
"order_lines": [
{
"order_line_number": "1",
"min_line_status": "2160",
"max_line_status": "2160",
"order_line_status_history": [
{
"status_name": "LATE",
"status_date_time": "2023-09-17T09:03:17.000Z",
"status_quantity": "5.0"
}
],
"order_line_type": "TEST",
"destination_zip_code": "30188",
"destination_state_code": "GA",
"destination_time_zone": "-7",
"sku": "",
"parent_line_relationship": [
{
"parent_line_number": "1",
"parent_line_relationship_type": "CHILD"
}
],
"original_ordered_quantity": "5",
"current_quantity": "5",
"cancelled_quantity": "0",
"returned_quantity": "0",
"fulfillment_type": "OPEN",
"promised_delivery_date": "2023-10-19",
"purchase_orders": [
{
"purchase_order_number": "12345",
"purchase_order_creation_date_time": "2023-09-17T09:03:17.000Z",
"purchase_order_modification_date_time": "2023-09-17T09:03:17.000Z",
"delivery_method": "VIA_PARCEL",
"ship_by_date": "2023-10-18",
"completely_shipped_date_time": null,
"shipments": [
{
"monitor_log": [],
"tracking_number": "TID234321",
"carrier_scac": "fdeg",
"carrier_name": "FedEx",
"leg_type": "LastMile",
"origin_location_id": "3",
"destination_location_id": "43",
"shipment_status": "SHIP_ME_HARDER",
"estimated_delivery_date": "2023-10-17",
"delivery_window": {
"start": "2023-09-17T09:03:17.000Z",
"end": "2023-09-17T09:03:17.000Z"
},
"tracking_events": [
{
"event_date_time": "2023-09-17T09:03:17.000Z",
"local_date_time": "2023-09-18T03:27:00",
"event_status": "NOT_DELIVERED",
"event_description": "In a ditch somewhere",
"ingestion_date_time": "2023-09-17T09:03:17.000Z",
"event_location_city": "SANTA BARBARA",
"event_location_state_code": "CA",
"event_type": "tracking",
"estimated_delivery_date": "2023-09-17",
"revised_estimated_delivery_date": "2023-09-17"
}
]
}
]
}
]
}
]
}
{
"customer_order_number": "T12345678",
"order_status_history": [
{
"status_name": "DOES_NOT_MATTER",
"status_date_time": "2023-09-17T09:03:17.000Z"
}
],
"order_lines": [
{
"order_line_number": "1",
"min_line_status": "2160",
"max_line_status": "2160",
"order_line_status_history": [
{
"status_name": "LATE",
"status_date_time": "2023-09-17T09:03:17.000Z",
"status_quantity": "5.0"
}
],
"order_line_type": "TEST",
"destination_zip_code": "30188",
"destination_state_code": "GA",
"destination_time_zone": "-7",
"sku": "",
"parent_line_relationship": [
{
"parent_line_number": "1",
"parent_line_relationship_type": "CHILD"
}
],
"original_ordered_quantity": "5",
"current_quantity": "5",
"cancelled_quantity": "0",
"returned_quantity": "0",
"fulfillment_type": "OPEN",
"promised_delivery_date": "2023-10-19",
"purchase_orders": [
{
"purchase_order_number": "12345",
"purchase_order_creation_date_time": "2023-09-17T09:03:17.000Z",
"purchase_order_modification_date_time": "2023-09-17T09:03:17.000Z",
"delivery_method": "VIA_PARCEL",
"ship_by_date": "2023-10-18",
"completely_shipped_date_time": null,
{
"monitor_log": [],
"tracking_number": "TID234323",
"carrier_scac": "fdeg",
"carrier_name": "FedEx",
"leg_type": "LastMile",
"origin_location_id": "3",
"destination_location_id": "43",
"shipment_status": "SHIP_ME_HARDER",
"estimated_delivery_date": "2023-10-17",
"delivery_window": {
"start": "2023-09-17T09:03:17.000Z",
"end": "2023-09-17T09:03:17.000Z"
},
"tracking_events": [
{
"event_date_time": "2023-09-17T09:03:17.000Z",
"local_date_time": "2023-09-18T03:27:00",
"event_status": "DELIVERED",
"event_description": "Still at my house",
"ingestion_date_time": "2023-09-17T09:03:17.000Z",
"event_location_city": "SANTA BARBARA",
"event_location_state_code": "CA",
"event_type": "tracking",
"estimated_delivery_date": "2023-09-17",
"revised_estimated_delivery_date": "2023-09-17"
}
]
}
]
}
]
}
]
}
Basically, one full record for each customer_order_number.
Is there a way to split this out based on a common customer_order_number? It's likely that I'll have many many records to search through ad split out. There will be orders with multiple purchase orders, each with multiple shipments. I'd need each late shipment to be its own return - so in the example above, I'd have two records.
If it had two purchase orders with 3 shipments in the first order, and 1 in the following, I'd need to return 4 records, tied to its own purchase_order, then back to the order_number.
Is this possible with an elasticsearch query?? Have I bitten off more than I can chew?
What I currently have is this:
{
"query": {
"bool": {
"filter": [
{
"exists": {
"field": "customer_order_number"
}
},
{
"nested": {
"inner_hits": {
"name": "eventStatus"
},
"path": "order_lines.purchase_orders.shipments.tracking_events",
"query": {
"bool": {
"mustNot": [
{
"terms": {
"order_lines.purchase_orders.shipments.tracking_events.event_status": [
"OUT_FOR_DELIVERY",
"DELIVERED",
"CANCELED",
"RETURN_TO_SENDER",
"REVISED_ESTIMATED_DELIVERY_DATE",
"PROJECTED_PROMISE_DATE_MISS",
"AVAILABLE_FOR_PICKUP",
"CONSIGNEE_REFUSED",
"DISPOSED OTHER"
]
}
},
{
"nested": {
"path": "order_lines.purchase_orders.shipments.monitor_log",
"query": {
"bool": {
"filter": [
{
"exists": {
"field": "order_lines.purchase_orders.shipments.monitor_log.last_execution_ts"
}
}
]
}
}
}
}
]
}
}
}
},
{
"nested": {
"path": "order_lines",
"query": {
"bool": {
"filter": [
{
"terms": {
"order_lines.destination_state_code": [
"NY",
"PA",
"GA",
"NJ",
"OH",
"VA",
"MA",
"NC",
"MD",
"SC",
"CT",
"NH",
"ME",
"DE",
"RI",
"WV",
"VT",
"DC"
]
}
},
{
"range": {
"order_lines.promised_delivery_date": {
"gte": "now/d",
"lte": "now/d"
}
}
},
{
"nested": {
"inner_hits": {
"name": "ShipmentObj"
},
"path": "order_lines.purchase_orders.shipments",
"query": {
"bool": {
"filter": [
{
"exists": {
"field": "order_lines.purchase_orders.shipments.tracking_number"
}
},
{
"terms": {
"order_lines.purchase_orders.shipments.carrier_scac": [
"uspn",
"otcl",
"fdeg"
]
}
}
]
}
}
}
}
]
}
}
}
}
]
}
},
"_source": {
"excludes": ["_source"],
"includes": ["customer_order_number",
"eventStatus"
]
}
}
How far away from a solution am I? Trying to add a
"split": {"field": "customer_order_number"}
didn't make it happy, and I'm having trouble with my google fu on finding a better solution.