SELECT * FROM(
         SELECT cast('online' as varchar(15))                                                                                       as channel,
               cast('es' as varchar(15))                                                                                       as "contentLanguage",
               cast('CO' as varchar(15))                                                                                           as "targetCountry",
                p.product_id                                                                                   AS "offerId",
                initcap(p.title)                                                                               AS title,
                CASE
                    WHEN p.description is null
                        THEN (concat(concat(p.title,','),p.brand))
                    else p.description
                    END                                                                                        AS description,
                Concat('https://[Log in to view URL]', p.product_id)                                              AS link,
                f.url                                                                                          AS image_link,
                to_jsonb(string_to_array(t4.selection_url, ','))  AS "additionalImageLinks",
                cast('in stock' as varchar(15))                                                                                     AS availability,
                ('{"currency": "COP", ' || string_agg('"' || 'value' || '": ' || cast(Round((p.price::numeric(15,2)/ 3540) * 3800) as varchar(50)), ',') || '}')::json as cost_of_goods_sold ,
               -- concat(cast(Round((p.price::integer/ 3540) * 3800) as varchar(50)),' COP')                     AS cost_of_goods_sold,
                ('{"currency": "COP", ' || string_agg('"' || 'value' || '": ' || cast(round(p.price) as varchar(50)), ',') || '}')::json as price ,
                json_build_object('months', '36','amount', json_build_object('currency', 'COP','value', Round(price::numeric/36)))  as installment,
                Replace(c.name, '/', ' > ')                                                                AS google_product_category,
                Replace(c.fullname, '/', ' > ')                                                                AS "productTypes",
                CASE
                    WHEN brand IS NULL THEN 'kiero'
                    WHEN brand = '' THEN 'Kiero'
                    ELSE brand
                    END                                                                                        AS brand,
                CASE  WHEN p.upc = '0' THEN NULL ELSE split_part(p.ean, ',', 1) END                            AS gtin,
                CASE  WHEN p.is_v4 = true THEN Concat('V4_',p.product_id) else Concat('API_', p.product_id) end                 AS mpn,
                cast('new' as varchar(15))                                                                                           AS condition,
                CASE
                    WHEN p.is_adult_product = 1 THEN 'Si'
                    ELSE 'No'
                    END                                                                                        AS adult,
                CASE
                   WHEN p.price > 5500000 THEN 'Price 5.5M +'
                   WHEN p.price > 1500000 AND (p.price < 4499999) THEN 'Price 1.5 - 5.5M'
                   WHEN p.price > 500000 AND (p.price < 1499999) THEN 'Price 500k - 1.5M'
                   WHEN p.price > 10000 AND (p.price < 499999) THEN 'Price 0 - 500K'
                   ELSE '00'
                   END                                                                                        AS "customLabel0",
                to_jsonb('{"Display_ads","Free_listings"}'::text[]) as "includedDestinations",
                to_jsonb('{"Buy_on_Google_listings","Shopping_ads"}'::text[]) as"excludedDestinations"
         FROM products p
                  INNER JOIN files f  ON p.product_id = f.product_id
                  INNER join img_products_unicas t4 on p.product_id = t4.product_id
                  INNER JOIN categories c ON p.category_id = c.category_id
         WHERE f.main = 1
         AND p.status = 1
         AND c.fullname ilike 'Industria y científico%'
         AND p.price > 200000
         
 GROUP BY p.product_id, f.url, t4.selection_url, c.NAME, c.fullname, f.product_id
    ) Q where q.mpn ilike 'V4_%'