SQL: return a value when the record doesn't exist in table-CodePudding

I have a SQL query that returns the list of products from a database as a JSON object as follows:

select json_build_object('has_product', 'true', 'product_id', product_id)
from products
where id in (10, 65, 75);

and this is the result:

 {"has_product" : "true", "product_id" : 10}
 {"has_product" : "true", "product_id" : 65}

but for the product_id: 75 I have no result since it doesn't exist and I want it to return something like this:

 {"has_product" : "false", "product_id" : null}

I found a solution but I am wondering if there is a faster and cleaner way to do it:

select row_to_json(req)
from (  
  select
  case (coalesce((select product_id from products where product_id = 10), 0))
    when 0 then 'false'
    when 10 then 'true'
  end has_product,
  case (coalesce((select product_id from products where product_id = 10), 0))
    when 0 then 'null'
    when 10 then '10'
  end product_id

  -- union all
  -- same request for id 65

  union all

  select
  case (coalesce((select product_id from products where product_id = 75), 0))
    when 0 then 'false'
    when 75 then 'true'
  end has_product,
  case (coalesce((select product_id from products where product_id = 75), 0))
    when 0 then 'null'
    when 75 then '75'
  end product_id
) req

CodePudding user response：

Instead of filtering using in, build on the fly a table with all the desired product IDs, and right join against it. It will create one row for each product ID you want. Then you can tell if the product exists in your main table by checking the product_id against null. For the product IDs for which you do not have an entry in the main table it will be null, for those for which you have it will be non-null.

select
    json_build_object('has_product', product_id is not null, 'product_id', desired_product_id)
from
    products
        right join
    unnest(ARRAY[10, 65, 75]) desired_product_id
    on product_id = desired_product_id;

CodePudding user response：

Instead of using IN(), use a LEFT JOIN...

WITH
  list(id) AS
(
            SELECT 10
  UNION ALL SELECT 65
  UNION ALL SELECT 75
)
SELECT
  json_build_object(
    'has_product', CASE WHEN products.id IS NULL THEN 'false' ELSE 'true' END,
    'product_id', products.product_id
  )
FROM
  list
LEFT JOIN
  products
    ON products.id = list.id