We have injested JSON data from a Kafka pipeline into a single column of the Singlestore table. How to achieve the following query given a sample/subset schema as follows -
Let me try to depict my scenario with a subset of columns.
The JSON object we have is nested at 4 levels something like this -
|-- kafkatimestamp: string (nullable = true)
|-- action: string (nullable = true)
|-- actiondatetime: string (nullable = true)
|-- mainid: string (nullable = true)
|-- shopid: string (nullable = true)
|-- radioid: string (nullable = true)
|-- workload: struct (nullable = true)
| |-- settings: array (nullable = true)
| | | |-- deviceid: string (nullable = true)
| | | |-- devicename: string (nullable = true)
| | | |-- devicetype: string (nullable = true)
| | | |-- autodownload: string (nullable = true)
| | | |-- caid: string (nullable = true)
| | | |-- channelid: string (nullable = true)
Within the records with the action equal to “my bookmarks”, we want to scan the “settings” array. Within this settings array, we want to collect the “channelid” when the “changetype” is equal to “insert” or “remove”.