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?

CodePudding user response：

I don't know Php. But from this link(https://kb.objectrocket.com/postgresql/postgres-stored-procedure-call-in-php-1475), seems it's pretty ok to use functions in php.

demo

CREATE OR REPLACE FUNCTION find_answer (_key text)
    RETURNS json
    AS $$
DECLARE
    is_exists boolean;
    _sql text;
    _return json;
BEGIN
    _sql := $sql$
    SELECT
        row_to_json(cte.*)
    FROM (
        SELECT
            tbl.data ->> $1 AS response,
            count(tbl.data ->> $1) AS total
        FROM
            tbl
        GROUP BY
            1
        ORDER BY
            total DESC
        LIMIT 1) cte $sql$;
    SELECT
        (data[_key] IS NULL) INTO is_exists
    FROM
        tbl;
    IF is_exists THEN
        RAISE EXCEPTION '% not exists.', _key;
    ELSE
        RAISE NOTICE '% sql', _sql;
        EXECUTE _sql
        USING _key INTO _return;
        RETURN _return;
    END IF;
END
$$
LANGUAGE plpgsql;

then call it. select * from find_answer('example');

about Prepared statements

Prepared statements only last for the duration of the current database session. When the session ends, the prepared statement is forgotten, so it must be recreated before being used again. This also means that a single prepared statement cannot be used by multiple simultaneous database clients; however, each client can create their own prepared statement to use. Prepared statements can be manually cleaned up using the DEALLOCATE command.