Question 280411 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – SQL How can I return an empty array when all values are null?

I select some tables with json_agg and json_build, all works but I got some data that I dont want it,

My product_media (pm) is empty there are no images, if I use json_agg(json_build_object)) then I got 3x times all rows with null like this:

  product_media: [
    {
      id: null,
      src: null,
      alt: null,
      s_position: null,
      updated_at: null
    },
    {
      id: null,
      src: null,
      alt: null,
      s_position: null,
      updated_at: null
    },
    {
      id: null,
      src: null,
      alt: null,
      s_position: null,
      updated_at: null
    }
  ],

how can I say when id is null then dont show it or when no product media was found then make an empty array ?

code:

      SELECT 

      p.id,
      p.shop_id,
      p.provider_id,
      p.name,
      p.status,

      json_agg(json_build_object(
        'id', pt.id,
        'tag', pt.tag
      )) AS tags,

      json_agg(json_build_object(
        'id', pm.id,
        'src', pm.src,
        'alt', pm.alt,
        's_position', pm.s_position,
        'updated_at', pm.updated_at
      )) AS product_media

      po.id as po_id,
      po.name,
      po.position,

      pov.id as pov_id,
      pov.value,

      pv.id as pv_id,
      pv.quantity,
      pv.price,
      pv.is_male,
      pv.shipping_price,
      pv.grams,
      pv.weight,
      pv.weight_unit,
      pv.length,
      pv.s_position,
      pv.option_1,
      pv.option_2,
      pv.option_3

      FROM product p

      LEFT JOIN product_media pm
      ON pm.p_id = p.id

      LEFT JOIN product_options po
      ON po.p_id = p.id

      LEFT JOIN product_options_value pov
      ON pov.p_o_id = po.id

      LEFT JOIN product_tags pt
      ON pt.p_id = p.id

      LEFT JOIN product_variants pv
      ON pv.p_id = p.id

      WHERE p.provider_id = $1
      
      GROUP BY 
        p.id,
        pm.id,
        po.id,
        pov.id,
        pv.id
        
      LIMIT 1;

I am very thankful for your help I dont know what to do or how to do it

Tags:

2

Answers


  1. 0

    this should do the trick

    json_agg(
    CASE WHEN pm.id IS NULL THEN NULL ELSE
      json_build_object(
        'id', pm.id, 
        'src', pm.src,
        ...
      ) END
  ) AS product_media

    This will check if pm.id is NULL, and if so return NULL instead of building the JSON object.

    Login or Signup to reply.
  2. 0

    Try using json_strip_nulls():

          json_agg(json_strip_nulls(json_build_object(
        'id', pm.id,
        'src', pm.src,
        'alt', pm.alt,
        's_position', pm.s_position,
        'updated_at', pm.updated_at
      ))) AS product_media

    If you can work with jsonb instead of json, that would be preferred (more space efficient). If you choose to use jsonb, then you can use jsonb_strip_nulls() instead.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search