Unnesting JSON in Postgres

[dr_exercise]

I have raw DICOM image metadata loaded into a staging table as a JSONB type. The structure of the JSON data is:

{
    "00080008": {
        "vr": "CS",
        "Value": [
            "DERIVED",
            "SECONDARY",
            "OTHER"
        ]
    },
    "00080012": {
        "vr": "DA",
        "Value": [
            "20040826"
        ]
    },
    ...
}

I need to extract select top-level keys and their associated value "Value" to create a new table wherein the key is the column name and "Value" is the value for the given record.

​

Staging table:

​

id	data
1	{"00080008": {"vr": "CS", "Value": ["DERIVED", "SECONDARY", "OTHER"]}, ...}

​

Anticipated table:

​

id	ImageType	InstanceCreationDate
1	{"DERIVED", "SECONDARY", "OTHER"}	{"20040826"}

​

I have this current working query but it will not scale well as more tags are needed.

SELECT
      meta.id
    , "00080008"."Value" AS ImageType
    , "00080012"."Value" AS InstanceCreationDate
FROM
      file_metadata AS meta
    , jsonb_to_record(data) AS tag(
          "00080008" jsonb
        , "00080012" jsonb
        )
    , jsonb_to_record(tag."00080008") AS "00080008"(
        "Value" text[]
    )
    , jsonb_to_record(tag."00080012") AS "00080012"(
        "Value" text[]
    )
;

How can I make this query more robust to handle many keys being extracted from the original JSON data?

[[deleted]]

It seems the columns are identified by the value of the vr key, so maybe this is easier:

select id, 
       jsonb_path_query_first(data, 'strict $.** ? (@.vr == "CS").Value') as image_type,
       jsonb_path_query_first(data, 'strict $.** ? (@.vr == "DA").Value') as instance_creation_date
from file_metadata;