How do I SELECT a record by using a property inside some JSONB as the id? Structure:

table "mysuperspecialtable"
  id: TEXT = 'activeUsers',
  value: JSONB =
    {
      "activeUserId": "abcdef"
    }

Something like:

SELECT
          username,
          avatarurl
    FROM mysuperspecialtable
    LEFT JOIN users ON users.id = (mysuperspecialtable.value ->> 'activeUserId')::TEXT
    WHERE
        mysuperspecialtable.id = 'activeUsers'

How do I grab the user's record using that property? I have tried everything I can think of (Some context is I am converting Firestore to PostgreSQL and I stored some references to docs in an array)

Try doing

sql
 SELECT
          username,
          avatarurl
    FROM mysuperspecialtable
    LEFT JOIN users ON users.id = mysuperspecialtable @>'{"value": "activeUserId"}'
    WHERE
        mysuperspecialtable @> '{"id":"activeUsers"}'

I fixed it by doing a simple subquery:

SELECT
        username,
          avatarurl
    FROM
        users
    WHERE
        users.id = (
            SELECT mysuperspecialtable.value ->> 'activeAsset'
            FROM mysuperspecialtable
            WHERE
                id = 'activeUsers'
        )