This seems to be an interesting issue with flatten.
All the data seems to be recognized as a list, and would expect flatten to just
unnest the the first layer??
Seems to be somewhat related to DRILL-1673
select
typeof(t.RetailTransaction.POSLog.`ARTS:Transaction`.`ARTS:RetailTransaction`.`ARTS:LineItem`)
from `multimap` t ;
+---------+
| EXPR$0 |
+---------+
| LIST |
| LIST |
| LIST |
| LIST |
| LIST |
| LIST |
| LIST |
| LIST |
| LIST |
| LIST |
+---------+
select
typeof(t.RetailTransaction.POSLog.`ARTS:Transaction`.`ARTS:RetailTransaction`.`ARTS:LineItem`[0])
from `multimap` t ;
+---------+
| EXPR$0 |
+---------+
| MAP |
| MAP |
| MAP |
| MAP |
| MAP |
| MAP |
| MAP |
| MAP |
| MAP |
| MAP |
+---------+
select
typeof(t.RetailTransaction.POSLog.`ARTS:Transaction`.`ARTS:RetailTransaction`.`ARTS:LineItem`[10])
from `multimap` t ;
+---------+
| EXPR$0 |
+---------+
| NULL |
| NULL |
| MAP |
| NULL |
| NULL |
| MAP |
| NULL |
| MAP |
| MAP |
| NULL |
+---------+
--Andries
> On Jan 20, 2016, at 9:24 AM, Christopher Matta <cma...@mapr.com> wrote:
>
> 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 mattacma...@mapr.com
> 215-701-3146
>
