Reports

this might the answer:

    WITH json_data AS (
    SELECT PARSE_JSON('{
        "docId": 123,
        "version": 1,
        "docName": "Test doc",
        "attributtes": [
            {"key": "eff_date", "value": ["22-09-2024", "12-08-2022"]},
            {"key": "renew_flag", "value": ["Y"]}
        ],
        "created_by": "CCVVGG"
    }') AS data
)
SELECT 
    data:docId::INT AS docId,
    data:version::INT AS version,
    data:docName::STRING AS docName,
    data:created_by::STRING AS created_by,
    eff_dates.value::STRING AS eff_date,  -- Flatten eff_date values
    renew_flag.value::STRING AS renew_flag
FROM json_data,
LATERAL FLATTEN(input => data:attributtes) attr
LEFT JOIN LATERAL FLATTEN(input => attr.value:value) eff_dates ON attr.value:key::STRING = 'eff_date'
LEFT JOIN LATERAL FLATTEN(input => attr.value:value) renew_flag ON attr.value:key::STRING = 'renew_flag'
WHERE attr.value:key::STRING IN ('eff_date', 'renew_flag');
79431372
