parse json object with nested object with unique key-CodePudding

I have the following json object

{"type":"dataset","format":"json","version":"test","data":{"id_one":{"value":"266"},"id_two":{"value":"103"},"id_three":{"value":"103"}}}

I want to parse the data object with a row for each id. I should return something like this:

id, value
id_one, 266,
id_two, 103,
id_three, 103

Id's are not fixed so the solution should be dynamic.

This is what I have now:

WITH raw_data AS (
   SELECT SAFE.PARSE_JSON('{"type":"dataset","format":"json","version":"test","data":{"id_one":{"value":"266"},"id_two":{"value":"103"},"id_three":{"value":"103"}}}') AS json
)
SELECT 
   -- accessing data object non-dynamically and as column instead of rows
   json['data']['id_one'],
   json['data']['id_two'],
   json['data']['id_three'],
FROM raw_data

I guess I need to loop through the data object and pivot at some point but I'm not sure how to do it.

CodePudding user response：

Consider below approach

select kv[offset(0)] as id, kv[offset(2)] as value, 
from raw_data, unnest(split(translate(to_json_string(json.data), '{}"', ''))) id_value,
unnest([struct(split(id_value, ':') as kv)])

if applied to sample data (raw_data CTE) in your question - output is