Dear Splunk community, I have following sample input data, containing JSON snippets in MV fields: | makeresults count=5 | streamstats count as a | eval _time = _time + (60*a)
| eval
json1="{\"...
See more...
Dear Splunk community, I have following sample input data, containing JSON snippets in MV fields: | makeresults count=5 | streamstats count as a | eval _time = _time + (60*a)
| eval
json1="{\"id\":1,\"attrib_A\":\"A1\"}#{\"id\":2,\"attrib_A\":\"A2\"}#{\"id\":3,\"attrib_A\":\"A3\"}#{\"id\":4,\"attrib_A\":\"A4\"}#{\"id\":5,\"attrib_A\":\"A5\"}",
json2="{\"id\":2,\"attrib_B\":\"B2\"}#{\"id\":3,\"attrib_B\":\"B3\"}#{\"id\":4,\"attrib_B\":\"B4\"}#{\"id\":6,\"attrib_B\":\"B6\"}"
| makemv delim="#" json1
| makemv delim="#" json2
| table _time, json1, json2 The lists of ids in json1 and json2 may be disjoint, identical or overlap. For example, in above data, id=1 and id=5 only exist in json1, id=6 only exists in json2, the other ids exist in both. Attributes can be null values, but may then be treated as if the id didn't exist. For each event, I would like to merge the data from json1 and json2 into a single table with columns id, attrib_A and attrib_B. The expected output for the sample data would be: _time id attrib_A attrib_B t 1 A1 null t 2 A2 B2 t 3 A3 B3 t 4 A4 B4 t 5 A5 null t 6 null B6 ... ... ... ... t+5 1 A1 null t+5 2 A2 B2 t+5 3 A3 B3 t+5 4 A4 B4 t+5 5 A5 null t+5 6 null B6 How can I achieve this in a straighforward way? The following works for the sample data, but it seems overly complicated and am not sure if it works in all cases: ```insert after above sample data generation:```
```extract and expand JSONs```
| mvexpand json2
| spath input=json2
| rename id as json2_id
| mvexpand json1
| spath input=json1
| rename id as json1_id
| table _time, json1_id, attrib_A, json2_id, attrib_B
```create mv fields containing the subsets of IDs from json1 and json2```
| eventstats values(json1_id) as json1, values(json2_id) as json2 by _time
| eval only_json1=mvmap(json1, if(isnull(mvfind(json2, json1)), json1, null()))
| eval only_json2=mvmap(json2, if(isnull(mvfind(json1, json2)), json2, null()))
| eval both=mvmap(json1, if(isnotnull(mvfind(json2, json1)), json1, null()))
| table _time, json1_id, attrib_A, json2_id, attrib_B, json1, json2, only_json1, only_json2, both
```keep json2 record if a) json2_id equals json1_id or b) json2_id does not appear in json1```
| eval attrib_B=if(json2_id==json1_id or isnull(mvfind(json1, json2_id)), attrib_B, null())
| eval json2_id=if(json2_id==json1_id or isnull(mvfind(json1, json2_id)), json2_id, null())
```keep json1 record if a) json1_id equals json2_id or b) json1_id does not appear in json2```
| eval attrib_A=if(json1_id==json2_id or isnull(mvfind(json2, json1_id)), attrib_A, null())
| eval json1_id=if(json1_id==json2_id or isnull(mvfind(json2, json1_id)), json1_id, null())
```remove records where json1 and json2 are both null```
| where isnotnull(json1_id) or isnotnull(json2_id)
| table _time, json1_id, attrib_A, json2_id, attrib_B
| dedup _time, json1_id, attrib_A Thank you!