Hi All, I need to work with JSON data that is stored as a JSONB data type from the source POSTGRES table. Below snapshot of the dataset details the JSON data under the 'data' column. company_id data
6066 {'gw': {'date': '2015-12-31', 'currency': 'USD', 'value': 32021}, 'do': {'date': '2015-12-31', 'currency': 'USD', 'value': 71}} Desired output is to transform the dataset to the below form. company_id metric date currency value
6066 gw 2015-12-31 USD 32021
6066 do 2015-12-31 USD 71 The contents under the JSON string are pretty long extending to 1000's of metrics, and the dataset is large that it makes converting back to JSON and parsing it is cumbersome. a. With the JSON data that needs parsing in a dataset, is there an option of still using INFILE and/or LIBNAME JSON feasible? b. Tried parsing the string with CALL SCAN position parameters, but, unsure how to deal with multiple metric data rollover to separate rows of data. Any pointers on the approach to follow would be very helpful. Thank you!
... View more