gistfile1.txt

-- Create the test table
CREATE TABLE table_name (
    id SERIAL PRIMARY KEY,
    warnings JSONB
);

-- Insert test data with the original JSON structure
INSERT INTO table_name (warnings) VALUES (
    '[
        {
            "source": [
                {
                    "name": "email",
                    "type": "string"
                },
                {
                    "name": "phone",
                    "type": "string"
                }
            ],
            "message": "Invalid contact information"
        },
        {
            "source": [
                {
                    "name": "address",
                    "type": "object"
                }
            ],
            "message": "Missing address details"
        }
    ]'::jsonb
);

-- View the original data
SELECT id, warnings 
FROM table_name;

-- Run the migration
UPDATE table_name
SET warnings = (
    SELECT jsonb_agg(
        jsonb_build_object(
            'unprocessedFields', (
                SELECT jsonb_agg(
                    jsonb_build_object(
                        'name', source->>'name',
                        'type', source->>'type'
                    )
                )
                FROM jsonb_array_elements(warning->'source') AS source
            ),
            'processedFields', (
                SELECT jsonb_agg(
                    jsonb_build_object(
                        'name', source->>'name',
                        'type', source->>'type'
                    )
                )
                FROM jsonb_array_elements(warning->'source') AS source
            ),
            'message', warning->>'message'
        )
    )
    FROM jsonb_array_elements(warnings) AS warning
)
WHERE warnings IS NOT NULL;

-- View the transformed data
SELECT id, warnings 
FROM table_name;

-- Clean up (optional)