Convert single row with list of ids to a table and join it with another table

[elad]

I have this query:

my search

| rex field=line ".*customerId\":(?<customer_id>[0-9]+)"

| dedup customer_id

| table customer_id 

That returns multiple rows and generate a table:

customer_id
-----------
1
2
3
4
5

I also have another query  that returns a single row with an array  of ids:

Synced accounts: [ 1, 3, 5 ]

My questions are :

1) How can I convert the row from query 2 into a table with the ids 

2) how can I do left join between the results ( that I will see on the table only the ids from query 2)?

customer_id
----------
1
3
5

Thanks in advance 

Elad

[elad]

@PickleRick please see the example I provided:  

Synced accounts: [ 1, 3, 5 ]

[PickleRick]

But is it a single string value, a multivalued field, something else?

[elad]

Please see @ITWhisperer answer - it does a group regex for the row and then convert it to multiple events with mvexpand

[ITWhisperer]

my search
| rex field=line ".*customerId\":(?<customer_id>[0-9]+)"
| dedup customer_id
| table customer_id 
| join type=left customer_id [ your other search
  | rex max_match=0 "(?<customer_id>\d+)"
  | mvexpand customer_id
  | table customer_id]

[elad]

@ITWhisperer thanks this was exactly what I was looking for.  But I have another issue now - the full row actually is something like : 

Synced accounts: [1,3,5], Deleted accounts: [2,4]

How can I make the regex ignore the groups on the deleted accounts list?

[ITWhisperer · Accepted solution]

You would have to do two rex

| rex "Synced accounts: \[(?<customer_ids>[^\]]+)\]"
| rex field=customer_ids max_match=0 "(?<customer_id>\d+)"

[elad]

Amazing thanks!

[PickleRick]

What do you mean by "array"?

And are you sure you want a left join? It looks  more like inner join.