SELECT
TO_CHAR(m.create_time, 'yyyy-mm-dd') create_time,
m.customer_name,
m.salesmen_indo::json->>'name' sales_name,
m.salesmen_indo,
totalTestingCost,
t.test_items
FROM  t_inq_order m
LEFT JOIN t_inq_sync_crm_detail t
ON t.inq_order_id = m."id"
CROSS JOIN LATERAL (
    SELECT
    sum((x.obj->>'testingCost')::float) as totalTestingCost
    FROM json_array_elements(
        case when t.test_items LIKE '{%' OR t.test_items LIKE '[%' then t.test_items::json else null end
        ) as x(obj)
) totalTestingCost
-- LEFT JOIN (
--     SELECT
--     t.id,
--     SUM((j->>'testingCost')::FLOAT) totalTestingCost
--     FROM (
--         SELECT id, json_array_elements(t.test_items::json) j
--         FROM t_inq_sync_crm_detail t
--         WHERE test_items LIKE '{%' OR test_items LIKE '[%'  -- TODO check if is json, NOT best condition
--         ) t
--     GROUP BY t.id
-- ) test_summary
-- ON test_summary.id = t.id

WHERE m.create_time >= '2023-01-01'
--and to_number(to_char(m.create_time, 'yyyymmdd'),'9999999900')<20230206
AND t.ask_type ='药物类产品'
AND m.source_type <>'老系统转入'
AND t.sales_order_code IS NOT NULL;