Anyone know how to extract data from nested json: Not sure how to extract “data.device” and put it in “device” column.

Event coming in kafka:
{
  "user_id": "1234",
  "data": {
    "device": "abcd"
  }
}


Schema I need for table:
{
  {
    "name": "user_id",
    "dataType": "STRING"
  },
  {
    "name": "device",
    "dataType": "STRING"
  },
}

{

"columnName": "device",

"transformFunction": "\"data.device\""

},

-> that should do the trick 😉

Let me try

Or using

{
      "columnName": "device",
      "transformFunction": "JSONPATHSTRING(data,'$.device')"
    },

First one did not work. Thanks “jsonpathstring” worked.

I was thinking from you question you’ve been exploding the neasted JSON my first answer 😉 For reference you will find usefull example here https://docs.pinot.apache.org/developers/advanced/ingestion-level-transformations#inbuilt-pinot-functions (offcicial doc) or here https://dev.startree.ai/docs/pinot/recipes/