我有一个JSON字符串，它存储在DB中与父ID对应的单个单元格中

{"profileState":"ACTIVE","isDefault":"true","joinedOn":"2019-03-24T15:19:52.639Z","profileType":"ADULT","id":"abc","signupDeviceId":"1"}||{"profileState":"ACTIVE","isDefault":"true","joinedOn":"2021-09-05T07:47:00.245Z","imageId":"19","profileType":"KIDS","name":"Kids","id":"efg","signupDeviceId":"1"}

现在我想使用上面的JSON从中提取id。

Parent ID  |  Profile JSON
1          |  {profile_json} (see above string)

我希望输出如下所示

Parent ID  |  ID
1          |  abc
1          |  efg

现在，我已经尝试了几次迭代来解决这个问题

第一次接近：

select
    get_json_object(p.profile, '$$.id') as id,
    test.parent_id
    
    from (
        select split(
                regexp_replace(
                    regexp_extract(profiles, '^\\[(.+)\\]$$',1),
                '\\}\\,\\{', '\\}\\|\\|\\{'),
                '\\|\\|') as profile_list,
                parent_id ,
                
        from source_table) test
        lateral view explode(test.profile_list) p as profile
)

但是这是返回id列的NULL值，我是不是漏掉了什么。

第二种方法：

with profiles as(
  select        regexp_replace(
                    regexp_extract(profiles, '^\\[(.+)\\]$$',1),
                '\\}\\,\\{', '\\}\\|\\|\\{') as profile_list,
                parent_id
                
        from source_table
)      

SELECT
  get_json_object (t1.profile_list,'$.id')
FROM profiles t1

第二种方法只返回上述JSON字符串的第一个id（abc）。