Is it possible to set up a multidimensional CSV lookup?

[brcrommett]

Hello,

I'd like to setup a lookup table to provide additional static information for a set of known parameters. However, some of the fields vary in number (one to many). In a SQL database, I'm using 2 tables, and a join between them. Is something like this possible using the lookup functionality? I'm sure if this is possible, it's documented somewhere - I haven't had any luck finding it.

ex:
table: product -> columns: product_id,name,latency,owner,customer_id
table: customers -> columns: customer_id,customer_name,address

In this made up example, I'd like to be able to put a list of customer/name in a table for each match to say product/name.

Hopefully this makes sense - thanks in advance!

--dp

[woodcock]

You can do this, just try it. The duplicated values will show up as a multi-valued field. The only problem that you may have is that there are limits on the number of multiple values the field may have. You will probably have to use mvzip and some other stuff to join things together after the lookup.

[micahkemp · Accepted solution]

You can use lookups to do that if you put them in your search itself, thus controlling the order:

| lookup product product_id OUTPUT name latency owner customer_id | lookup customers customer_id output customer_name address

However, I'm not sure you can control the order of your lookups if you put them in props.conf to be handled automatically at search time.

[brcrommett]

This is exactly what I needed - I was over thinking things. For what I'm doing, I might try and use the mvexpand, but for the time being a search time solution will work.

Thanks!

[DalJeanis]

assuming that customer_id was a multivalued field, you can avoid the issues @woodcock mentioned by using mvexpand to give each customer their own record for each product...

 (your search that produces a field named product_id)
 | lookup product product_id OUTPUT name latency owner customer_id 
 | mvexpand customer_id
 | lookup customers customer_id output customer_name address

... since you refer to SQL, I'd assume that your tables were properly normalized before the join... in which case the above advice will not apply.

Lookup is only valid when there is a single set of output values per input key. If that is not the case, then there are other structures, like join or append or a grouped search, that can achieve the desired end. If there are multiple customers per product, then something like this can be efficient.

 (your search that produces a field named product_id)
 | eval rectype="detail"

 | rename COMMENT as "This section brings in the records for product_id and rolls them together to separate out each value of customer_id, then kills unneeded values from the lookup table" 
 | inputlookup append=t product  
 | stats values(*) as * by product_id customer_id
 | where isnotnull(rectype)
 | fields - rectype

 | rename COMMENT as "This simple lookup could be expanded as above if there potentially were more than one customer record per customer id."
 | lookup customers customer_id output customer_name address

[woodcock]

Using mvexpand (by itself) will only work if you have a single multi-valued field coming out of the lookup but his description leads me to believe that there will be more than one where mates are matched by position on the fields. This always needs mvzip to work it.