CREATE OR REPLACE PROCEDURE get_key_language_contents(
    IN json_input JSONB,
    INOUT result JSONB
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_company_id INT;
BEGIN
    v_company_id := COALESCE((json_input->>'company_id')::INT, 0);

    -- Initialize the result parameter as an empty JSON object if it's not provided
    IF result IS NULL THEN
        result := '[]'::jsonb;
    END IF;

    -- Aggregate the data into the result JSONB parameter
    result := (
        SELECT jsonb_agg(
            jsonb_build_object(
                'key1', ek.key1,
                'language_content', (
                    SELECT jsonb_agg(
                        jsonb_build_object(
                            'language_id', l.id,
                            'language_name', l.name,
                            'name_in_english', l.name_in_english,
                            'description', COALESCE(sekv.values, NULL)
                        )
                          ORDER BY sekv.key_content ASC
                    )
                    FROM languages l
                    LEFT JOIN language_contents sekv ON ek.key1 = sekv.key_content AND sekv.language_id = l.id AND sekv.status_id = 1
                    WHERE
                        l.company_id = v_company_id
                        AND l.status_id = 1
                )
            )
                 ORDER BY ek.key1 ASC

        )
        FROM (
            SELECT display_name AS key1 FROM master_entity_line_items WHERE company_id = v_company_id AND status_id = 1
            UNION
            SELECT key_content AS key1 FROM language_contents WHERE company_id = v_company_id AND status_id = 1
        ) AS ek
        WHERE ek.key1 IS NOT NULL
    );

END;
$$;