Split pattern into multiple rows?

[LearningGuy]

Split pattern in CSV lookup format into multiple rows
| lookup table.csv
Note that the number of SubnetID-IP pair is not always 4.   It could be from 1 to 4  or more (1...  N)
The example below is only one row, but the real data has multiple rows (eg. Comp-B to Comp-Z)

Before the split:       
Company        Location       SubnetID-IP
Comp-A           Loc- A           [{"subnet_id":"101","ip_address":"192.168.1.1"},{"subnet_id":"102","ip_address":"192.168.1.2"},{"subnet_id":"103","ip_address":"192.168.1.3"},{"subnet_id":"104","ip_address":"fd12:3456:789a:1::1"}]

After the split: 

Company        Location       IP                                subnetID
Comp-A            Loc-A            192.168.1.1                     101
Comp-A            Loc-A            192.168.1.2                     102
Comp-A            Loc-A            192.168.1.3                     103
Comp-A            Loc-A            fd12:3456:789a:1::1     104

Thank you so much for your help

[LearningGuy]

Your first suggestion worked. Thank you so much

| spath input=Subnet-IP path={}
| mvexpand {}
| spath input={}

 
I ran your second suggestion and received this "Error in 'makeresults' command: This command must be the first command of a search."
What is the purpose of the following commands?
Note that my search is using | inputlookup test.csv. It looks like makeresults didn't work with inputlookup. Thank you

| makeresults
| eval Company = "Comp-A", Location = "Loc-A", Subnet-IP = "[{\"subnet_id\":\"101\",\"ip_address\":\"192.168.1.1\"},{\"subnet_id\":\"102\",\"ip_address\":\"192.168.1.2\"},{\"subnet_id\":\"103\",\"ip_address\":\"192.168.1.3\"},{\"subnet_id\":\"104\",\"ip_address\":\"fd12:3456:789a:1::1\"}]"

[yuanliu]

The makeresults block is meant to emulate inputlookup as a way of comparison in case the solution gives unexpected output.

[yuanliu · Accepted solution]

spath is your fiend.

| spath input=Subnet-IP path={}
| mvexpand {}
| spath input={}

Your sample data gives

Company	Location	ip_address	subnet_id	{}
Comp-A	Loc-A	192.168.1.1	101	{"subnet_id":"101","ip_address":"192.168.1.1"}
Comp-A	Loc-A	192.168.1.2	102	{"subnet_id":"102","ip_address":"192.168.1.2"}
Comp-A	Loc-A	192.168.1.3	103	{"subnet_id":"103","ip_address":"192.168.1.3"}
Comp-A	Loc-A	fd12:3456:789a:1::1	104	{"subnet_id":"104","ip_address":"fd12:3456:789a:1::1"}

This is an emulation that you can play with and compare to your real data

| makeresults
| eval Company = "Comp-A", Location = "Loc-A", Subnet-IP = "[{\"subnet_id\":\"101\",\"ip_address\":\"192.168.1.1\"},{\"subnet_id\":\"102\",\"ip_address\":\"192.168.1.2\"},{\"subnet_id\":\"103\",\"ip_address\":\"192.168.1.3\"},{\"subnet_id\":\"104\",\"ip_address\":\"fd12:3456:789a:1::1\"}]"