Union query fails when table contains JSON field

[jezen]

Version

Using esqueleto-3.4.2.0

Persistent entity definition

Mailpiece sql=mailpieces
  Id UUID default=uuid_generate_v4()
  title Text
  description Textarea
  pages PageList
  -- Path to image file on S3
  thumbnail Text Maybe
  createdAt UTCTime default=now()
  -- Which of the two following fields have a value depends on whether or not
  -- the user was authenticated when creating the mailpiece.
  createdBy UserId Maybe
  unverifiedCreatedBy Email Maybe
  -- TODO: Do without generics
  deriving Generic

Instances for persisting that PageList type

instance PersistFieldSql PageList where
  sqlType _ = SqlOther "json"

instance PersistField PageList where
  toPersistValue = toPersistValueJSON
  fromPersistValue = fromPersistValueJSON

Table description in psql

                                  Table "public.mailpieces"
        Column         |           Type           | Collation | Nullable |      Default
-----------------------+--------------------------+-----------+----------+--------------------
 id                    | uuid                     |           | not null | uuid_generate_v4()
 pages                 | json                     |           | not null |
 title                 | character varying        |           | not null |
 description           | character varying        |           | not null |
 thumbnail             | character varying        |           |          |
 created_at            | timestamp with time zone |           | not null | now()
 created_by            | uuid                     |           |          |
 unverified_created_by | character varying        |           |          |
Indexes:
    "mailpieces_pkey" PRIMARY KEY, btree (id)

My union query

getMailpiecesForEmail :: Email -> DB [Entity Mailpiece]
getMailpiecesForEmail email = select $ do
  results <- from $
    (do
      (mailpieces :& user) <-
        from $ Table @Mailpiece `InnerJoin` Table @User
        `on` (\(m :& u) -> m^.MailpieceCreatedBy ==. just (u^.UserId))
      where_ $ user^.UserEmail ==. val email
      pure mailpieces
    )
    `union_`
    (do
      mailpieces <- from $ Table @Mailpiece
      where_ $ mailpieces^.MailpieceUnverifiedCreatedBy ==. just (val email)
      pure mailpieces
    )
  orderBy [ desc (results^.MailpieceCreatedAt) ]
  pure results

The runtime failure

2021-07-15 09:01:57.496 EEST [44514] ERROR:  could not identify an equality operator for type json at character 269
2021-07-15 09:01:57.496 EEST [44514] STATEMENT:  SELECT
          u.v_id,
          u.v_title,
          u.v_description,
          u.v_pages,
          u.v_thumbnail,
          u.v_created_at,
          u.v_created_by,
          u.v_unverified_created_by
        FROM (
          SELECT
            mailpieces.id AS v_id,
            mailpieces.title AS v_title,
            mailpieces.description AS v_description,
            mailpieces.pages AS v_pages,
            mailpieces.thumbnail AS v_thumbnail,
            mailpieces.created_at AS v_created_at,
            mailpieces.created_by AS v_created_by,
            mailpieces.unverified_created_by AS v_unverified_created_by
          FROM mailpieces
          INNER JOIN users ON mailpieces.created_by = users.id
          WHERE users.email = 'alice@example.com'
          UNION
          SELECT
            mailpieces2.id AS v_id,
            mailpieces2.title AS v_title,
            mailpieces2.description AS v_description,
            mailpieces2.pages AS v_pages,
            mailpieces2.thumbnail AS v_thumbnail,
            mailpieces2.created_at AS v_created_at,
            mailpieces2.created_by AS v_created_by,
            mailpieces2.unverified_created_by AS v_unverified_created_by
          FROM mailpieces AS mailpieces2
          WHERE mailpieces2.unverified_created_by = 'alice@example.com'
        ) AS u
        ORDER BY u.v_created_at DESC;
ERROR:  could not identify an equality operator for type json
LINE 15:     mailpieces.pages AS v_pages,
             ^

[jezen]

As it turns out, this is not an issue with Esqueleto, and is instead just because PostgreSQL doesn't know how to compare json fields. Allowing duplicate results with unionAll_ works around this.

Given this is a non-issue, I will close this.