How to extract interesting fields from JSON data pulled from Google Places' API?

[lukas_loder]

Hi!

I'm trying to get Information from Google Places into our Splunk. We want to analyze how we get rated on social media.
I'm able to use the API and get all the Information with the RAST app in a json Format.

Unfortunately, the fields which are interesting have this Name result.reviews{}.author_name . And I'm not able to use these fields in a Data model and/or in Pivot. Does somebody know this Problem? I already tried it with Field alias result.reviews{}.author_Name = _AuthorName but I don't get this one in Splunk.

{
   "html_attributions" : [],
   "result" : {
      "address_components" : [
         {
            "long_name" : "Golden Gate Bridge",
            "short_name" : "Golden Gate Bridge",
            "types" : [ "route" ]
         },
         {
            "long_name" : "California",
            "short_name" : "CA",
            "types" : [ "administrative_area_level_1", "political" ]
         },
         {
            "long_name" : "Vereinigte Staaten",
            "short_name" : "US",
            "types" : [ "country", "political" ]
         }
      ],
      "adr_address" : "\u003cspan class=\"street-address\"\u003eGolden Gate Bridge\u003c/span\u003e, \u003cspan class=\"region\"\u003eCalifornia\u003c/span\u003e, \u003cspan class=\"country-name\"\u003eVereinigte Staaten\u003c/span\u003e",
      "formatted_address" : "Golden Gate Bridge, California, Vereinigte Staaten",
      "formatted_phone_number" : "(415) 921-5858",
      "geometry" : {
         "location" : {
            "lat" : 37.819929,
            "lng" : -122.478255
         }
      },
      "icon" : "http://maps.gstatic.com/mapfiles/place_api/icons/generic_business-71.png",
      "id" : "2213bf496e53a020f356d0274a5a75a4a98907fa",
      "international_phone_number" : "+1 415-921-5858",
      "name" : "Golden Gate Bridge",
      "photos" : [
         {
            "height" : 460,
            "html_attributions" : [
               "\u003ca href=\"https://plus.google.com/116252465308456831481\"\u003eSirena Rios\u003c/a\u003e"
            ],
            "photo_reference" : "CnRoAAAAUNNiCRDBUFiy-B4yGw4-jrggo5DZ1FETN3V3crzIlXG5S59Rjut1tfvJqOwz8Xxs_O4hJ7uQZEIQSBFjlMhfzD0tG1ch0sfl246QUe5xrwsKVBTaf47UuT7nmNPUpU_C1x1yIBRV5V9he5wbnGjnbhIQJPJ5IAjeyUmlgNEULUqh1RoU524MuyQhy2waajQ5C3NVMn4sZKo",
            "width" : 808
         },
         {
            "height" : 360,
            "html_attributions" : [
               "\u003ca href=\"https://plus.google.com/102187346347125126278\"\u003ehitops61\u003c/a\u003e"
            ],
            "photo_reference" : "CnRvAAAAsvPdawhTUkj-P61vqkrFjs3A0VT8TqKTWEOWD60kVc-9piF06BkoNw4Sw2hNd5WHj1ugLSlDzfgaZx1oO5uJ5m-kxWn02FP-rv6zPZsdrtSe7sruD9hc_hW9FwGfpy7OPwqT8oGWs019tDn01_GEARIQ_tDckrOcC8ajUK829NyjMxoUog5RWHZRtCncgPuHT1Z517lp36U",
            "width" : 480
         },
         {
            "height" : 1490,
            "html_attributions" : [
               "\u003ca href=\"https://plus.google.com/107569050817939542355\"\u003eCharo Perez Torrego\u003c/a\u003e"
            ],
            "photo_reference" : "CqQBkwAAAPzqyTiq7YALbd3fyeDZPIZMEoj_dDY4YNqBSK5fHEcmL22ILMji8dUsUVPQi-pHz7BoVbA7LCIWapCU6ffj7nKeJJWeYTcU-ufGeMVMW3-0aJv2FAlnnEuFdQyuS-LJ-iYuABiNnDLvwaYWBW-kAFv_fYBFNJAMuOBmqPXfaxavn5QDKpFGwIU_UaS7HNpVLUhfcQJLJoNu4Y0h0kmQFu54SEGkzIOYjNt3VKrLW_q4vilwaFDVjU75AgnP0r9nhfo-26XkBbEFd",
            "width" : 1500
         },
         {
            "height" : 352,
            "html_attributions" : [
               "\u003ca href=\"https://plus.google.com/113191727760949412949\"\u003eVan Huu\u003c/a\u003e"
            ],
            "photo_reference" : "CnRoAAAAuVQST-Btusn_bGOYGGjd0UeTvpWinirj71HKr1hj7UjAYElOqFCQ0hMQhg2lMyjil8jNPL4YvDGx1gJvLFzHVi81Tm2HM4aZOGuHMynuMoL5Sx7G936nxqGJGg_zkL8MdlmsJea3LRCh6Ljjv9w0WBIQ6DLO_rM4PuTZRkN7aHTdehoUD6CKZwgj0q9G8erQyBf7uTyvdyc",
            "width" : 800
         },
         {
            "height" : 450,
            "html_attributions" : [
               "\u003ca href=\"https://plus.google.com/105984049565006804495\"\u003eTami Moen\u003c/a\u003e"
            ],
            "photo_reference" : "CnRoAAAABKsd5uTiKn-8pTVr8zPuUz-svE5XE9FVcxd37eiX4LmFYM3bZJa0d0OxGnFQnr-LIBHS6FxBae_nWDY-cw3BwGQ2_U2ePxxcoR1-JcOGqPXfEBH27O_NPp2NVd7uPTw6k1s-86ThCmWh4mcJSxHmpRIQKIuelY37zJza7gLAx_taVxoUcD3JcYV_gG_6Y4_6t5tAVf8mTJM",
            "width" : 800
         },
         {
            "height" : 480,
            "html_attributions" : [ "Von einem Google-Nutzer" ],
            "photo_reference" : "CoQBdAAAAL-Uln0CGiqS_EY1n4rjZ8KKDEFJsmNUjJbXR8pxv3ATfk0UK8owRxvRkD7IlK-i4eK3uHDgGFrdO4KQp-a4EweFCRF6M_lA7sUPp8drsZDEtZ7Lgptl6dkI0GO9_OQMPfQNccdOyd11bEzfIn_zUawoEr9ZtPCyPh3M8C-VTIOotEhAbLeGWKl18LXM5fGJb0gn3GhQ7IRKyFQB9xqtTRS4zNI8wG7iCVA",
            "width" : 640
         },
         {
            "height" : 816,
            "html_attributions" : [
               "\u003ca href=\"https://plus.google.com/107565519404076123865\"\u003ePrasham Sheth\u003c/a\u003e"
            ],
            "photo_reference" : "CoQBdAAAAGiDC4ydgHtY9FnctMOmjwPik_-VpE-aacy285gP41pjry2Zex_YnbdlNdwX9mdsOjMclwLhaPlO1Sz3FMVAutftKj7PgyYcENbCCBA6mRXvzGcwoQrO2fKuWQb_RrspaLjuSvroCNZEv2MmbKEtHGu0DYrEQl7zXJ_mnxTDdtFMEhCd45G5iOjhaUAWwcGHWkPFGhQQq35Lur4PNmvtsD1dRRcBNdIRZQ",
            "width" : 612
         },
         {
            "height" : 1200,
            "html_attributions" : [
               "\u003ca href=\"https://plus.google.com/117001986278088134060\"\u003eStephen SteveT\u003c/a\u003e"
            ],
            "photo_reference" : "CnRi-AAAAfRxD3Zu6LNmch2BUVeISNHnVyqQ0sAj9GNM5as9Wj3pk_pvW-T1hW1NCMTJCUWwF32kW_TN1yvfoVNIEce2Hi1zTXBqO4_5kIdsyT3jRuLtsLk1TK88V4TEuaOrZgMGfZeewG6ouzwSubQIhJFN98hIQ_T9hrq-U2aC3Ex0_AhWPFhoUh6jx9IGV6O2xTywH1JiPB_HYz40",
            "width" : 900
         },
         {
            "height" : 480,
            "html_attributions" : [
               "\u003ca href=\"https://plus.google.com/115792059750378980460\"\u003eAntónio Matos\u003c/a\u003e"
            ],
            "photo_reference" : "CnRoAAAAESKkwwsmwjbd0D8VH9xYrPY-qCN8gam0De0KC9UWPLTFP6fiCDPC6ryxLuMf-4QV7V36l6D-ZGDTcdwjqClimKExLIH3d1AezfFYaUTIEP0nVd8gcv9I5RwNztnrF5zOs79ikwihUYtENaO4hi5dxhIQY2k3rxN_HKg8reG6wQ5X4RoUpv0NDIekESH-9GRphSzhjMn8XBE",
            "width" : 640
         },
         {
            "height" : 612,
            "html_attributions" : [
               "\u003ca href=\"https://plus.google.com/113489281302232740823\"\u003eDavid Burkett\u003c/a\u003e"
            ],
            "photo_reference" : "CnRoAAAAVnmf2BVct9UrBiZbMdZiCAWXkPpPmUHHkQiJN72CAAto4InQeKktGQoFwiO9V7_dmleXWGwtpOx-gJpZR4R22eL4yLKkft9dhmNu7rgaIqexvbDvEWx5GSyGpy4CKepTi0XBY1RHlKN2eXcsU6DFLuhIQZeU-zzeeRYH355pSj0RDGhoUVPr3a-EeWJWK-1gM1Bf0W0XPRdA",
            "width" : 816
         }
      ],
      "place_id" : "ChIJw____96GhYARCVVwg5cT7c0",
      "rating" : 4.7,
      "reference" : "CnRmAAAAqIpRW4ANK4KvJMGBEn3lR5R2M15zUEfW0UVf3zvk5c3dGs_5PTx0WpECI5Ktm16nQ6nFRJW-PtfBCD9aD9mE8yILW1DZGIuuNx-WtdBpSW8vQ9uRAN3J68aA70h1lMtkHDySMhWwe59UvpwKXwAkdxIQsgku6hyetLN6grGLOr-iWBoU8n2KvEqDLlI65S0xooUWjQEoXa8",
      "reviews" : [
         {
            "aspects" : [
               {
                  "rating" : 3,
                  "type" : "overall"
               }
            ],
            "author_name" : "Aaron Richter",
            "author_url" : "https://plus.google.com/111270458209006049005",
            "language" : "de",
            "rating" : 5,
            "text" : "Es war atemberaubend dieser ausblick in die ferne einfach nur episch ",
            "time" : 1433583287
         },
         {
            "aspects" : [
               {
                  "rating" : 3,
                  "type" : "overall"
               }
            ],
            "author_name" : "Fabian Prinz-Arnold",
            "author_url" : "https://plus.google.com/109400312077468092625",
            "language" : "de",
            "rating" : 5,
            "text" : "Guter Kaffee. ",
            "time" : 1428647728
         },
         {
            "aspects" : [
               {
                  "rating" : 3,
                  "type" : "overall"
               }
            ],
            "author_name" : "Carolin Eberhard",
            "author_url" : "https://plus.google.com/108342380563416174190",
            "language" : "de",
            "rating" : 5,
            "text" : "coole Brücke\n",
            "time" : 1427735795
         },
         {
            "aspects" : [
               {
                  "rating" : 3,
                  "type" : "overall"
               }
            ],
            "author_name" : "Leon M.",
            "author_url" : "https://plus.google.com/109837476853552751747",
            "language" : "de",
            "rating" : 5,
            "text" : "Coole Brücke geile aussicht\n",
            "time" : 1427121695
         },
         {
            "aspects" : [
               {
                  "rating" : 2,
                  "type" : "appeal"
               },
               {
                  "rating" : 2,
                  "type" : "facilities"
               },
               {
                  "rating" : 2,
                  "type" : "service"
               }
            ],
            "author_name" : "Manuel Ihl",
            "author_url" : "https://plus.google.com/107685098902545654125",
            "language" : "de",
            "rating" : 4,
            "text" : "Ja was soll mach sagen... AMAZING... die absolute Hauptattraktion der Stadt ist definitiv die Golden Gate Bridge. Der Ausblick den man hier hat die beeindruckend und das Ambi-ente natuerlich auch.\n\nEin Tipp (wenn auch kein geheimer) - am besten bei schoenem Wetter geniessen... :)",
            "time" : 1362335658
         }
      ],
      "scope" : "GOOGLE",
      "types" : [ "establishment" ],
      "url" : "https://plus.google.com/105065405533145638947/about?hl=de-CH",
      "user_ratings_total" : 828,
      "utc_offset" : -420,
      "vicinity" : "Golden Gate Bridge",
      "website" : "http://www.goldengatebridge.org/"
   },
   "status" : "OK"
}

[fdi01 · Accepted solution]

try like

...| spath input=json path="result.reviews{}.author_Name"|rename  result.reviews{}.author_Name as _AuthorName  |...

[lukas_loder]

Hi fdi01, you helped me!

I found the right helppage from splunk.
index=test sourcetype=Googleplaces | spath | rename result.reviews{}.rating AS ReviewRating, result.reviews{}.author_name AS ReviewName, result.reviews{}.text AS ReviewText

This one worked for me.
With this I made it to seperate column

index=test sourcetype=Googleplaces | spath | rename result.reviews{}.rating AS ReviewRating, result.reviews{}.author_name AS ReviewName, result.reviews{}.text AS ReviewText | eval x=mvzip(ReviewRating,ReviewName) | eval y=mvzip(x,ReviewText) | mvexpand y | eval y = split(y,",") | eval Rating=mvindex(y,0) | eval AuthorName=mvindex(y,1) | eval RatingText=mvindex(y,2) | table _time,Rating, AuthorName, RatingText

[fdi01]

i am happy for you. you can up vote or accepted if you ok
thank. Mr lukas.loder