0

First let me say I am entirely new to ArangoDb and coding in a non relational world (sql being my experience).

We have an issue where we are looking to pull out information from an embedded list. We have the below scenario where the data comes through like below:

[
  {"addressType":"LO","city":"test","country":"US"
  },

  {"addressType":"PPB","city":"glasgcity","country":"GB"
  }
]

Now at a basic level I can get the data to come through for each field by referring to the position like below:

FOR t IN CustDetails
    RETURN { 
        Key: t._key, 
        ID: t._id, 
        REV: t._rev,
        addresses: t.customer.addresses,
        address_Type: [t.customer.addresses[*].addressType],
        address_Type1: [t.customer.addresses[0].addressType],
        address_Type2: [t.customer.addresses[1].addressType]
    }

This works fine and pulls out the element I need in a flattened out view.

In an ideal world though I would pull the above example with multiple records like below:

AddressType City Country LO test US PPB glasgcity GB

Now I realise I'm still thinking in my old relational world but I have a few reasons I would like to do this: 1. I need to pull and merge with our old Oracle source using a data virtualization tool 2. Although a simple example there can be up to 15 addresstypes for the example shown, so I'd like to avoid having to write 15 versions of the city, postcode field, etc and then upivot in the format I need to union with our relational data.

I was looking into array contraction [*], [**] approaches but to date have only been able to get this working for a field on it's own (i.e. pull back city in multiple records but doesn't work when I add in country).

Any potential functions and approaches to solving such a problem would be greatly appreciated if any exists (or potential documentation that may help solve my issue).

darkheir
  • 8,844
  • 6
  • 45
  • 66
run400
  • 1
  • Could you give an example on how you want to get the data ? `AddressType City Country LO test US PPB glasgcity GB` is not clear for me. – darkheir Dec 24 '19 at 10:50
  • Apologies , strange formatting got applied. So ideally it would come through with multiple records . AddressType City Country LEgal Paris. France Domicile. London. UK. Other. Rome. Italy. So really outputting the data in three records of three address types rather. In essence I am pulling this data into a reporting tool so close just pull through the address types in the json format then get the tool to parse it and then I pivot the data but I would like to determine if possible in aql. – run400 Dec 24 '19 at 20:56
  • Apologies the formatting when I try to show expected format (on a table format really ) is coming out strange . I’ll try to format accordingly when not posting from my phone – run400 Dec 24 '19 at 20:57

0 Answers0