Wednesday, 19 April 2017

Postgres append to json array if jsonb column is null

I have a column images jsonb for table listings. It will always be just one json array. Users will upload images and the images column will hold image info.

I have a query where I will append to the column images.

It works if there is already a array... but if the column is null because a user hasn't uploaded the first image yet, then I can't get the append to work. What is the best way to handle this?

      var arrayElement =
        `[{"base64String": "${base64String}", "filename": "${filename}"}]`;

      pool
        .query(`UPDATE listings  SET images = images || $1::jsonb \
               WHERE listings.id = $2`, [arrayElement, id])
        .then(() => {
          var response = {
            results173:
              { filename: filename }
          };
          res.status(200).json(response);
        })



via dman

No comments:

Post a Comment