Json Query

[david_martinez]

Hello,
I have the next json in splunk:

{

_data : {

services : [

{

id : "FB00000",

users : [

100,

122

]

},

{

id : "FB11111",

users : [

404,

797

]

}

],

socialNetwork : "FB"

},

_timestamp : "01-02-02013T01:00:04.582+0100",

_type : "ServiceReport"

}

I would like to know the query to generate a table with the following format:

ID USER

FB00000 100

FB00000 122

FB11111 404

FB11111 797

I tried with:
sourcetype="singleline_json" AND _type="ServiceReport"

| rename _data.services{}.users{} as USER

| rename _data.services{}.id as ID

| fields ID, USER

| mvexpand USER

| eval x=split(USER,",")

| eval USER = mvindex(x,0)

| table ID, USER

But it does not work.

Thanks in advance!

[to4kawa]

| makeresults 
| eval _raw="{\"_data\":{\"services\":[{\"id\":\"FB00000\",\"users\":[100,122]},{\"id\":\"FB11111\",\"users\":[404,797]}],\"socialNetwork\":\"FB\"},\"_timestamp\":\"01-02-02013T01:00:04.582+0100\",\"_type\":\"ServiceReport\"}"
| spath path=_data.services{} output=data
| kv
| rename data.services{}.* as * 
| stats count by data.socialNetwork timestamp type data
| spath input=data
| stats count values(*) as * by users{}
| rename users{} as users, data.socialNetwork as socialNetwork
| table id users socialNetwork timestamp type

mvexpand gives "mvexpand output will be truncated due to excessive memory usage

Because of this kind of problem,
I thought of an expansion method that doesn't use mvexpand and mvzip.

The point is that the field that becomes multivalue is extracted once and expanded without using mvexpand by stats.

[martin_mueller]

In your json you once have a field "user", and once have a field "users". That can't be good.

[martin_mueller]

I see. Your names in front of the colons also lack quotation marks to be valid JSON.

What exactly isn't working, what results are you getting?

Edit: Once you fix that you can do this:

...  | spath path=_data.services{} output=services | mvexpand services | spath input=services | stats values(users{}) as users by id | mvexpand users

to get this:

    id      users
1   FB00000 100
2   FB00000 122
3   FB11111 404
4   FB11111 797

[david_martinez]

It was a mistake for copy-paste, sorry -> fixed. Thanks for the advice