Convert Key Value Pairs in one row to multiple rows in Postgres

user_5822129 0
Viewed 182

For example I have data like below:

bucket_name                               tags
 my_bucket         {'RITM': '0864658', 'AppCode': 'FCKK', 'ApplicationName': 'test-645'}
 my_bucket2        {"RITM": "1117054", "AppCode": "GWRI",  "AssetID": "06634",  "CostCenter": "046",  "ProjectCode": "D20CL"}

I need these key value pair of tags column into multiple rows.

Output:

bucket_name     Key                 Value 
my_bucket       RITM                0864658
my_bucket       AppCode             FCKK
my_bucket       ApplicationName     test-645
my_bucket2      RITM                1117054
my_bucket2      AppCode             GWRI
my_bucket2      AssetID             06634
my_bucket2      CostCenter          046
my_bucket2      ProjectCode         D20CL
key-value postgresql dictionary sql
1 Answers

You can use json_object_keys:

select b.bucket_name, v key, b.tags::json -> v value
from buckets b cross join json_object_keys(tags::json) v;

See fiddle.

user_73267380
Related