'Prepared statements against a JSON key throwing "must appear in the GROUP BY clause" in PostgreSQL using PDO

I am trying to dynamically group by json keys to find the average answer.

select
   tbl.data->>'Example' as response,
   count(tbl.data->>'Example') as total
from table tbl
group by tbl.data->>'Example'
order by total
limit 1

This Query works fine when it is ran inside PostgreSQL and I get my expected result:

|     response   |  total  |
|--------------------------|
|  Hello World   |    4    |

The issue now occurs when I don't know the keys. They're dynamically created and thus I need to loop over them.

$sql = <<<END
    select
       tbl.data->>? as response,
       count(tbl.data->>?) as total
    from table tbl
    group by tbl.data->>?
    order by total
    limit 1
END;

$stmt = (new \PDO(...))->Prepare($sql);
$stmt->execute(array_fill(1, 3, 'Example'));
$stmt->fetch(\PDO::FETCH_ASSOC);

'Example' comes from user input. The JSON is created by the user, the keys could be anything. In this case, its hard-coded but I run a seperate SQL query to get all the keys and loop over them:

But I always get the following error:

tbl.data must appear in the GROUP BY clause or be used in an aggregate function

Now, I assume this is because of the prepared statement treating the column as data but this information derives from user input so I need to use prepared statements.

select json_object_keys(data) as keys from table

Any guess to how I can resolve this?



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source