I have some POS log data that I’m converting from XML which has a nested
list of items/actions added during the transaction. The issue I’m having is
that when I try and flatten the list Drill complains:
0: jdbc:drill:> select
flatten(t.RetailTransaction.POSLog.`ARTS:Transaction`.`ARTS:RetailTransaction`.`ARTS:LineItem`)
from `multimap` t ;
Error: DATA_READ ERROR: Error parsing JSON - You tried to start when
you are using a ValueWriter of type NullableVarCharWriterImpl.
File /user/cmatta/projects/multimap/data.json
Record 1
Fragment 0:0
[Error Id: 544e0177-20a7-4e59-9e29-6ac199e31701 on
se-node13.se.lab:31010] (state=,code=0)
This is because the ARTS:LineItem list has multiple types of map schemas,
depending on what the operator does at the POS, here is a sampling:
// PreferredCustomer
{
"EntryMethod": "Keyed",
"VoidFlag": "false",
"PreferredCustomer": {
"CustomerIdentification": {
"NoSaleData": "00",
"CustomerAccountID": "xxxx",
"EntryMethod": "Keyboard"
}
},
"ARTS:SequenceNumber": "21"
},
// ARTS:Sale
{
"EntryMethod": "Scanned",
"ARTS:Sale": {
"ItemType": "Stock",
"ItemSupplementalInformation": {
"RetailTransactionLineItemFlags": {
...
},
"FamilyCodePrevious": "136",
"FamilyCodeCurrent": "411",
"GroceryDepartment": "67",
"OrdinalNumber": "1"
},
"ARTS:Quantity": "1",
"ARTS:ExtendedAmount": "5.19",
"ARTS:POSIdentity": {
"ARTS:POSItemID": "1200080999"
}
},
"VoidFlag": "false",
"ARTS:SequenceNumber": "30"
},
// ARTS:Tender
{
"VoidFlag": "false",
"ARTS:SequenceNumber": "80",
"ARTS:Tender": {
"ARTS:Amount": "1.00",
"TypeCode": "Sale",
"ARTS:TenderID": "83",
"ARTS:Coupon": {
"ARTS:SecondaryLabel": "0",
"ARTS:ManufacturerID": "0",
"ARTS:PrimaryLabel": "990011",
"ARTS:ExpirationDate": "2005-12-31",
"ARTS:FamilyCode": "000000",
"ItemSupplementalInformation": {
"RetailTransactionLineItemFlags": {
...
},
"FamilyCodePrevious": "000",
"FamilyCodeCurrent": "000",
"GroceryDepartment": "5",
"OrdinalNumber": "4"
},
"ARTS:Quantity": "1",
"ARTS:PromotionCode": "0"
},
"TenderType": "Voucher"
}
},
// ARTS:LoyaltyReward
{
"ARTS:LoyaltyReward": {
"ARTS:PromotionID": "Electronic Marketing",
"PointsAwarded": "3005"
},
"VoidFlag": "false",
"ARTS:SequenceNumber": "240"
},
I’ve tried using the new exec.enable_union_type=true setting but that
returns no results to the same query:
0: jdbc:drill:> alter session set `exec.enable_union_type`=true;
+-------+----------------------------------+
| ok | summary |
+-------+----------------------------------+
| true | exec.enable_union_type updated. |
+-------+----------------------------------+
1 row selected (0.217 seconds)
0: jdbc:drill:> select
flatten(t.RetailTransaction.POSLog.`ARTS:Transaction`.`ARTS:RetailTransaction`.`ARTS:LineItem`)
from `multimap` t ;
+---------+
| EXPR$0 |
+---------+
+---------+
No rows selected (0.305 seconds)
How can I flatten this map?
This is Drill v1.4.
Chris [email protected]
215-701-3146
