Postgres - Check if array contains an id

Question

I'm trying to determine if a user has voted on a question. The user clicks on an upvote/downvote button which makes a request to an API that pulls out their id form a cookie and then stores it within an upvoters or downvoters array. The upvote/downvote logic works as expected -- no problem there -- however, I'm having trouble determining whether or not the upvoters or downvoters array contains their id when the user queries questions by a specific tag.

The question model:

 Column                  │ Type                      │  Modifiers
─────────────────────────┼───────────────────────────┼──────────────────────────────────────────────
 key                     │ Serial                    │  PRIMARY KEY
 userid                  │ UUID                      │  NOT NULL REFERENCES users(id)
 date                    │ TIMESTAMP                 │  WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP
 answered                │ BOOLEAN                   │  DEFAULT FALSE
 views                   │ INTEGER                   │  DEFAULT 0
 upvoters                | TEXT[]                    |  DEFAULT array[]::text[]
 downvoters              | TEXT[]                    |  DEFAULT array[]::text[]
 title                   | TEXT                      |  NOT NULL DEFAULT ''
 uniquetitle             | VARCHAR                   |  NOT NULL DEFAULT ''
 body                    | TEXT                      |  NOT NULL DEFAULT ''
 tags                    | TEXT[]                    |  DEFAULT array[]::text[]
 comments                | JSONB                     |  

The API endpoint:

import get from "lodash.get";
import isEmpty from "lodash.isempty";
import db from "~db"; // connection to Postgres using pg-promise

const findAllQuestionsByTagLimitAndOffset = `
  SELECT 
     questions.key, 
     questions.userid, 
     questions.date, 
     questions.body, 
     questions.answered, 
     questions.views, 
     cardinality(questions.upvoters)-cardinality(questions.downvoters) as votes, 
     EXISTS(
       SELECT FROM questions WHERE questions.upvoters @> $4
     ) as upvoted, // check whether or not the logged in user has upvoted this question
     EXISTS(
        SELECT FROM questions WHERE questions.upvoters @> $4
     ) as downvoted, // check whether or not the logged in user has downvoted this question
     questions.title, 
     questions.uniquetitle, 
     questions.tags, 
     users.username, // appends original poster's username
     users.reputation as userrep, // appends original poster's rep
     users.key as userkey // appends original poster's id
  FROM questions 
  INNER JOIN users ON questions.userid=users.id 
  WHERE questions.tags @> $1 
  ORDER BY questions.date DESC 
  LIMIT $2 
  OFFSET $3
`;

/**
 * Fetches questions by tag.
 *
 * @function fetchNewestQuestionsByTag
 * @param {object} req - request object
 * @param {object} res - response object
 * @returns {array} data - questions that contain the queried tag with original poster populated data and some logged in user data
 * @throws {string} err
 */
const fetchNewestQuestionsByTag = async (req, res) => {
  try {
    const { tag } = req.query;
    if (!tag) throw String("Unable to locate questions because the tag is not valid.");
    const userid = get(req.session, ["id"]);

    const data = await db.any(findAllQuestionsByTagLimitAndOffset, [
      [tag],
      10,
      0,
      [userid],
    ]);
    if (isEmpty(data)) throw String("Unable to locate any questions.");

    res.status(201).send(data);
  } catch (err) {
    res.status(404).send(err);
  }
};

Here's how the question looks after an upvote:

answered: false
body: "Test"
date: "2020-06-19T20:24:46.496Z"
downvoters: []
comments: []
title: "Test Title"
tags: ["reactjs"]
uniquetitle: "test-title"
upvoters ["d17a33f4-b26a-11ea-9372-131fa959a01b"] // logged in user id is stored
userid: "d17a33f4-b26a-11ea-9372-131fa959a018"
views: 1

After a user has upvoted and refreshes the page and/or queries the question they upvoted by a tag, the following (incorrect) data structure is returned:

answered: false
body: "Test"
date: "2020-06-19T20:24:46.496Z"
downvoted: false
tags: ["reactjs"]
title: "Test Title"
uniquetitle: "test-title"
upvoted: false // this should be true as the logged in user is "d17a33f4-b26a-11ea-9372-131fa959a01b" and their id is contained within this array, yet it is still false
userid: "d17a33f4-b26a-11ea-9372-131fa959a018"
userkey: 1
username: "Matt"
userrep: 0
views: 1
votes: 1

The problem is that these queries always return false:

  EXISTS(
    SELECT FROM questions WHERE questions.upvoters @> $4
  ) as upvoted,
  EXISTS(
    SELECT FROM questions WHERE questions.downvoters @> $4
  ) as downvoted,

I've tried the following and none of them returns the correct boolean status:

How to make a select with array contains value clause in psql

Check if value exists in Postgres array

Postgres: check if array field contains value?

What am I doing wrong?

Answer 1

Your use of EXISTS would check in all table rows instead of just the row at hand.

Also, you had upvoters twice. Must be downvoters for downvoted.

I suggest:

  SELECT 
     q.key, 
     q.userid, 
     q.date, 
     q.body, 
     q.answered, 
     q.views, 
     cardinality(q.upvoters)-cardinality(q.downvoters) AS votes, 
     $4 = ANY (q.upvoters) AS upvoted,
     $4 = ANY (q.downvoters) AS downvoted,
     q.title, 
     q.uniquetitle, 
     q.tags, 
     u.username,
     u.reputation AS userrep,
     u.key as userkey
  FROM   questions q
  JOIN   users     u ON q.userid = u.id 
  WHERE  q.tags @> $1 
  ORDER  BY q.date DESC 
  LIMIT  $2 
  OFFSET $3

The array operator @> expects array types on both sides. Seems like you got that right, passing [userid] (constructs an array, right?). With my query, just pass the plain string value userid.

WHERE q.tags @> $1 may still make sense as is, if you aim for index usage. See:

• Can PostgreSQL index array columns?

Also, d17a33f4-b26a-11ea-9372-131fa959a01b suspiciously looks like a UUID. Don't use data type text / text[] for it. Use uuid / uuid[]. Much better in multiple respects. See:

• Would index lookup be noticeably faster with char vs varchar when all values are 36 chars

But all of these grave issues still cannot explain what you report. You should have seen true for both upvoted and downvoted. There may be more mistakes ...