How to extract first level of keys from JSON and show them in a tabular format in Splunk?

Question

I need to display the below JSON in a table format. I want the first level of keys movie1 and movie2 to come under a table column , for example Movie_List. So far, I have tried the below spath command and it is only showing the last extracted value, not both the values for movie1 and movie2 .

| makeresults |eval _raw = "{
    \"movie1\": {
        \"genre\": \"Comedy\",
        \"review\": \"Good\",
        \"release\": \"01:02:2023\"
    }, 
    
        \"movie2\": {
        \"genre\": \"Action\",
        \"review\": \"Average\",
        \"release\": \"01:01:2023\"
    }
}" |spath input=_raw path=movie1{}.genre output=genre 

|spath path=movie1.review output=review
|spath path=movie1.release output=release_date
|spath path=movie2{}.genre output=genre 

|spath path=movie2.review output=review
|spath path=movie2.release output=release_date
|fields - _raw _time 

I want the table to be as below :

Movie_List	genre	review	release_date
movie1	Comedy	Good	01:02:2023
movie2	Action	Average	01:01:2023

Please help.

Thanks

Answer 1

You can use the json_keys eval function to get the keys of the initial JSON. The result will be something like ["movie1","movie2"] (which is not really helpful), but the json_array_to_mv eval function will help you having a multivalue of the keys of the JSON.

And here is a possible solution:

| makeresults
| eval raw = "{
    \"movie1\": {
        \"genre\": \"Comedy\",
        \"review\": \"Good\",
        \"release\": \"01:02:2023\"
    },
    \"movie2\": {
        \"genre\": \"Action\",
        \"review\": \"Average\",
        \"release\": \"01:01:2023\"
    }
}"

| eval movie_id = json_array_to_mv(json_keys(raw))
| mvexpand movie_id
| eval data = json_extract(raw, movie_id), genre = json_extract(data, "genre"), review = json_extract(data, "review"), release_date = json_extract(data, "release")
| table movie_id, genre, review, release_date
| rename movie_id as Movie_List

This may not be the best solution, but it works.