How to extract field name and value from JSON

Help Connectors and Pipelines
1

Hi,
I have created a pipeline that gets JSON messages from Kafka into stored procedure where stored procedure gets generic_json which is of type JSON.
I have used generic_json <- % in my pipeline create statement because fields in JSON messages are dynamic

I know I can extract value if I know path/name of the field. I do not have any nested JSONs.

How can I extract field name (Since I do not know before hand) and it’s value so that I can store them as name/value pairs in my table.

Thanks!

Thanks!

2

What may make sense is to harvest a few of the known columns in the normal way, and have a JSON column to store the complete Kafka message. As you learn more about how the data is used and which fields are important, you can add new columns in the table and back-fill these fields.