(FUNC) Gets set data as single JSON object.

get-set-as-json-object.sql

DROP FUNCTION IF EXISTS get_set_data;

CREATE OR REPLACE FUNCTION get_set_data(setId integer)
RETURNS jsonb AS $$
    -- ---------------------------------------------
    -- Selects set as JSON
    -- ---------------------------------------------
    select jsonb_build_object(
        'set_id', s.id,
        'set_name', s.name,
        'family_id', s.family_id,
        'family_name', f.name,
        'user_id', s.user_id,
        'style_id', s.style_id,
        'style_name', st.value,
        'icons_count', (
            SELECT count(*)
            FROM icons i
            WHERE i.set_id = s.id
        ),
        'illustrations_count', (
            SELECT count(*)
            FROM illustrations i
            WHERE i.set_id = s.id
        ),
    
        -- ---------------------------------------------
        -- Selects the icons data for the set.
        -- ---------------------------------------------
        'icons', (
            SELECT CASE
                WHEN EXISTS (
                    SELECT 1
                    FROM icons i
                    WHERE i.set_id = s.id
                ) THEN (
                    SELECT jsonb_agg(
                        jsonb_build_object(
                            'id', i.id,
                            'name', i.name,
                            'price', (SELECT GREATEST(COALESCE(NULLIF(price, ''), 0), 0)),
                            'license_id', i.license_id,
                            'style_id', i.style_id,
                            'style_name', st.value,
                            'color_data', i.color_data,
                            'tags', (
                                SELECT jsonb_agg(t.name)
                                FROM entity_to_tags ett
                                LEFT JOIN tags t on t.id = ett.tag_id
                                WHERE ett.entity_id = i.id
                            ),
                            'images', (
                                SELECT jsonb_agg(
                                    jsonb_build_object(
                                        'id', img.id,
                                        'url', img.url,
										'type_value', it.value,
                                        'image_type_id', img.image_type_id
                                    )
                                )
                                FROM images img
								LEFT JOIN image_types it on it.id = img.image_type_id
                                WHERE img.entity_id = i.id
                            )
                        )
                    )
                    FROM icons i
                    LEFT JOIN styles st on st.id = i.style_id
                    WHERE i.set_id = s.id
                )
                ELSE '[]'::jsonb
            END
        ),
    
        -- ---------------------------------------------
        -- Selects the illustrations data for the set.
        -- ---------------------------------------------
        'illustrations', (
            SELECT CASE
                WHEN EXISTS (
                    SELECT 1
                    FROM illustrations i
                    WHERE i.set_id = s.id
                ) THEN (
                    SELECT jsonb_agg(
                        jsonb_build_object(
                            'id', i.id,
                            'name', i.name,
                            'price', (SELECT GREATEST(COALESCE(NULLIF(price, ''), 0), 0)),
                            'license_id', i.license_id,
                            'style_id', i.style_id,
                            'style_name', st.value,
                            'color_data', i.color_data,
                            'tags', (
                                SELECT jsonb_agg(t.name)
                                FROM entity_to_tags ett
                                LEFT JOIN tags t on t.id = ett.tag_id
                                WHERE ett.entity_id = i.id
                            ),
                            'images', (
                                SELECT jsonb_agg(
                                    jsonb_build_object(
                                        'id', img.id,
                                        'url', img.url,
										'type_value', it.value,
                                        'image_type_id', img.image_type_id
                                    )
                                )
                                FROM images img
								LEFT JOIN image_types it on it.id = img.image_type_id
                                WHERE img.entity_id = i.id
                            )
                        )
                    )
                    FROM illustrations i
                    LEFT JOIN styles st on st.id = i.style_id
                    WHERE i.set_id = s.id
                )
                ELSE '[]'::jsonb
            END
        )
    )
    from sets s 
    left join styles st on st.id = s.style_id
    left join families f on f.id = s.family_id
    where s.id = setId;
$$ LANGUAGE SQL;