“invalid reference to FROM-clause entry for table”

I have the following query:

query =
    "SELECT
      data #>> '{id}'          AS id,
      data #>> '{name}'        AS name,
      data #>> '{curator}'     AS curator,
      data #>  '{$isValid}'    AS \"$isValid\",
      data #>  '{customer}'    AS customer,
      data #>  '{$createdTS}'  AS \"$createdTS\",
      data #>  '{$updatedTS}'  AS \"$updatedTS\",
      data #>  '{$isComplete}' AS \"$isComplete\",
      (count(keys))::numeric as \"numProducts\",
      created_at
    FROM
      appointment_intakes,
      LATERAL jsonb_object_keys(data #> '{products}') keys
    INNER JOIN
      appointment_intake_users
    ON
      appointment_intake_users.appointment_intake_id = appointment_intakes.id
    #{where_clause}
    GROUP BY id"

And it is causing the following error:

invalid reference to FROM-clause entry for table "appointment_intakes"

The error started happening after I added:

LATERAL jsonb_object_keys(data #> '{products}') keys

and

(count(keys))::numeric as \"numProducts\"

because I needed to calculate the number of products.

How can I avoid this error from happening?

Explain error

The immediate cause for the error message is that any explicit JOIN binds stronger than a comma (,) which is otherwise equivalent to a CROSS JOIN, but (per documentation):

Note: This latter equivalence does not hold exactly when more than two tables appear, because JOIN binds more tightly than comma. For example FROM T1 CROSS JOIN T2 INNER JOIN T3 ON condition is not the same as FROM T1, T2 INNER JOIN T3 ON condition because the condition can reference T1 in the first case but not the second.

Bold emphasis at the end mine. This is exactly the cause of your error. You could fix it:

FROM  appointment_intakes
CROSS JOIN LATERAL jsonb_object_keys(data #> '{products}') keys
INNER JOIN appointment_intake_users ON ...

But that's not the only problem in your query. See below.

One might argue that Postgres should see that LATERAL only makes sense in connection with the table to the left. But trying to be to smart quickly gets you in trouble. Better be strict about this.

Assumption

I added table aliases and table-qualified all column names as suspected. While being at it, I simplified the JSON references and trimmed some noise. The query is still incorrect:

"SELECT i.data ->> 'id'          AS id,
        i.data ->> 'name'        AS name,
        i.data ->> 'curator'     AS curator,
        i.data ->  '$isValid'    AS \"$isValid\",
        i.data ->  'customer'    AS customer,
        i.data ->  '$createdTS'  AS \"$createdTS\",
        i.data ->  '$updatedTS'  AS \"$updatedTS\",
        i.data ->  '$isComplete' AS \"$isComplete\",
        count(k.keys)::numeric   AS \"numProducts\",
        u.created_at
 FROM   appointment_intakes i
      , jsonb_object_keys(i.data -> 'products') AS k(keys)
 JOIN   appointment_intake_users u ON u.appointment_intake_id = i.id
 #{where_clause}
 GROUP  BY i.id"

If that's correct and based on some more assumptions, the solution could be to do the count in a subquery, like:

Raw query

Based on above assumptions:

SELECT i.data ->> 'id'          AS id,
       i.data ->> 'name'        AS name,
       i.data ->> 'curator'     AS curator,
       i.data ->  '$isValid'    AS "$isValid",
       i.data ->  'customer'    AS customer,
       i.data ->  '$createdTS'  AS "$createdTS",
       i.data ->  '$updatedTS'  AS "$updatedTS",
       i.data ->  '$isComplete' AS "$isComplete",
       (SELECT count(*)::numeric
        FROM   jsonb_object_keys(i.data -> 'products')) AS "numProducts",
       min(u.created_at)        AS created_at
FROM   appointment_intakes i
JOIN   appointment_intake_users u ON u.appointment_intake_id = i.id
--     #{where_clause}
GROUP  BY i.id

Since you only need the count, I converted your LATERAL join into a correlated subquery, thereby avoiding the various problems arising from multiple 1:n joins combined. More:

What is the difference between LATERAL and a subquery in PostgreSQL?
Two SQL LEFT JOINS produce incorrect result

You need to escape identifiers properly, use a prepared statement and pass values as values. Don't concatenate values into the query string. That's an invitation for random errors or SQL injection attacks.

Here is a recent example for PHP: