'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 |
|---|
